Probably very simple, but.

Need help with listing of mySQL results by category in php...

[jofusblue]

Hello everyone, i'll make this brief, as I'm sure its very simple...

I am a designer first and foremost, so php/mySQL coding is relatively new to me, however, I am in the process of building an online store for a small business. I have used a table 'products' to hold all my products, with the following structure;

|id|product_name|price|category|description|product_image

I have been able to create a page which shows ALL the products, however, what I would like to do is have a list of categories down one side which, when clicked will go to a page showing all products with that category. My question, do I have to build a separate page for each category, or can I build one page, and carry through the category variable in the URL rather like the actual product view page I have... (i.e /product.php?id=2).

The code that outputs the products on the main store page is as follows;

<?php 

$dynamic_list = "";
$sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC LIMIT 50");
$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"]));
 $details = $row["details"];
 $dynamic_list .= '<table width="90%" border="0" cellspacing="0" cellpadding="5">
   <tr>
    <td width="16%" valign="top"><a href="product.php?id='. $id .'"><img src="/inventory_images/'. $id .'.jpg" width="100" height="100" /></a></td>
    <td width="84%" valign="top">'. $product_name .'<br />
    £'. $price .'<br />
    '. $details .'<br />
    <a href="product.php?id='. $id .'">View Product</a></td>
   </tr>
   </table>';
 }
} else {
$dynamic_list = "There are no products listed in your store yet";
}
mysql_close();

?>

I apologise if i'm not using the correct terminology, as i said, i'm relatively new to php programming, and have been building this store and a gallery by using bits and bobs from various tutorials around the web and modifying the code to try and figure it all out, learn by doing and all that...

Many thanks in advance!

Joe

[rocknbil]

Welcome aboard jofusblue. Your initial setup is limited, for one example, what happens if they want to add two photos, or a second category, or sub categories? The straightforward solution would be to add a field to the database - which means every time they change your mind, you're modifying the database and programming.

A more expandable method would be

products
|id|product_name|price|description

categories
id|title|

product_categories
id|cat_id|product_id

product_images
id|product_id|product_image|sequence (sequence for arranging order)

Then you would do selections based on mysql joins. This may seem off topic, but if you're just starting it, think forward . . . customers will tell you "only one image" but historically, that always changes. Not maybe . . . always. :-)

To answer your question, the first thing you'd do is take your select out of the msql_query command and build it as a variable, dynamically.

// Set this to null so you don't get concatenation errors
$where=null;

// Although I'm setting it static here, visualize the possibility of using this
// for a search - you can use a "# of results" dropdown, "direction" radio
// button, and "order by" drop down with a list of fields to reset the of results and
// # ordering. This gets important with lots of projects when you need pagination.
$order_by = 'date_added';
$direction = 'desc';
$limit = 50;

// Let's say it's not just category - you can us this to "pull" just the id for
// the product detail page.
$possible_params = ('id','category');

// Now let's buils our where clause 
foreach ($possible_params as $param) { 
if (isset($_GET['$param']) and ! empty($_GET['param'])) { 
 // you only need to add AND if $where has been concatenated at least once. 
 // Note the SPACES. important. 
 if ($where) { $where .= ' and'; } 
 $where .= " $param='" . $_GET['param']. "'"; 
} 

// Your select statement, as you have it now. . . note the SPACE
// before the keyword where. Important.
$query = "select * FROM products";
if ($where) { $query .= " where $where"; }
if ($order_by) { $query .= " order by $order_by"; }
if ($direction) { $query .= " $direction"; }
if ($limit) { $query .= " limit $limit"; }

$sql = mysql_query($query);

This should give you a variety of selects, based on input.
$_GET['category'] = 'Widgets'; // really, this should be numeric, see above
select * from products where category='Widgets' order by date_added desc limit 50

$_GET['id'] = 'Widgets'; // for "product detail" - order/limit not needed
select * from products where id='1234' order by date_added desc limit 50

// No input
select * from products order by date_added desc limit 50

WORD OF WARNING: Injection is not discussed here, this is wide open to mysql injection - but that's a topic for another thread. This is a basic example but gives you the idea of building a dynamic select statement.