MySQL problems with mysql_result() & mysql_num_rows()

When I try using either one of those functions I get this type of error: supplied argument is not a valid MySQL result resource
mysql_result(): supplied argument is not a valid MySQL result resource
I get the same thing for mysql_num_rows()

Any ideas?

zippy21Asked:
Who is Participating?
 
Hamlet081299Connect With a Mentor Commented:
How do you know "the query works fine"?

I created a table as you specified with password as VARCHAR(255), and run the code and IT WORKED!!!

But then I did something which I knew would trip it up...

I changed to userid to have a quote character in it, and voila I get ...
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in testpass.php on line 11

So then I went back and added and error check to the m mysql_query...

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

... and then (as I expected) I get ...

You have an error in your SQL syntax near 'sh')' at line 1

Did you actually try the error checking?  Or were you assuming that because your test case worked in MySql or worked once then the query was okay?

So ... First thing is check your inputs ($userid and $pass).  If they ever contain apostrophes then therein lies the problem.

The solution is to do this ...

$userid = addslashes($userid);
$password = addslashes($password);

... which should protect you from those nasty quotes.
0
 
Richard QuadlingSenior Software DeveloperCommented:
Can you show your PHP code.

Normally, you would do something like this ...


<?php
    /* Connecting, selecting database */
    $link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
        or die("Could not connect");
    print "Connected successfully";
    mysql_select_db("my_database") or die("Could not select database");

    /* Performing SQL query */
    $query = "SELECT * FROM my_table";
    $result = mysql_query($query) or die("Query failed");

    /* Printing results in HTML */
    print "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        print "\t<tr>\n";
        foreach ($line as $col_value) {
            print "\t\t<td>$col_value</td>\n";
        }
        print "\t</tr>\n";
    }
    print "</table>\n";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link);
?>


(Straight from the PHP manual).

Richard.
0
 
zippy21Author Commented:
This is with the mysql_num_rows method:

?
session_start();

if ($userid && $password)
{
  // if the user has just tried to log in

  $db_conn = mysql_connect("localhost")
  mysql_select_db("dbauthorization", $db_conn);
  $query = "select * from tblvalidation "
           ."where name='$userid' "
           ." and pass=password('$password')";
  $result = mysql_query($query, $db_conn);
  if (mysql_num_rows($result) >0 )
  {
    // if they are in the database register the user id
    $valid_user = $userid;
    session_register("valid_user");
  }
}
?>

This is with the count method:

<?
session_start();

 if (isset($userid) && ($password))
{
  // if the user has just tried to log in

  $db_conn = mysql_connect("localhost");
  mysql_select_db("dbauthorization", $db_conn);
  $query = "select Count(*) from tblvalidation"
           ."where username='$userid' "
           ." and pass=password('$password')";
  $result = mysql_query($query, $db_conn);
  $count=mysql_result($result,0,"count(*)");
if ($count > 0)

  {
    // if they are in the database register the user id
    $valid_user = $userid;
    session_register("valid_user");
  }
}
?>


0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Hamlet081299Commented:
The error you are getting indicates that your query FAILED.

Try this...

$result = mysql_query($query, $db_conn)
  or die(mysql_error());
0
 
Hamlet081299Commented:
Is "password()" a MySQL function?
0
 
TherilCommented:
> Is "password()" a MySQL function?

Yes, it returns a hash for the string it gets as an argument. Passwords in user-table of mysql-database are stored as hashes, not plain text, to increase security.

SELECT password("Foo")
returns
72ec97820a2efefb

When user tries to log in, hashes are compared, not passwords themselves. Eg.

SELECT password("Foo") AND '72ec97820a2efefb'
returns 1 (true).
0
 
TherilCommented:
> SELECT password("Foo") AND '72ec97820a2efefb
Sorry, this should of course be:
SELECT password("Foo") = '72ec97820a2efefb
Otherwise it would return true always as long as one (or both) of the strings are '0'
0
 
quangntCommented:
The function mysql_result() returns the contents of one cell from a MySQL result set. It cannot be used with multiple cells.
so you should use as following:

 $query = "select Count(*) as user_count from tblvalidation"."where username='$userid' "." and pass='$password'";
 $result = mysql_query($query, $db_conn);
 $count=mysql_result($result,0,"user_count");

if ($count > 0)

 {
   // if they are in the database register the user id
   $valid_user = $userid;
   session_register("valid_user");
 }


I hope this will make you satisfy.
best regards
0
 
zippy21Author Commented:
does not work, I still get the same error:
Warning: mysql_result(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\chapter20\passprotect.php on line 13

is something not set up right on my machine?  I am running iis 5.0 and win2000....
0
 
Richard QuadlingSenior Software DeveloperCommented:
Does the query fail? Have you added the ...

or die("Query failed")

?

0
 
TherilCommented:
Your SQL-query propably has an error. Try this one:

$qresult = mysql_query($query) or print(mysql_error());
0
 
Richard QuadlingSenior Software DeveloperCommented:
Theril, see the the first reply AND Hamlet's reply.

Same thing.
0
 
Hamlet081299Commented:
If your sql is okay the problme may be even further back...

Try checking for errors on you connect and select db...
  $db_conn = mysql_connect("localhost")
    or die('connect failed');
  mysql_select_db("dbauthorization", $db_conn)
    or die('select db failed');
 
I also noticed that there was NO SEMICOLON at the end of...
  $db_conn = mysql_connect("localhost")

Did you copy and paste the code directly?  The reason I ask is that I don't see any reason the sql would fail, unless either the table name or one of the field names is wrong.  Please double check these.
0
 
zippy21Author Commented:
the query works fine...do you think it could be how I defined the pass field in my database?  it is just pass varchar(255)

I know there is data in there because I wrote a php page to view the data in the database.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.