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

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

If you have a high need for availability for a production application, but don’t have the money to invest in full fault tolerance networking, machinery, and access, you’ll need to have some sort of realtime backup system in place should your primary environment go down. This poses the issue of how do you keep one system on standbye, but be sure that it’s ready to go at all times. The image below shows one possible setup. The...

Read More