Myslq Query based on User Selection

[cheelo007]

I've been trying to make dynamic queries, where the user determines what will be queried. The reason is because I'd like the images being displayed in my gallery reflect the category selected by the user.
mysql_query("SELECT(whatever category they chose) FROM products")

below is the generation of categories as links. (not sure if it is set up for the task)

<? 
        include "storescripts/connect_to_mysql.php"; 
        $data = mysql_query("SELECT category FROM products") or die(mysql_error());
        $info = mysql_fetch_array( $data );
        while($info = mysql_fetch_array( $data ))
            { 
            $category = $info["category"];
            print '<li><a href="'. $category .'"> ' .  $category . ' </a> </li>';
            }
    ?>

I'm really lost on how to get the images to reflect which category they chose. As of right now, the images are loading with this code.

<?php 
include "storescripts/connect_to_mysql.php"; 
$dynamicList = "";
$sql = mysql_query("SELECT * FROM products ORDER BY date_added DESC LIMIT 10");
$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"];
             $subcategory = $row["subcategory"];
             $date_added = strftime("%b %d, %Y", strtotime($row["date_added"]));
             $dynamicList .= 
    '
    <div>
          <li>
             <a href="product.php?id=' . $id . '"><img style="border:#666 1px solid;" src="inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="180" height="255" border="1" /></a>
            <h4> <a href="product.php?id= ' . $id . '"> ' . $product_name . ' </a> </h4>
            <p>'. $subcategory .'</p> 
            <span>  $' . $price . '</span>
          </li>
    </div>
    ';
    }
} else {
    $dynamicList = "There are no products listed at this time.";
}
mysql_close();
?>

Any help would be greatly appreciated.

Thank you.

[gon1387]

Your SQL query isn't fileterd to get a set of image from a particular category.
Current:
SELECT * FROM products ORDER BY date_added DESC LIMIT 10
Proposed:
SELECT * FROM products WHERE category='[USER_SELECTED_CATEGORY]' ORDER BY date_added DESC LIMIT 10

[cheelo007]

That's what I was hoping to get answerd from you all. I used this code with no luck....

    $sql = mysql_query("SELECT * FROM products WHERE category='%s' ORDER BY date_added DESC LIMIT 10"]", $_GET["selectcat"]);

Below is my dynamic category list, which I hope can be passed through the query.

<? 
include "storescripts/connect_to_mysql.php"; 
$data = mysql_query("SELECT category FROM products") or die(mysql_error());
$info = mysql_fetch_array( $data );
while($info = mysql_fetch_array( $data ))
    { 
    $category = $info["category"];
    print '<li><a id="selectcat" href=""> ' .  $category . ' </a> </li>';
    }
?>

I fear that if I am successful in this, when the user visits the products page, there will not be a query automatically set and the user will see an error or nothing at all on the page.

But I guess that would be a better problem than the one I have now.

[gon1387]

Just a tip though, before running an SQL query in your application dynamically, be sure it runs well with preloaded data or check it first for erros.

Let's first find if you can query this.
SELECT * FROM products WHERE category='[USER_SELECTED_CATEGORY]' ORDER BY date_added DESC LIMIT 10
Be sure that the category you put there exists in your database. And there are products assigned in your selected category. So a sample SQL command you can run with the table below:
QUERY:
SELECT * FROM products WHERE category='bags' ORDER BY date_added DESC LIMIT 10

category
|name       |date_added     |
-----------------------------
|bags       |2012-9-12      |
|dress      |2012-4-14      |
|sunglasses |2012-10-23     |
|shoes      |2012-5-24      |

product
|name       |category   |date_added     |
-----------------------------------------
|jansport   |bags       |2012-6-12      |
|bench      |dress      |2012-3-03      |
|swiss      |bags       |2012-9-23      |
|rayban     |sunglasses |2012-6-21      |

>SELECT * FROM products WHERE category='bags' ORDER BY date_added DESC LIMIT 10;

RESULT
|name       |category   |date_added     |
-----------------------------------------
|swiss      |bags       |2012-9-23      |
|jansport   |bags       |2012-6-12      |

Now, with your code, see my comments

/**
* This snippet has an issue on this par `ESC LIMIT 10"]", $_GE`
* you should make it like this `ESC LIMIT 10", $_GE`
* if your formatting your string you can check the one below from your code
*/
$sql = mysql_query("SELECT * FROM products WHERE category='%s' ORDER BY date_added DESC LIMIT 10"]", $_GET["selectcat"]);

// You can format it like this.
// you can even put the sprintf inside the mysql_query
// if that's your style
$formattedQuery = sprintf("
    SELECT * 
    FROM products 
    WHERE 
        category='%s' 
        ORDER BY 
            date_added DESC 
        LIMIT 10
    ", $_GET["selectcat"]);

$sql = mysql_query($formattedQuery);

[cheelo007]

lol that wasn't in my code, I swear. I must have copied and pasted in a hurry.

I'm going to try your method now. Also, do you think I should just make a "landing page" for my products page since I will not have an active query with this code upon loading the page? I was thinking that's probably the best option because I don't know how to have a default query which I can then have updated when the user selects a new category.

Thank you for you help!

[gon1387]

You can just make the page dynamic, in the sense, it can cater to different product category while using the same script. Atleast, it can be reused.