mysql

Best way to perform DELETE that uses ids from a SELECT statement in MYSQL

I'm working on a stored procedure, that needs to delete specific rows based on a timestamp. Here's what I was going to use until I found out you can't include a select clause in the delete statement if they are both working on the same table. DELETE FROM product WHERE merchant_id = 2 AND product_id IN (SELECT product_id FROM product W...

MySQL Storage Engine supporting optionally indexed FKs

One of the more annoying things about InnoDB is that although it supports foreign keys it requires that foreign key columns in the referencing table be indexed. While that's often a good idea it certainly isn't always one. Take for example an orders table with ~20 columns. Aside from the single column primary key and perhaps the cre...

Rails mysql Type Error after switching to Ruby 1.9.1

I'm not quite sure how to go about diagnosing this issue, after switching my app to Ruby 1.9.1 I'm getting this error: TypeError wrong argument type Mysql (expected Struct) And it seems the app fails to boot, so I'm not really sure of how to even begin solving this issue. I've made sure all my gems are up to date and they are. I'm us...

mysql procedure call in codeigniter

Any simple example of mysql stored procedure and example to show how to call that procedure from codeigniter php. Any help? ...

Dealing with MySQL Temp Table algorithm for views

It would be nice if the Temp Table algorithm would be renamed to Unscalable algorithm. Perhaps then it would provide more of a warning to developers when seeing this in a view definition - similarly when it says using temp table in the explain results. Just a tongue-in-cheek request for the most part but really it can be disastrous to t...

Update query not effecting all rows in database

I have the following query.... UPDATE vehicle_catalog SET parent_id = 0 WHERE parent_id = SUBSTR(id, 0, 5) I need to set all parent_ids to 0 where the first 5 characters of id is the same as the parent_id. This is effecting 0 rows when I'm looking at the data and it should be effecting over 10,000. Any ideas on why this wouldn't be ...

mysql: get all rows into 1 column

hi, i have 3 tables: post (id_post, title) tag (id_tag, name) post_tag (id_post_tag, id_post, id_tag) Lets suppose that id_post 3 has 4 linked tags 1,2,3,4 (soccer, basket, tennis and golf). Is there a way to return something like this in ONE row? col 1 id_post = 3 col 2 tags = soccer basket tennis golf Thanks ...

First record does not show in pagination script

This is my pagination script which extracts info for my TV guide project that I am working on. Currently I've been experimenting with different PHP/MySQL before it becomes a production site. This is my current script: <?php include("pmcPagination.php"); $paginator = new pmcPagination(20, "page"); mysql_...

PDO Database Connections Problem

Hey Everyone, Over a year ago I created my own database classes which use PDO, and handle all preparing, executing, and closing connections. These classes have been working great up until now. There are two different database severs I am grabbing from, MySQL, and MS SQL Express. I am retrieving an employee id from the MySQL server and...

With modern social networking and scaling, can Cassandra replace the standard relational database such as MySQL?

Is it possible to build the entire social networking application on cassandra? Sure, it takes longer to set up, but it scales much better. Correct? Please list the situations when Cassandra should be used. ...

MySQL: How to consume/discard the result of a query?

I have a stored procedure which executes an optimize table statement for every table in a DB. Those optimize table statements are prepared statements of course (they have to be built at runtime) and I need to call that procedure from PHP using ext/mysql API. Unfortunately, ext/mysql does't support doing such thing because optimize table...

What is the unit in returned by MySQL GLength method?

I want to get the length in meters between 2 points in the surface of the Earth. But the GLength method returns an unexpected value http://dev.mysql.com/doc/refman/5.0/en/geometry-property-functions.html#function_glength SELECT GLength(GeomFromText( 'LINESTRING(-67.8246 -10.0073,-67.8236 -10.0082)', 4326)) actual result 0.0013453...

mysqldump parameter to ensure DROP VIEW IF EXISTS rather than incorrect DROP TABLE IF EXISTS

Wonder if there is a parameter I can pass in mmysqldump equivalent for SQL Servery mysqldump that will make the incorrect "DROP TABLE IF EXISTS" statements into "DROP VIEW IF EXISTS", so that populating a database in an automated development environment refresh will work? Clarification, I'm getting DROP TABLE IF EXISTS statements in my ...

Mysql syntax help

Query: select t1.col1 from table1 t1 inner join with (nolock) table2 t2 on t1.col2 = t2.col1 Am trying to use nolock option for optimized query in mySQL db, but for some reason the above query does not work and the error i receive is You have an error in your SQL syntax; Any thoughts? ...

mysql join with conditional

Hi There, I am currently working on a MySQL query that contains a table: TBL:lesson_fee -fee_type_id (PRI) -lesson_type_id (PRI) -lesson_fee_amount this table contains the fees for a particular 'lesson type' and there are different 'fee names' (fee_type). Which means that there can be many entries in this table for one 'lesson type' ...

How do I make replies to comments? (PHP)

I want to create something like reddit where they have comments, then replies to the comment, then reply to the reply. What type of database structure do they use so: 1. they keep track of all the comments to a posting 2. a reply to a comment 3. a reply to a reply All I have right are is just a posting and a bunch of comments relatin...

Voting Script, Possibility of Simplifying Database Queries

I have a voting script which stores the post_id and the user_id in a table, to determine whether a particular user has already voted on a post and disallow them in the future. To do that, I am doing the following 3 queries. SELECT user_id, post_id from votes_table where postid=? AND user_id=? If that returns no rows, then: UPDATE po...

MySQL - Mac - Error 2002 - Can't connect to local... through socket

I had MySQL running fine on my Mac till earlier today when I installed homebrew and also updated my Path. When I try to run mysql from my ternimal window now, I get the following error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) I can't connect MySQLAdmin either. I can't even run M...

How do you stored a php serialized array in mysql with values that contain apostrophes?

I have a listing of names, that I want to store in mysql as a serialized array (for caching purposes). Some names contain apostrophes, and for some reason won't un-serliaze properly. There is no output. ...

How can I use PHPUnit to set up and tear down the database using a SQL file?

I have a MySQL file that represents my database in a specific state that would allow me to test a class I am working on. What I want to do is have PHPUnit clean and rebuild the database from this file for each test or set of tests. How can I go about that without having to resort to rewriting the data in the XML format that PHPUnit uses...