checking return on mysql_query

melrse

Hi,
Just looking for a quick and easy response.

I have a security file that contains name and id that a person enters from a form. I am using $result = mysql_query ("SELECT * from xxx WHERE name = $name and id = $id",$db) to check for validation.

I only care if there is an associated record and I don't need to do anything with the fields. I have been checking the value of $result but its not correct. I have been trying to do if !$result - no record.
In reading the documentation on mysql_query the pass or fail value is only based on the validity of the SQL statement so using $result is incorrect?

Its easy things that seem to foul me up.

Thanks,
Melissa

Anon

If you want to know if any results came up, just go like this.....

$result = mysql_query(blah blah blah blah);
$numrows = mysql_num_rows($result);
if ($numrows == "0") {
//what to do if no rows came up
} else {
//what to do if 1 or more rows came up
}

also.... i noticed your username is melrse and then you have "Thanks, Melissa"..... are you by any chance Milissa Rose?

melrse

Yes, Melissa Rose it is.
Thanks for the help.

Anon

Did you used to go to CHS by any chance or is that a different Melissa?

melrse

It must be a different Melissa.

Take care and thanks again.

kparker

There's an important thing missing here, making it a quite-unsafe way to program:

  $result = mysql_query(blah blah blah blah);
  $numrows = mysql_num_rows($result);

What happens if the query fails? $result is not a valid mysql result ID in that case, and so you'll just get an error messages when you try to retrieve the number of rows. Instead, you need to do this:

  $sql = "blah blah blah";
  $result = mysql_query( $sql ); \
  if ( ! $result )
    {
    echo "I tried to ask for $sql<br>";
    echo "but mysql didn't like it because:$sql<br>";
    echo mysql_error();
    }
else
    {
    $numrows = mysql_num_rows($result);
    }

Part of the reason people seem to have so much confusion on this point is that failure, from the standpoint of mysql_query(), has nothing to do with the number of resulting rows found, if any. Rather, it strictly means that there was a syntax or permissions error that prevented the query from being carried out. The programmer, meanwhile, it short-circuiting the process and thinking, "Did I get any matching rows or not?"

melrse

I actually just received that error you spoke of "supplied arguement is not a valid MYSQL request resource."

I think I am a bit confused but will try again.

Anon

Yes, ofcourse but yet, I wouldn't be writing a faulty SQL statement 🙂

melrse

I do understand that !$result is referring to the SQL statement.

For some reason when I changed my SQL statement
to using a variable, i received that error.

Please look at my code and tell me what gives.

if ($Empno) {

$link = mysql_connect("x","x");
mysql_select_db("x") or die ("could not open");
$result = mysql_query("SELECT * FROM x WHERE Lname=$Lname and Empno=$Empno");
$numrows=mysql_num_rows($result);
print $numrows;
mysql_close($link);
}

When i used Lname='name' it worked correctly for both a return $numrows of 0 and 1. As soon as I used the form variable, I received the error supplied arguement is not a valid MYSQL result resource.

What is so annoying is I have 2 PHP4 books in front of me but I think I have confused myself silly.

Thanks,

Melissa

Anon

Try using single qoutes around $Lname and $Empno

Kind Regards

Paul

kparker

I assume you mean that humorously, but for the benefit of those new to the subject: it can be other things than just plain invalid sql syntax.

kparker

This is exactly what I mean by saying you need to check the error code:

Don't say:

$result = mysql_query(...);
$numrows=mysql_num_rows($result);

Instead say:

$result = mysql_query(...);
if (! $result )
  echo "Can't run query: " . mysql_error();
else
  $numrows=mysql_num_rows($result);

Now you'll get a nice error message, right from mysql itself, showing what it didn't like about your query.

melrse

Thanks for all of your help. Things are working as I wanted them to.

melrse

Thanks for all of your help. Things are working now as I wanted.