news, announcements & our blog...

Displaying all posts with the tag 'MySQL'

MySQL Insert or Update if exist posted by Kevin S, 03.03.2011

When developing applications its inevitable at some point you'll be checking to see if something exists and if it doesn't; creating it in the database.

The way you've most likely been doing this is running a select query to see if it exists, grabbing the ID if it does exist then executing an UPDATE or noting it doesn't exist then executing an INSERT. It's time consuming and adds more unnecessary code you feel you probably don't need.

We have an answer!

There is a way to run an update within MySQL and make a note as to how many records it has updated, you can check to see if this is zero then run the insert code if need be, simple eh? Check out my example below and start counting up those saved seconds for another tea and biscuit break!

MySQL Insert or Update if it exists

$result = mysql_query("UPDATE table SET notes = '' WHERE date = '$date'");
if (mysql_affected_rows() == 0) {
$result = mysql_query("insert into table () values ();");
}

A coding tip: MySQL CASE posted by Adam B, 18.02.2011

Here's a top tip to speed up anyone's day (so long as they spend their days typing code). Sometimes you may want to have an on/off status for a particular entry in a database. For example, in our CMS system you can make web pages visible or invisible to the public by simply clicking the on/off button for each page. To do this might involve looking in the database, finding a field, checking the value and changing it accordingly (eg 1 for on, 0 for off).

So without the need for all these lines of PHP and MySQL you can simply use the MySQL 'CASE' construct, which looks at a field, and changes it if it is a certain value, in much the same way as an if ... else statement (but with much less code!).

Have a look at the example below, this is a quick an easy way to say "If 'status' is set to 1, set it to 2, but if it is set to 2, set it to 1. And Bob's yer uncle!