I was going to be a good Bellsouth customer and try out their new “free” personality test when I get this SQL error.

This can mean a whole heap of personal issues I’m going to have to deal with:

1. I really do have a twin brother!
2. I’m so common I get a SQL duplcate enty error.
3. My friends at work won’t let me in, now Bellsouth’s database.

Thanks Bellsouth for boosting my self-asteam one more notch up!

Quite a few times during projects I only want to find a single value from the database. But rather than setting up WHILE loop, I’ve been using a quick function that does two things perfectly.

Here’s the function:

// returns single result
function mysql_one_data($query)
{
   $one=mysql_query($query);
   $r=mysql_fetch_row($one);
   return($r[0]);
}

// returns single array
function mysql_one_array($query)
{
   $one=mysql_query($query) or die (mysql_error());
   $r=mysql_fetch_array($one);
   return($r);
}

Say for example I wanted just the date of a record. All I have to do is make sure to call the function and then do the following:

// single value
$date = mysql_one_data("SELECT date FROM records WHERE id='2' LIMIT 1");

// single array
$date_info = mysql_one_array("SELECT firstname, lastname, date FROM records WHERE id='2' LIMIT 1");

// you can then use the single $date_info array like
echo $date_info['firstname'].' '.$date_info['lastname'].' ('.$date_info['date'].')';

Just let me know if you have any questions about this.

The other day I needed to compare two tables and see what data was in one but not in the other.

table_a is my main table that I want to update and table_b is my storage table that I have my updates in. The query below will return all records that are in table_b, but no in table_a.


SELECT table_b.* FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.item_id IS NULL


The you can take those results and use them to INSERT the missing records.

This is the best way I can think of. If anyone has a better way, please leave a comment on how you would recommend.