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

Dev Shed Forums Sponsor:

October 29th, 2012, 02:01 PM

Jimmy #1

Registered User

Join Date: Oct 2012

Posts: 9

Time spent in forums: 2 h 58 m 51 sec
Reputation 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.

October 29th, 2012, 02:11 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 53 m 18 sec
Reputation Power: 4140

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

October 29th, 2012, 02:38 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,701

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 5 h 23 m 54 sec
Reputation Power: 8969

Send a message via Google Talk to 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

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

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

October 29th, 2012, 02:39 PM

Jimmy #1

Registered User

Join Date: Oct 2012

Posts: 9

Time spent in forums: 2 h 58 m 51 sec
Reputation Power: 0

Quote:

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.

October 29th, 2012, 03:15 PM

Jimmy #1

Registered User

Join Date: Oct 2012

Posts: 9

Time spent in forums: 2 h 58 m 51 sec
Reputation Power: 0

Thanks requinix.

Quote:

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.

October 29th, 2012, 03:39 PM

Jimmy #1

Registered User

Join Date: Oct 2012

Posts: 9

Time spent in forums: 2 h 58 m 51 sec
Reputation Power: 0

Quote:

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)) {

October 29th, 2012, 03:49 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 53 m 18 sec
Reputation Power: 4140

Quote:

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

October 29th, 2012, 03:56 PM

Jimmy #1

Registered User

Join Date: Oct 2012

Posts: 9

Time spent in forums: 2 h 58 m 51 sec
Reputation Power: 0

Quote:

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.