If you’re busy in PHPMyAdmin and you do alot of repeat queries, or you just come back to a query now and again, you might find it useful to have a record of those queries.  Whether it’s just saving you time retyping something, or if you’re switching back and forth between windows and accidentally close your query window, having a record is always good.  There are two useful things you can do.  The first is...

Read More

Many times you have to do a query before you do a write action to a database because you have to see whether or not a row exists for the data you want to put in. If it does exist, you’d then do another query that was an update, otherwise you’d have to do an insert. Well if you want a quicker way, and you don’t need to use operators like “Update `column`+1″, then “REPLACE INTO” is the function for you. ...

Read More

Sometimes it’s nice to just use a left join to see if a column exists. That’s what left joins are for. Your base query will still return rows even if there are no rows in the joined table(s). However, what if there is a one to many relationship between the base table and the joined table, that is to say that for any row in the base table there could be any number of rows in the joined table. If you have more than one row in the...

Read More

I don’t recommend over using the REPLACE() function in MySQL.  It completely destroys performance.  However, sometimes, either by poor design, or just oversight, you have to replace something in order to make a join work, or the application code work.  REPLACE() is case-sensitive. The syntax is pretty simple: REPLACE(`field`, ‘find’, ‘replacewith’) Example: Fruit Values: Apple, Orange, Pear,...

Read More

Warning Statement may not be safe to log in statement format You may get this error if you are replicating using statement based replication and not row based replication. The fix for the most part is simply to avoid “LIMIT” clauses. Do not use limits on Updates or Deletes because the records may be stored physically different on the replicated server and if you aren’t careful, you may delete undesired data.

Read More

If you need a quick way to insert into another table from the results of a query, the best way would be to use the INSERT-SELECT Syntax. It’s quite simple. INSERT INTO tableA (FieldA, FieldB) (SELECT FieldC, FieldD FROM tableB) ; Note that field A & C and B & D should have the same data type. Using this method you can avoid any secondary programming language or loops to achieve your insert.

Read More