#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0

    PHP and MySQL Product Display page - how to link columns and exclude products


    I know this must be a very basic question but I've been working very hard to find answers and I've gone through multiple PHP and MySQL tutorials but I can't find how to do these things (or which learning resources are focused on them).

    Basically, I have a MySQL database with products in a table called products and the table has columns like "productCategory" which are INT values. I have another category called category which has auto increments in the first column (categoryID) and values like "First Category" etc. in the second column (categoryName).

    I learned how to use while statements to show results of a database, but I'm pretty stuck now.

    Here's the type of thing that I can do so far:

    <?php

    $query = mysql_query("SELECT `productID`, `productCategory` FROM `products`");
    {

    }

    while ($row = mysql_fetch_assoc($query)) {

    echo 'ID: '.$row['productID'].'<br />Category: '.$row ['productCategory'];
    }

    ?>

    What I want to have is a product page that shows the category as the categoryName column in the category table.

    I also want to know how I can have links on the page that append a url parameter that when picked up will exclude products based on that value. Like maxprice=40 only shows products with prices at or below 40 and maxprice=40&minrating=4 does the same and also only shows products rated 4 and above.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Code:
    SELECT products.productID
         , category.categoryName
      FROM products
    INNER
      JOIN category
        ON category.categoryID = products.productCategory
    as for your second question, i'm moving your thread to the php forum for help on pulling parameter values off the url

    Comments on this post

    • Jimmy #1 agrees : helpful
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,759
    Rep Power
    9397
    For stuff in the URL $_GET is your first step. You'll have to grab the various values and construct the query as needed.

    One simple way is like
    PHP Code:
    $conditions = array();

    $minprice = (isset($_GET["minprice"]) ? (float)$_GET["minprice"] : 0);
    $maxprice = (isset($_GET["maxprice"]) ? (float)$_GET["maxprice"] : 0);
    $minrating = (isset($_GET["minrating"]) ? (int)$_GET["minrating"] : 0);
    $maxrating = (isset($_GET["maxrating"]) ? (int)$_GET["maxrating"] : 0);

    if (
    $minprice 0) {
        
    $conditions[] = "`price` >= {$minprice}";
    }
    if (
    $maxprice 0) {
        
    $conditions[] = "`price` <= {$maxprice}";
    }
    if (
    $minrating 0) {
        
    $conditions[] = "`rating` >= {$minrating}";
    }
    if (
    $maxrating 0) {
        
    $conditions[] = "`rating` <= {$maxrating}";
    }

    // ...

    $sql "
        SELECT products.productID
             , category.categoryName
          FROM products
        INNER
          JOIN category
            ON category.categoryID = products.productCategory
    "
    ;

    if (
    $conditions) {
        
    $sql .= " WHERE " implode(" AND "$conditions);
    }

    // execute $sql 

    Comments on this post

    • Jimmy #1 agrees : helpful
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT products.productID
         , category.categoryName
      FROM products
    INNER
      JOIN category
        ON category.categoryID = products.productCategory
    as for your second question, i'm moving your thread to the php forum for help on pulling parameter values off the url
    OK thanks a lot, I appreciate the help even though I'm pulling hair out of my head trying to read and understand how these JOINS work.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Thanks requinix.

    Originally Posted by requinix
    For stuff in the URL $_GET is your first step. You'll have to grab the various values and construct the query as needed.

    One simple way is like
    Code:
    $conditions = array(); 
    
    $minprice = (isset($_GET["minprice"]) ? (float)$_GET["minprice"] : 0); 
    $maxprice = (isset($_GET["maxprice"]) ? (float)$_GET["maxprice"] : 0); 
    $minrating = (isset($_GET["minrating"]) ? (int)$_GET["minrating"] : 0); 
    $maxrating = (isset($_GET["maxrating"]) ? (int)$_GET["maxrating"] : 0);
    What this says is that if $_GET is set then these variables are the same value, otherwise these variables are 0, is that correct?

    Code:
    if ($minprice > 0) { 
        $conditions[] = "`price` >= {$minprice}"; 
    } 
    if ($maxprice > 0) { 
        $conditions[] = "`price` <= {$maxprice}"; 
    } 
    if ($minrating > 0) { 
        $conditions[] = "`rating` >= {$minrating}"; 
    } 
    if ($maxrating > 0) { 
        $conditions[] = "`rating` <= {$maxrating}"; 
    }
    So this means that when these variables are set, they are entered into the $conditions array with the relevant MySQL variable and then a <= or =>?

    Code:
    $sql = " 
        SELECT products.productID 
             , category.categoryName 
          FROM products 
        INNER 
          JOIN category 
            ON category.categoryID = products.productCategory 
    ";
    I still don't get this but I'll be able to use it at least.


    Code:
    if ($conditions) { 
        $sql .= " WHERE " . implode(" AND ", $conditions); 
    }
    From what I can see, this adds the WHERE clause with the values of the $conditions array starting with " AND " to the $sql variable if $conditions has anything in it. Is this correct?

    Please tell me if everything I said was right because I'm not sure if I understand it correctly.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in ***** on line 8
    Code:
    $query = mysql_query("SELECT products.productID, category.categoryName FROM `products` INNER JOIN `category` ON `category.categoryID` = `products.productCategory`");
    
    while ($row = mysql_fetch_assoc($query)) {
    	
    echo $row['productID'], $row ['productCategory'];
    	}
    Line 8 is
    Code:
    while ($row = mysql_fetch_assoc($query)) {
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Originally Posted by Jimmy #1
    Code:
     ON `category.categoryID` = `products.productCategory`
    why did you stick those backticks in there?

    they weren't in the query i gave you

    they're what's causing your query to fail
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    why did you stick those backticks in there?

    they weren't in the query i gave you

    they're what's causing your query to fail
    My mistake, I had watched a tutorial that said to put backticks or maybe I mixed that up. The query works now EDIT: forgot to insert values into the category table.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Thanks a lot guys, you have been a great help.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Originally Posted by Jimmy #1
    I had watched a tutorial that said to put backticks or maybe I mixed that up.
    any tutorial that says you have to use backticks is crap
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo