Query issue

[VTJ]

I'm very new to php and mysql so this might be a very stupid question and I'm apologizing ahead of time. Anyway I have a page titled Apparel.php that is supposed to show the most recent item uploaded from each of it's subpages. In other word Apparel is the $category and it has 4 $subcategories (Dresses.php, Skirts.php, Coats.php and Shorts.php). I was able to successfully have the right items show but my coding is pretty long, which I think may affect the speed of the page from loading. I'm sure there's a way to shorten it by perhaps using just one query but I can't figure out how. I'm actually using 4 queries but only posted 2 to keep it shorter. Thanks ahead of time!

 

<?php 
// Run a select query to get latest item from each subcategory
$dynamicList = "";
$query1 = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Dresses' ORDER BY date_added DESC LIMIT 1");
$query2 = mysql_query("SELECT * FROM products WHERE category='Apparel' AND subcategory='Skirts' ORDER BY date_added DESC LIMIT 1");
$productCount = mysql_num_rows($query1); // count the output amount
   while($row = mysql_fetch_array($query1)){ 
   $id = $row["id"];
   $product_name = $row["product_name"];
   $subcategory = $row["subcategory"];
   $dynamicList .= '
   <ul>
   <li><a href="Apparel/' . $subcategory . '.php"><img src="inventory_images/' . $id . '_1.jpg" alt="' . $product_name . '" width="140" height="210" border="0" /></a>
   <center><h3>' . $subcategory . '</h3></center>
   </li>
   </ul>';
}
$productCount = mysql_num_rows($query2); // count the output amount
   while($row = mysql_fetch_array($query2)){ 
   $id = $row["id"];
   $product_name = $row["product_name"];
   $subcategory = $row["subcategory"];
   $dynamicList .= '
   <ul>
   <li><a href="Apparel/' . $subcategory . '.php"><img src="inventory_images/' . $id . '_1.jpg" alt="' . $product_name . '" width="140" height="210" border="0" /></a>
   <center><h3>' . $subcategory . '</h3></center>
   </li>
   </ul>';
}
mysql_close();
?>

 

MOD EDIT:

 . . . 

BBCode tags added.

[Psycho]

When you are doing something more than once you should think about creating a funciton

 

<?php

//Function to output products for selected subcategory
function displaySubcategoryProducts($subcategory)
{
    $query = "SELECT id, product_name
              FROM products
              WHERE category='Apparel'
                AND subcategory='{$subcategory}'
              ORDER BY date_added DESC LIMIT 1";
    $result = mysql_query($query);

    $htmlOuput .= " <center><h3>{$subcategory}</h3></center>\n";
    $htmlOuput .= "<ul>\n";
    while($row = mysql_fetch_array($query1))
    {
        $htmlOuput .= "<li>";
        $htmlOuput .= "<a href=\"Apparel/{$subcategory}.php\">";
        $htmlOuput .= "<img src=\"inventory_images/{$id}_1.jpg\" alt=\"{$product_name}\" width=\"140\" height=\"210\" border=\"0\" />";
        $htmlOuput .= "</a>";
        $htmlOuput .= "</li>\n";
    }
    $htmlOuput .= "<ul>\n";
    return $htmlOutput;
}

//Execute one line for each subcategory to display
echo displaySubcategoryProducts('Dresses');
echo displaySubcategoryProducts('Skirts');
echo displaySubcategoryProducts('Pants');
echo displaySubcategoryProducts('Hats');

?>

[awjudd]

If the result sets are going to be the same, you could make a function and then call that all 4 times just passing in the array coming back from the database.

 

I noticed that you had LIMIT 1 at the end of your query but all of your mysql_fetch_array's are in a while loop ... is this for any particular reason?  If you want multiple rows, you should remove the LIMIT 1 from the query and if you want only one row returned, you should remove it from being in a loop since it isn't necessary.

 

That said, you can probably join all 4 of your queries together and with a bit more logic split all of the sections up just by watching the subcategory until it changes ...

 

~judda

[Pikachu2000]

Duplicate topics merged. Do not double post.

 

When posting code, please enclose it within the forum's

 . . . 

BBCode tags.