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,806
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Try echo $sql2; die; then copy and paste the query into phpmyadmin

  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,069
    Mentioned
    152 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.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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,806
    Mentioned
    73 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/

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,069
    Mentioned
    152 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=
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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,069
    Mentioned
    152 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
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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,069
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Can you provide the HTML form too?
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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,069
    Mentioned
    152 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.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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
  •