better grasp on sending and recieving data with stored procedures

[blackhawk]

Hello,

I'm currently in the process of advancing my skills with stored prodcures in mysql. I would consider myself a well trained novice.  I currently have a smarty template site that is running with php in the background, that then connects to a mysql database. 

 

 

within my index.tpl page i have...

 

{html_options name="ProductDisplay" options=$obj->mProductDisplayOptions selected=$obj->mProduct.display}
<input type="submit" name="SetProductDisplayOption" value="Set" />       

 

within my php class plug-in page i have...

 

    if (isset ($_POST['SetProductDisplayOption']))
    {
      $product_display = $_POST['ProductDisplay'];
      Catalog::SetProductDisplayOption($this->_mProductId, $product_display);
    }

 

within my php Catalog page i have php code that reads the procedures from my mysql database.

 

within my mysql procedure, i call a list of all the products listed in my databse.  But I also want to build a CASE WHEN statement of 3 values to ORBER BY....  I want to order my list of items by name, price, or description.  I know how to build that actual statement itself, I'm just not sure how to tell my index.tpl page to sort the list of products by the CASE I  have set in my prodecure.  Does that make sense?

 

 

Here is the code the retrieves a list of my products...

 

BEGIN
  PREPARE statement FROM
    "SELECT DISTINCT p.product_id, p.name,
                     IF(LENGTH(p.description) <= ?,
                        p.description,
                        CONCAT(LEFT(p.description, ?),
                               '...')) AS description,
                     p.price, p.discounted_price, p.thumbnail
     FROM            product p

 

thanks for the help!

-bh

 

 

 

 

[blackhawk]

So I guess what I'm really saying "for the sake of less confusion" is, how do I return my list of items back to my index.tpl page as a certain "order", all based on my html_options selection?

 

thanks!

 

[blackhawk]

...

[fenway]

I don't understand how you want them ordered... please clarify.

[blackhawk]

my apologies for not clarifying that.  I would like to sort my list by either

name or description or price, depending on what i select from my html_options dropdown.

 

I have a procedeer that gets all full list of items

BEGIN
  PREPARE statement FROM
    "SELECT DISTINCT p.product_id, p.name,
                     IF(LENGTH(p.description) <= ?,
                        p.description,
                        CONCAT(LEFT(p.description, ?),
                               '...')) AS description,
                     p.price, p.discounted_price, p.thumbnail
     FROM            product p
ORDER BY p.description;

 

but now i want to take adjust that code so its based on a conditional statement coming from my html_options drop down.

 

I guess I could create 3 different proceeders, each of them having a different ORDER by p...., but that just seems stupid to me.  any help would be appriciated!

 

 

[fenway]

You can use CASE in your ORDER BY clause.