Using results after searching sql with php

software development

#1

OK. I have this PHP code below to search a sql database.

$query = mysql_query('SELECT guest_number FROM serial_numbers WHERE guest_name = "'.mysql_real_escape_string($name).'" ORDER BY guest_number ASC');
$row = mysql_fetch_assoc($query) or die(mysql_error());

I know that $row[‘guest_number’] will return what I’m looking for…as long as there is a result. How do I handle a situation when there is no ‘guest_number’ where ‘guest_name’ equals $name?
I had this situation happen on one of my pages. I viewed the source and everything on the page turned out fine until the php code. After the code, no more HTML was printed.

Any suggestions or useful links?


#2

I would recommend using mysql_num_rows(), because it is the least complicated way of handling the situation. Also, if you are expecting only 1 or 0 results there is no need for the ORDER BY clause. Try something like this (which I’ve made a bit more verbose for clarity):$name = mysql_real_escape_string($name); $sql = "SELECT guest_number FROM serial_numbers WHERE guest_name = '$name'"; $query = mysql_query($sql) or die(mysql_error()); $num_rows = mysql_num_rows($query); if($num_rows === FALSE) { echo "No guest number found."; } else { $row = mysql_fetch_assoc($query); echo "Guest number: ".$row['guest_number']; // etc. }--------
si-blog | Keystone Websites
Save $97 on yearly plans with promo code [color=#CC0000]SCJESSEY97[/color]


#3

Thanks so much. I’ll try that out. As for results, there can be anywhere from 0 to thousands or results. I’m printing them to the screen in a table, so the order by clause is helpful.


#4

That worked great but I have one more problem/question.

I’m trying to get the number of guest_numbers between two values, $start and $end.
$query = mysql_query(‘SELECT COUNT(*) FROM serial_numbers WHERE guest_number > $start AND guest_number <= $end’);
$row=mysql_fetch_row($query);
The code above isn’t working. Even if I change the last line to $row=mysql_num_rows($query);

This leads me to believe that something is wrong with the query statement. Do I have to use an int equivalent of mysql_real_escape_string()?


#5

You need to single quote your variables:

$query = mysql_query("SELECT COUNT(*) FROM serial_numbers WHERE guest_number > '$start' AND guest_number <= '$end'");Note that I’ve switched to using double quotes for the entire query to avoid confusion. For easy debugging, consider separating the SQL string from the function:$sql = "SELECT COUNT(*) FROM serial_numbers WHERE guest_number > '$start' AND guest_number <= '$end'"; $query = mysql_query($sql);--------
si-blog | Keystone Websites
Save $97 on yearly plans with promo code [color=#CC0000]SCJESSEY97[/color]