How to check if a row exists in MySQL using PHP

Question

I am trying to read in an XML file and compare it to fields in an existing database.

If the ID in the database doesn't exist in the XML file, then the whole row corresponding to the Id is no longer valid and will be deleted.

To do this I read in each line of the XML from start to finish in a while statement.

As step one I am trying to do a simple compare, and echo if it finds an Id in the database that doesn't exist in the XML.

I know there are some Ids in the database that don't exist in the XML, but the following code is not displaying them.

I've got three questions, firstly how would I display the Id that is pulled from the database, and secondly why isn't this code finding any ids that are not in the XML?

The final question is am I going about this completely the wrong way and is there a better way to do it!

$sql_result = mysql_query("SELECT id FROM `list` WHERE id =  $id") or die(mysql_error());

if($sql_result)
{
// echo $id . " Id exists " .  $sql_result["id"] . "\n";
}
else
{
echo "Id no longer exists" . $id . "\n";
}

Answer 1

Your code isn't finding what you expect because even though the id may not be found, $sql_result still holds a TRUE value because the query was successful. Instead, check if myqsl_num_rows() > 0

if($mysql_num_rows($sql_result) > 0)
{
  // echo $id . " Id exists "\n";

  //Now, to print the id, you need to fetch it from `$sql_result`, 
  //which is just a resource at this point:
  $row = mysql_fetch_assoc($sql_result);
  echo $row['id'];
}

Answer 2

This is the proper way to check:

$sql_result = mysql_query("SELECT `id` FROM `list` WHERE `id` =  ".intval($id,10)." LIMIT 0,1");

if(is_resource($sql_result) && mysql_num_rows($sql_result) > 0 ){
    $sql_result = mysql_fetch_assoc($sql_result);
    echo $id . " Id exists " .  $sql_result["id"] . "\n";
}
else{
    echo "Id no longer exists" . $id . "\n";
}