Geek Answers Handbook

How to check if a row exists in MySQL using PHP

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

If the identifier in the database does not exist in the XML file, then the entire row corresponding to the identifier is no longer valid and will be deleted.

To do this, I read in each line of XML from beginning to end in a while statement.

As a first step, I try to do a simple mapping and echo if it finds an identifier in a database that does not exist in XML.

I know that there are several identifiers in the database that do not exist in XML, but the following code does not display them.

I have three questions: firstly, how can I display the Id that I pulled from the database, and secondly, why this code does not detect identifiers that are not in XML?

The last question: I am completely wrong about this, and there is a better way to do this!

$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"; }
+4
source share
6 answers

This is the correct 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"; }
+5
source

Your code does not find what you expect, because although the identifier is not found, $sql_result still contains the value TRUE , because the request 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']; }
+5
source

You should check the number of rows returned with mysql_num_rows (). Otherwise, you simply check to see if the query is being executed without any errors.

 if($sql_result)

to

 if(mysql_num_rows($sql_result))
+3
source

You can use NOT IN () for your choice with identifiers that exist on your XML, for example:

 SELECT id FROM `list` WHERE id NOT IN($your_id_list)

With this, you will get a list of identifiers that are not in the list.

Your identifiers should be separated by a comma, for example:

 SELECT id FROM `list` WHERE id NOT IN(123,654,987,45)
+3
source

Question 1: how can I display the Id that I pulled from the database?

 $sql_result = mysql_query("SELECT `id` FROM `list` WHERE `id` = $id") or die(mysql_error()); $sql_row = mysql_fetch_assoc($sql_result); if(!empty($sql_row['id'])) { echo "Id exists - " . $sql_row['id'] . "\n"; } else { echo "Id no longer exists - " . $sql_row['id'] . "\n"; }

Question 2: why this code does not detect identifiers that are not in XML?

I think in your code the if () condition will always return true regardless of whether the identifier exists in the database or not. And secondly, as you might have guessed from my code above, you are missing data from the SQL result set

Question 3: Am I completely wrong about this, and is there a better way to do this?

You do it right by looking at XML and checking for every entry in the database. It would be best to get all the identifiers from XML first, and then use them in a single SQL query:

 SELECT `id` FROM `list` WHERE `id` NOT IN ($list);

Please note that this request may work slowly if the XML file contains a lot of identifiers, say several hundred.

+2
source 
 mysql_num_rows()

or

 SELECT COUNT(*) [...]

+1
source

More articles:


All Articles