Solved

MySQL problems with mysql_result() & mysql_num_rows()

Posted on 2002-07-15
16
569 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
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…

687 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