problem with a zero row result set

[jmilane]

Hi,

When I am doing a search, if there are no results,

$num=mysql_num_rows($result);

gives me an error. I want it to say something like "There are not matching records."

Instead, it returns a message that says:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\WAMP\xampp\htdocs\regsearch.php on line 30

How do I avoid returning this error message?

Thank you.

[ahundiak]

The error message is being generated because your sql statement has a problem in it.
Try:
$sql = 'Whatever';
$result = mysql_query($sql) or die(mysql_error());

[jmilane]

ahundiak wrote:

The error message is being generated because your sql statement has a problem in it.
Try:
$sql = 'Whatever';
$result = mysql_query($sql) or die(mysql_error());

It tells me I have an error in line 1... but line 1 has worked before.

Here is my code.

<?PHP

$username="dfsf";
$password="dfsdfsdfsdfsdf";
$database="sdfsdfsdf";
$databox="www.sdfdsffsdsdfsdf.net";

mysql_connect($databox,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");


if (isset($_POST['regid'])) {
$regid = $_POST['regid'];
$query = "select employmentprog, businessprog, certprog, youthprog, parentprog, policyprog, firstname, middlename, lastname, birthdate, street, city, state, postalcode, homephone, otherphone, referral, contact, race, otherrace, gender, regdate from registration where id = $regid;";
}



if (isset($_POST['lastname'])) {
$ln = $_POST['lastname'];
$query = "select employmentprog, businessprog, certprog, youthprog, parentprog, policyprog, firstname, middlename, lastname, birthdate, street, city, state, postalcode, homephone, otherphone, referral, contact, race, otherrace, gender, regdate from registration where lastname = $ln;";
}

$result=mysql_query($query) or die(mysql_error());  

$num=mysql_num_rows($result);

if ($num == 0) {echo "No matching records."; }

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$firstname=mysql_result($result,$i,"firstname");
$middlename=mysql_result($result,$i,"middlename");
$lastname=mysql_result($result,$i,"lastname");
$homephone=mysql_result($result,$i,"homephone");
$otherphone=mysql_result($result,$i,"otherphone");
$street=mysql_result($result,$i,"street");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$postalcode=mysql_result($result,$i,"postalcode");
$referral=mysql_result($result,$i,"referral");
$race=mysql_result($result,$i,"race");
$otherrace=mysql_result($result,$i,"otherrace");
$contact=mysql_result($result,$i,"contact");
$gender=mysql_result($result,$i,"gender");
$regdate=mysql_result($result,$i,"regdate");


echo "<b>$firstname $lastname</b><br>Phone: $homephone<br>Other Phone: $otherphone<br>Street: $street<br>City: $city<br>Zip: $postalcode<br>Best time to contact $contact<br>Race: $race<br>Other race (if applicable): $otherrace<br>Gender: $gender<br>Registration date: $regdate<br><hr>";

$i++;
}

//include_once (./regdbsearch.php);

?>

[ragedigital]

Once you correct the SQL query, it will return the result of 0 (zero) to $num, if there are no records. It will not generate an error.

At that point you can do your:

if($num==0)
    $queryMsg = "There are no matching records.";

[jmilane]

ragedigital wrote:

Once you correct the SQL query, it will return the result of 0 (zero) to $num, if there are no records. It will not generate an error.

At that point you can do your:

if($num==0)
    $queryMsg = "There are no matching records.";

But there ARE matching records.

Now I am getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

When I echo $query and feed it into my query browser, it works. I get a record. It doesnt work here, for whatever reason... it is telling me that I have an error in my MySQL syntax... but this same syntax works from within the MySQL query brower, on the same database... same everything.

[ragedigital]

You have an extra semi-colon in there after $regid

$query = "select * from registration where id = $regid;";

I would even do this:

$query = "select * from registration where id = '".$regid."';

[jmilane]

ragedigital wrote:

You have an extra semi-colon in there after $regid

$query = "select * from registration where id = $regid;";

I would even do this:

$query = "select * from registration where id = '".$regid."';

Thank you.

Is this not correct? :

if (isset($_POST['regid'])) {
$regid = $_POST['regid'];
$query = "select employmentprog, businessprog, certprog, youthprog, parentprog, policyprog, firstname, middlename, lastname, birthdate, street, city, state, postalcode, homephone, otherphone, referral, contact, race, otherrace, gender, regdate from registration where id = $regid;"
}

I get "unexpected }"

[ragedigital]

Sorry... duplicate post

[ragedigital]

No, you have to have the semi-colon outside of the quotes...

id = $regid";

[jmilane]

ragedigital wrote:

No, you have to have the semi-colon outside of the quotes...

id = $regid";

This is such a mess.

Im still getting the line 1 error.

Im sorry. I dont expect any more help with this.

[ragedigital]

Did you correct the same issue in your other query?

$query = "select * from registration where lastname = $ln;"; 

Should be:

$query = "select * from registration where lastname = $ln"; 

or

$query = "select * from registration where lastname = '".$ln."'";

[ragedigital]

ragedigital wrote:

You have an extra semi-colon in there after $regid

$query = "select * from registration where id = $regid;";

I would even do this:

$query = "select * from registration where id = '".$regid."';

I posted an error in the second line, should be:

$query = "select * from registration where id = '".$regid."'";

[jmilane]

ragedigital wrote:

I posted an error in the second line, should be:

$query = "select * from registration where id = '".$regid."'";

Now i just get no matching records... but there are... if I echo $query and execute it in my query browser, I get a record back.

I took out everything that referenced the second search option (last name) and it works.... so something was getting crossed I guess.

Thanks for your help. Very much.

I am going to try and make this script (dont ask me how... I have no idea) be linked to in an email hyperlink with an id appended on the end like "?id=81" so it just runs and we can bypass all this data entry stuff. I will write reports later.

Thank you sir.

[ahundiak]

The last name query need to have some quotes:
where lastname = $ln;
should be:
where lastname = '$ln';

The semi-colon at the end is fine.
Are you sure you are directly copying and pasting the exact query into your query browser? You might try echoing the query and pasting it here. I expect that you are not actually getting the correct posted data.

[ragedigital]

jmilane wrote:

Now i just get no matching records... but there are... if I echo $query and execute it in my query browser, I get a record back.

I took out everything that referenced the second search option (last name) and it works.... so something was getting crossed I guess.

Thanks for your help. Very much.

I am going to try and make this script (dont ask me how... I have no idea) be linked to in an email hyperlink with an id appended on the end like "?id=81" so it just runs and we can bypass all this data entry stuff. I will write reports later.

Thank you sir.

If you do that with the email - it will not work unless you change from $POST to either $GET or $REQUEST. I suggest using $REQUEST in your script and it will capture both POST and GET variables.

It just sounds like the variables are not being passed properly.

[jmilane]

ragedigital wrote:

If you do that with the email - it will not work unless you change from $POST to either $GET or $REQUEST. I suggest using $REQUEST in your script and it will capture both POST and GET variables.

It just sounds like the variables are not being passed properly.

so if i have a script www.mysite.com/script.php

i can make a link www.mysite.com/script.php?id=33 and it will not get a 404? i can look to the $_REQUEST['id'] and it should work?

thanks!

[jmilane]

ragedigital wrote:

I posted an error in the second line, should be:

$query = "select * from registration where id = '".$regid."'";

Can I ask... why does $regid need to be surrounded by that format of quote? Why doesnt simply $regid work?

[ragedigital]

That is correct. It will work, if you:

Change all instances of $POST to $REQUEST

Here is an example on a page I wrote: http://www.seasl.org/archivePage.php?archiveYear=2006

I extract the archiveYear using $_REQUEST['archiveYear'], then place that into my database query to retrieve the events for that year.

[ragedigital]

jmilane wrote:

Can I ask... why does $regid need to be surrounded by that format of quote? Why doesnt simply $regid work?

Syntax... just the way it has to be. From what I have seen you can either do it this way:

$query = "select * from registration where id = '".$regid."'"; 

or like this:

$query = "select * from registration where id = '$regid'"; 

I personally like it the way I do it (first example) because it highlights it differently in my editor and it works with any type of statement.

[ahundiak]

jmilane wrote:

Can I ask... why does $regid need to be surrounded by that format of quote? Why doesnt simply $regid work?

As long as id is a numeric field then the value does not need to be quoted. In fact, standard SQL does not allow it to be quoted.

But mysql does not always follow the standards. The manual has a section on this. Numeric values can be either quoted or not quoted in mysql. It was something else that ended up fixing your problem.