Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Query DB to see if a record exists

[P-nut]

Oh brother - I can't believe I'm asking this as I know that I know the answer - I just can't seem to figure it out.

I want to query a table in a database (using PHP) to see if a particular thing exists. For this example I'll say I'm looking to see if there is a value of a color in the column color. If the color is in the database, I would like to ouput "This color was already added." If the color is not in the database I would like to output "The color was not found; you should add it now."

So I tried something like this:

PHP Code:

$sql = mysql_query("SELECT * FROM `table` WHERE `color` = '".$_GET['color']."'");

if(!$result) {
echo 'This color was not found; you should add it now.';
} else {
echo 'This color was already added';
} 


But I keep getting the message "This color was already added" when I know that it's not there.

Hoping someone will be able to help

[BurakUeda]

that $result should be $sql ?

[P-nut]

I finally got it to work:

PHP Code:

$result = mysql_query("SELECT * FROM `table` WHERE `color` = '".$_GET['color']."'") or die(mysql_error());
   if($row = mysql_fetch_array($result)) {
echo 'This color was already added';
   } else {
echo 'This color was not found; you should add it now.';
} 


Think I need to go to bed

[MGCJerry]

use mysql_numrows(), and if the count is 1, its there, and if its 0, its not there...

Something like this. Forgive me if I get the code wrong, I normally use a MySQL class.

PHP Code:

$sql = mysql_query("SELECT * FROM `table` WHERE `color` = '".$_GET['color']."'");
if (mysql_numrows($result)=="1")  {
    echo 'This color was already added';
} else {
    echo 'This color was not found; you should add it now.';
} 


Hopefully this points you in the right direction and you get it working.

[null]

You can also use simpe sql query:

select count(*) from table where color = [color]

It will return zero if color doesn't exist or 1 if it does.

[hiryuu]

I'm glad you got it working. A couple additional notes:
You should probably end it with 'LIMIT 1', so the server knows it can stop after the first hit. (If color is indexed as unique, then it doesn't matter so much.)

This is just mockup code, but I trust you know that the $_ variables should never go directly into a query. Treat every one of them, even 'trusted' ones like $_ENV, like a live grenade.

[Burhan]

In line with what hiryuu said, you should also ALWAYS do some filtering with data that is going to be embedded in a query -- use functions such as mysql_real_escape_string() on any user-submitted data.

I would also recommed you go with null's suggestion because it is a more efficient use of your database, and will probably improve the performance of your script should it ever be put on a heavily-used site. I went into a long discussion on why using COUNT(*) is better than SELECT * in another thread, but I can't remember the topic of it right now.

[P-nut]

Originally Posted by fyrestrtr

In line with what hiryuu said, you should also ALWAYS do some filtering with data that is going to be embedded in a query -- use functions such as mysql_real_escape_string() on any user-submitted data.

Of course Good advice nonetheless

[srcnix]

Originally Posted by fyrestrtr

I would also recommed you go with null's suggestion because it is a more efficient use of your database, and will probably improve the performance of your script should it ever be put on a heavily-used site. I went into a long discussion on why using COUNT(*) is better than SELECT * in another thread, but I can't remember the topic of it right now.

If you come across the topic again please direct me to it. Sounds interesting.