Solved

MySQL problems with mysql_result() & mysql_num_rows()

Posted on 2002-07-15
16
528 Views
Last Modified: 2008-03-10
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?

0
Comment
Question by:zippy21
  • 4
  • 3
  • 3
  • +2
16 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 7155336
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
 

Author Comment

by:zippy21
ID: 7155422
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
 
LVL 5

Expert Comment

by:Hamlet081299
ID: 7155804
The error you are getting indicates that your query FAILED.

Try this...

$result = mysql_query($query, $db_conn)
  or die(mysql_error());
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 5

Expert Comment

by:Hamlet081299
ID: 7155806
Is "password()" a MySQL function?
0
 

Expert Comment

by:Theril
ID: 7155852
> 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
 

Expert Comment

by:Theril
ID: 7155863
> 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
 

Expert Comment

by:quangnt
ID: 7156463
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
 

Author Comment

by:zippy21
ID: 7162055
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
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 7162074
Does the query fail? Have you added the ...

or die("Query failed")

?

0
 

Expert Comment

by:Theril
ID: 7162097
Your SQL-query propably has an error. Try this one:

$qresult = mysql_query($query) or print(mysql_error());
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 7162174
Theril, see the the first reply AND Hamlet's reply.

Same thing.
0
 
LVL 5

Expert Comment

by:Hamlet081299
ID: 7163532
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
 

Author Comment

by:zippy21
ID: 7165732
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
 
LVL 5

Accepted Solution

by:
Hamlet081299 earned 50 total points
ID: 7166423
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

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question