Show item from each category

PHP
1

hi all

i have 10-12 CATEGORIES and i want to show 1 most recent added product of each CATEGORY on my homepage

There is “category_id” in product_table


<?
$qry = "SELECT * FROM product_table where category_id=1 order by product_id desc LIMIT 0,1";
$result = mysql_query($qry);
while($row = mysql_fetch_array($result))
{
echo $row['product_id'] . "--". $row['product_name']. "<br>";
}
?>

So should i repeat this code 10 times and replace category id.

Or is there any other shorter solution ??

I mean can this be done in one single query ???

vineet

2

You could try…

SELECT DISTINCT(category_id), product_id, product_name FROM product_table order by product_id desc

*Completely untested!

3

The book I am reading right now would help you out greatly. It is called PHP & Mysql novice to ninja.

First of all, you don’t have to repeat the code 10 times. You need to actually use something called a placeholder. Also, you might want to configure your mysql database to have a hidden category and therefore you can hide all of the products that you don’t want to show on any given page.

Thirdly, from what I gather your code could be better in the fact that it is much more safer and secure if you use prepare statements as opposed to what you are using. Finally, it is best practices that anytime you query the database to wrap your code in TRY/CATCH statements.

Here is an example:


try
  {
    $sql = 'SELECT id, name FROM category where id = :id'
	$s = $pdo->prepare($sql);
	$s->bindValue(:id, $_POST['id'])
	$s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error removing jokes from category.';
	include 'error.html.php';
	exit();
  }

As I see it, your most pressing concern is database design and NOT an sql statement. You must design flexibility into it.