Warning: mysql_num_rows(): supplied argument... error

Hello everyone.

I would appreciate it if someone could help me with this. I have built an online store around a year ago (it’s not a real store - I just wanted to learn php and mysql). However, I’ve changed hosting providers in the meantime and when I imported the database to once again get the store up and running, I ran into quite a few problems. Everything worked 100% before and now I’m getting this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/hopefloatsdesign.co.za/httpdocs/shopping/list.php on line 91

This is the php code:


<?php 
session_start();

// Script Error Reporting
    error_reporting(E_ALL);
    ini_set('display_errors', '1');
 
// Check to see the URL variable is set and that it exists in the database
    if (isset($_GET['category'])){
    $category=$_GET['category'];
    $subcategory=$_GET['subcategory'];
    
// Connect to the MySQL database  
    include "storescripts/connect_to_mysql.php"; 
    $sql = mysql_query("SELECT * FROM products WHERE subcategory='$subcategory'");
    
// Adam's Pagination Logic
$nr = mysql_num_rows($sql); // Get total of Num rows from the database query
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers
} else { // If the pn URL variable is not present force it to be value of page number 1
    $pn = 1;
} 

unset($_GET['pn']);
$MyURL = $_SERVER['PHP_SELF']."?".http_build_query($_GET);

//This is where we set how many database items to show on each page 
$itemsPerPage = 5; 

// Get the value of the last page in the pagination result set
$lastPage = ceil($nr / $itemsPerPage);
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
if ($pn < 1) { // If it is less than 1
    $pn = 1; // force if to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
    $pn = $lastPage; // force it to be $lastpage's value
} 
// This creates the numbers to click in between the next and back buttons
$centerPages = "";
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
} else if ($pn == $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub2 . '">' . $sub2 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add2 . '">' . $add2 . '</a> &nbsp;';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
    $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    $centerPages .= '&nbsp; <a href="' . $MyURL.'&pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
}
// This line sets the "LIMIT" range
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 
// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below
$sql2 = mysql_query("SELECT * FROM products WHERE subcategory='$subcategory' $limit"); 
// END Adam's Pagination Logic
// Adam's Pagination Display Setup
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is not equal to 1 
if ($lastPage != "1"){
// This shows the user what page they are on, and the total number of pages
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '&nbsp;  &nbsp;  &nbsp; ';
// If we are not on page 1 we can place the Back button
    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $MyURL.'&pn=' . $previous . '"> Back</a> ';
    } 
// Lay in the clickable numbers display here between the Back and Next links
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
    // If we are not on the very last page we can place the Next button
    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
        $paginationDisplay .=  '&nbsp;  <a href="' . $MyURL.'&pn=' . $nextPage . '"> Next</a> ';
    } 
}
// END Adam's Pagination Display Setup
    
    $dynamicList = "";
    $productCount = mysql_num_rows($sql2); // count the output amount
    if ($productCount > 0) {
        // get all the product details
        while($row = mysql_fetch_array($sql2)){ 
             $id = $row["id"];
             $product_name = $row["product_name"];
             $price = $row["price"];
             $details = $row["details"];
             $category = $row["category"];
             $subcategory = $row["subcategory"];
             $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
         $dynamicList .= '<table style="float: left;" width="50%" border="0" cellspacing="0" cellpadding="6">
        <tr>
          <td width="17%" valign="top"><a href="product.php?id=' . $id . '"><img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="77" height="102" border="1" /></a></td>
          <td width="83%" valign="top">' . $product_name . '<br />
            R' . $price . '<br />
            <a href="product.php?id=' . $id . '">View Product Details</a></td>
        </tr>
      </table>';
    }
    }
} else {
    $dynamicList = "We have no products listed in our store yet";
}
?>

And this is the page giving the error (hope I’m allowed to give a link?)
http://www.hopefloatsdesign.co.za/shopping/list.php?category=clothing&subcategory=hats

You’ll notice that none of my subcategories are working and whenever I add products the category and subcategory are no longer added to the database. I’m completely stuck… Can someone point me in the right direction?

Try echo $sql2; die; then copy and paste the query into phpmyadmin

Thanks for your reply John, but I have no idea how to do that. :confused:

Two ways really:

$sql2 = mysql_query("SELECT * FROM products WHERE subcategory='$subcategory' $limit") or die(mysql_error());
$sql2 = mysql_query("SELECT * FROM products WHERE subcategory='$subcategory' $limit");
die("SELECT * FROM products WHERE subcategory='$subcategory' $limit");

The latter will show you the query it was executing (not nececssarily true for the first), either should give you an indication as to what is going wrong.

$sql2 = mysql_query("SELECT * FROM products WHERE subcategory='$subcategory' $limit");
die("SELECT * FROM products WHERE subcategory='$subcategory' $limit");

I used the above and this is what I get when I now try to open the page:

SELECT * FROM products WHERE subcategory=‘hats’ LIMIT -5,5

Something seems to be wrong with the LIMIT?

Hi @susanv;

I just checked your website and noticed it is showing:

SELECT * FROM products WHERE subcategory=‘hats’ LIMIT -5,5

I Googled “Mysql manual LIMIT” to confirm that the LIMIT starts at 0 so what you are requesting is to start the search five records before the first record.

http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

Definition: Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display).

>>> Thanks for your reply John, but I have no idea how to do that
Hopefully you have a copy of your database on your computer and you can select your database, choose the SQL tab at the top then paste your query into the textbox.

http://localhost/phpmyadmin/

Okay, so I’m walking through your code and the first thing that pops into my mind is 1) how many products do you actually have entered, and 2) do they have sub-categories defined (and if so, what are the values)?

The first place $pn can become 0 and thus cause a -5, 5 limit is line 37

// Get the value of the last page in the pagination result set
    $lastPage = ceil($nr / $itemsPerPage); -- THIS CAN EVALUATE TO ZERO
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
    if ($pn < 1) { // If it is less than 1
        $pn = 1; // force if to be 1
    } else if ($pn > $lastPage) { // if it is greater than $lastpage
        $pn = $lastPage; // force it to be $lastpage's value -- THIS LINE!
    }

Here is how:
If $sql = mysql_query(“SELECT * FROM products WHERE subcategory=‘$subcategory’”); returns 0 rows, $nr in the above code will be 0, 0 / 5 is still 0, ceil of 0 is 0. $pn is already defaulted to 1 (because it does that immediately after the $sql query). Since 1 is not less than 1, it falls to the else if, which is comparing 1 > 0, and it is, so it sets $pn to 0.

Phew! That’s a lot to take in, but it pinpoints that your issue is likely with your data, the fact that the products table is not returning any rows for “SELECT * FROM products WHERE subcategory=‘hats’”

I want to use the following URL as a proof of concept (notice I left subcategory blank)
http://www.hopefloatsdesign.co.za/shopping/list.php?category=clothing&subcategory=

@ John, I’ve noticed the -5,5 LIMIT as well and cpradio has explained how that could have occurred. Just scratching my head at this point to try and figure out how to correct it.

@ cpradio, I’m guessing the problem begins when I try to add new products because neither the category or the sub-category gets added to the database for some reason.

Thank you both.

That sounds like a good place to start :slight_smile: If you need help with that, feel free to post some code so we can see why it might not be adding the category and sub-category

Thank you cpradio. This is the code for the adding inventory page. Nothing wrong with the database it seems because I just manually added a product and it worked fine. So the problem must lie somewhere in this code:

<?php 
session_start();
if (!isset($_SESSION["manager"])) {
    header("location: admin_login.php"); 
    exit();
}

// Be sure to check that this manager SESSION value is in fact in the database
$managerID = preg_replace('#[^0-9]#i', '', $_SESSION["id"]); // filter everything but numbers and letters
$manager = preg_replace('#[^A-Za-z0-9]#i', '', $_SESSION["manager"]); // filter everything but numbers and letters
$password = preg_replace('#[^A-Za-z0-9]#i', '', $_SESSION["password"]); // filter everything but numbers and letters

// Run mySQL query to be sure that this person is an admin and that their password session 
// var equals the database information

// Connect to the MySQL database  
include "../storescripts/connect_to_mysql.php"; 
$sql = mysql_query("SELECT * FROM admin WHERE id='$managerID' AND username='$manager' AND password='$password' LIMIT 1"); // query the person

// Make sure the person exists in the database
$existCount = mysql_num_rows($sql); // count the row nums
if ($existCount == 0) { // evaluate the count
	 echo "Your login session data is not on record in the database.";
     exit();
}
?>

<?php 
// Script Error Reporting
error_reporting(E_ALL);
ini_set('display_errors', '1');
?>
<?php 

// Delete Item Question to Admin, and Delete Product if they choose
if (isset($_GET['deleteid'])) {
	echo 'Do you really want to delete product with ID of ' . $_GET['deleteid'] . '? <a href="inventory_list.php?yesdelete=' . $_GET['deleteid'] . '">Yes</a> | <a href="inventory_list.php">No</a>';
	exit();
}
if (isset($_GET['yesdelete'])) {

	// remove item from system and delete its picture
	// delete from database
	$id_to_delete = $_GET['yesdelete'];
	$sql = mysql_query("DELETE FROM products WHERE id='$id_to_delete' LIMIT 1") or die (mysql_error());

	// unlink the image from server
	// Remove The Pic -------------------------------------------
    $pictodelete = ("../inventory_images/$id_to_delete.jpg");
    if (file_exists($pictodelete)) {
       		    unlink($pictodelete);
    }
	header("location: inventory_list.php"); 
    exit();
}
?>

<?php 
// Parse the form data and add inventory item to the system
if (isset($_POST['product_name'])) {
	
    $product_name = mysql_real_escape_string($_POST['product_name']);
	$price = mysql_real_escape_string($_POST['price']);
	$category = mysql_real_escape_string($_POST['category']);
	$subcategory = mysql_real_escape_string($_POST['subcategory']);
	$details = mysql_real_escape_string($_POST['details']);
	// See if that product name is an identical match to another product in the system
	$sql = mysql_query("SELECT id FROM products WHERE product_name='$product_name' LIMIT 1");
	$productMatch = mysql_num_rows($sql); // count the output amount
    if ($productMatch > 0) {
		echo 'Sorry you tried to place a duplicate "Product Name" into the system, <a href="inventory_list.php">click here</a>';
		exit();
	}
	// Add this product into the database now
	$sql = mysql_query("INSERT INTO products (product_name, price, details, category, subcategory, date_added) 
        VALUES('$product_name','$price','$details','$category','$subcategory',now())") or die (mysql_error());
     $pid = mysql_insert_id();
	// Place image in the folder 
	$newname = "$pid.jpg";
	move_uploaded_file( $_FILES['fileField']['tmp_name'], "../inventory_images/$newname");
	header("location: inventory_list.php"); 
    exit();
}
?>

<?php 
// This block grabs the whole list for viewing
$product_list = "";
$sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC");
$productCount = mysql_num_rows($sql); // count the output amount
if ($productCount > 0) {
	while($row = mysql_fetch_array($sql)){ 
             $id = $row["id"];
			 $product_name = $row["product_name"];
			 $price = $row["price"];
			 $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
			 $product_list .= "Product ID: $id - <strong>$product_name</strong> - R$price - <em>Added $date_added</em> &nbsp; &nbsp; &nbsp; <a href='inventory_edit.php?pid=$id'>edit</a> &bull; <a href='inventory_list.php?deleteid=$id'>delete</a><br />";
    }
} else {
	$product_list = "You have no products listed in your store yet";
}
?>

Any advice would be much appreciated.

Can you provide the HTML form too?

Here you go:

<div class="box2">

	<div align="right" style="margin-right:32px;">
	<a href="inventory_list.php#inventoryForm">Add New Inventory Item</a>
	</div>

	<div align="left" style="margin-left:24px;">
    <h3>Inventory list</h3>
    <?php echo $product_list; ?>
    </div>

	</div>
    
	<div class="box2">
    <a name="inventoryForm" id="inventoryForm"></a>
    <h3>Add New Inventory Item Form</h3>
	
	<form action="inventory_list.php" FORM name="drop_list" id="drop_list" method="post">
    <table width="90%" border="0" cellspacing="0" cellpadding="6">
      <tr>
        <td width="20%" align="right">Product Name</td>
        <td width="80%"><label>
          <input name="product_name" type="text" id="product_name" size="40" />
        </label></td>
      </tr>
      <tr>
        <td align="right">Product Price</td>
        <td><label>
          R
          <input name="price" type="text" id="price" size="12" />
        </label></td>
      </tr>
      <tr>
        <td align="right">Category</td>
        <td><label>
          
		<SELECT  NAME="Category" id="Category" onChange="SelectSubCat();" >
		<Option value="">Category</option>
		</SELECT>
		  
        </label></td>
      	</tr>
      	<tr>
        <td align="right">Subcategory</td>
        <td>
		
		<SELECT id="SubCat" NAME="SubCat">
		<Option value="">SubCategory</option>
		</SELECT>
		
		</td>
    	</tr>
	    <tr>
        <td align="right">Product Details</td>
        <td><label>
          <textarea name="details" id="details" cols="40" rows="5"></textarea>
        </label></td>
      </tr>
      <tr>
        <td align="right">Product Image</td>
        <td><label>
          <input type="file" name="fileField" id="fileField" />
        </label></td>
      </tr>      
      <tr>
        <td>&nbsp;</td>
        <td><label>
          <input type="submit" name="button" id="button" class="submit" value="Add This Item Now" />
        </label></td>
      </tr>
    </table>
    </form>
    </div>
		
	</div>

Okay, looks like the names of your fields do not match what you have in your code. Change NAME=“SubCat” to name=“subcategory” and change NAME=“Category” to name=“category”

That should help line up the form names with your code and thus allow it to insert that data.

Thank you so very much cpradio, that did the trick! Much appreciated.