Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Question

I am getting the following error message on the code below (which is at the end of the query):

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ../view-ind-order.php on line 28

This script is supposed to retrieve the order (from a page which lists all of the order_id rows from the orders table), the contents of the order, the user who ordered and the product information. I think where I'm getting the error is where there is more than one product within the order but I can't quite see where I'm going wrong. (the header has a session start command)

     <?php 
      $page_title = 'Order';
      include ('./includes/header.html');

      if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) )
      {    
       $id = $_GET['id'];
       } elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) )
       {    
       $id = $_POST['id'];
       } else { 
       echo 'This page has been accessed in error';
       include ('./includes/header.html'); 
       exit();
      }

 require_once ('mydatabase.php'); 

 $query = "SELECT us.users_id, us.users_first_name, us.users_surname, us.users_business, 
             ord.order_id, ord.users_id, ord.total, ord.order_date,  
             oc.oc_id, oc.order_id, oc.products_id, oc.quantity, oc.price
             prd.products_id, prd.products_name, prd.price      
         FROM users AS us, orders AS ord, order_contents AS oc, products AS prd  
         WHERE ord.order_id=$id
         AND us.users_id = ord.users_id
         AND ord.order_id = oc.order_id
         AND oc.products_id = prd.products_id    
         ";
         
 $result = @mysql_query ($query); 

 if (mysql_num_rows($result) == 1) { 

    $row = mysql_fetch_array ($result, MYSQL_NUM);

    echo '
    <table>
    <tr>
    <td><strong>Name:</strong></td>
    <td>' . $row[1] . ' ' . $row[2] . '</td>
    </tr>
    <tr>
    <td><strong>Business Name</strong></td>
    <td>' . $row[4] . '</td>
    </tr>   
    <tr>
    <td><strong>Total:</strong></td>
    <td>' . $row[7] . '</td>
    </tr>
    <tr>
    <td><strong>Quantity</strong></td>
    <td>' . $row[12] . '</td>
    </tr>
    <tr>
    <td><strong>Product:</strong></td>
    <td>' . $row[15] . '</td>
    </tr>   
    <tr>
    <td><strong>Price:</strong></td>
    <td>' . $row[13] . '</td>
    </tr>
    </table>
    ';

} else { 
    echo '<h1 id="mainhead">Page Error</h1>
    <p class="error">This page has been accessed in error.</p><p><br /><br /></p>';   
}
mysql_close();

include ('./includes/footer.html');
?>

This probably means there's an error in your mySQL query. Change the `@mysql_query($query);` line to something like `@mysql_query($query) or die(mysql_error());` — Jimmy Sawczuk, May 19 '11 at 13:41
There might be an error with your SELECT-Statement (variable $query). Try isolating the select statement, something like `$result = mysql_query ($query) or die(mysql_error());` to find out whats wrong with it. — Bjoern, May 19 '11 at 13:44

score 7 · Accepted Answer · edited Oct 20 '12 at 10:02

7

Change $result = @mysql_query ($query);

with

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

and see if you have any errors.

EDIT:

You missed a comma after oc.price and before prd.products_id. Change your query like this:

$query = "SELECT us.users_id, us.users_first_name, us.users_surname, us.users_business, 
             ord.order_id, ord.users_id, ord.total, ord.order_date,  
             oc.oc_id, oc.order_id, oc.products_id, oc.quantity, oc.price/*here*/,/**/
             prd.products_id, prd.products_name, prd.price      
         FROM users AS us, orders AS ord, order_contents AS oc, products AS prd  
         WHERE ord.order_id=$id
         AND us.users_id = ord.users_id
         AND ord.order_id = oc.order_id
         AND oc.products_id = prd.products_id    
         ";

edited Oct 20 '12 at 10:02

Paul Fleming

24,238
8
76
113

answered May 19 '11 at 13:42

CristiC

22,068
12
57
89

I got this '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 '.products_id, prd.products_name, prd.price FROM users AS us, orders AS o' at line 4' – AdamMc May 19 '11 at 13:50
put a comma after oc.price and before prd.products_id – CristiC May 19 '11 at 13:52
The good news: It retrieved the orders when one product was ordered. The bad news: It would display 'This page has been accessed in error' if there was more than one order. – AdamMc May 19 '11 at 14:00
This means that more than 1 row was returned (the order has more than 1 products). From your query I see that only when an order has exactly 1 product, then it is display. Otherwise it will give you that message. – CristiC May 19 '11 at 14:08
I removed the '== 1' from the query and this worked, thanks for your help Parkyprg and to everyone else who responded! – AdamMc May 20 '11 at 00:27

score 1 · Answer 2 · answered May 19 '11 at 13:42

1

you probably have an error in your sql,

show it like:

if (!mysql_query($query, $link)) {
    echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
}

answered May 19 '11 at 13:42

Haim Evgi

123,187
45
217
223

Headshota · Answer 3 · 2012-10-20T17:19:32.290

0

apparently a query is causing an error:

 $result = mysql_query ($query) or trigger_error(mysql_error(),E_USER_ERROR);

echo the error to find out what is causing the problem.

edited Oct 20 '12 at 17:19

answered May 19 '11 at 13:42

Headshota

21,021
11
61
82

Spudley · Answer 4 · 2011-05-19T13:50:01.287

You need to check that $result is valid before calling mysql_num_rows() (ie usually immediately after mysql_query(), otherwise you can get this error.

However, if $result isn't a valid resource, then it means that your query failed, so the problem is earlier than mysql_num_rows().

I note that you're using @ to supress any errors thrown by mysql_query(). Why are you doing this? If you're getting an error from it, you need to (a) know about it, and (b) fix it. An error there implies that your SQL query has bugs - you should investigate this before anything else, as this is where your real problem lies.

You can find out what error was thrown by mysql_query() by using the mysql_error() function. It will tell you what the problem is with the query. (at first glance the query looks okay, but you could easily have mis-spelled a field name, or got a field in the wrong table prefix, etc; you'd need to see the error response to know for sure what the problem is)

By the way, is_numeric is not a good check for whether the input is valid or not: it can return true for values which will fail in your query (eg numbers in scientific exponential format, decimal values, etc).

score 0 · Answer 5 · answered May 19 '11 at 13:44

If $result is not a valid MySQL result resource, that means that the query did not return a resource but false, so your query failed.

You should not suppress the error message or at least check for the result of the query to not be false before you proceed using the results:

if (!$result)
{
  // error
}
else
{
  // use results
}

score 0 · Answer 6 · edited Oct 20 '12 at 10:02

0

First : Have you tried the request in a MySQL environement like MySQL Query Browser to know if the request is right and if the request return a result.

Second : Can't you use a normal mysql_query function to send the request to your MySQL server ?

edited Oct 20 '12 at 10:02

Paul Fleming

24,238
8
76
113

answered May 19 '11 at 13:46

pad31

48
1
6

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

6 Answers6