SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Location
    South Africa
    Posts
    24
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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 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 && $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 && $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/sh...bcategory=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?
    Last edited by spikeZ; Mar 5, 2013 at 06:02.

  2. #2
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,880
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    Try echo $sql2; die; then copy and paste the query into phpmyadmin
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  3. #3
    SitePoint Member
    Join Date
    Apr 2011
    Location
    South Africa
    Posts
    24
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply John, but I have no idea how to do that.

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,214
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Two ways really:

    1)
    PHP Code:
    $sql2 mysql_query("SELECT * FROM products WHERE subcategory='$subcategory$limit") or die(mysql_error()); 
    2)
    PHP Code:
    $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.

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Location
    South Africa
    Posts
    24
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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?

  6. #6
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,880
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    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/
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,214
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    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
    PHP Code:
    // 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/sh...g&subcategory=

  8. #8
    SitePoint Member
    Join Date
    Apr 2011
    Location
    South Africa
    Posts
    24
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    @ 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.

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,214
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by susanv View Post
    @ 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 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

  10. #10
    SitePoint Member
    Join Date
    Apr 2011
    Location
    South Africa
    Posts
    24
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 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.
    Last edited by cpradio; Mar 5, 2013 at 07:49. Reason: Added [php] tags

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,214
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Can you provide the HTML form too?

  12. #12
    SitePoint Member
    Join Date
    Apr 2011
    Location
    South Africa
    Posts
    24
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here you go:

    HTML Code:
    <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>
    Last edited by cpradio; Mar 5, 2013 at 08:46. Reason: Added [html] tags

  13. #13
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,214
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SitePoint Member
    Join Date
    Apr 2011
    Location
    South Africa
    Posts
    24
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you so very much cpradio, that did the trick! Much appreciated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •