[SOLVED] Is there an easy way

[phpbeginner]

Without getting into 4 different results or queries, is there an easy way to sort by status the way I want. For example I would like to order them by status as follows (Featured, Regular, Coming Soon and Sold).

 

 

$query = "SELECT * FROM tblproduct where type='Truck' ORDER BY 'status'";
$result = mysql_query("SELECT * FROM tblproduct where type='Truck' ORDER BY `status` LIMIT $limitvalue, $limit;");

 

Thanks So Much and Happy New Year!!

[revraz]

You still have to perform seperate querries, but you can do it with just 1 statement over and over if you use a variable for what you want to ORDER BY.

 

Or you can load everything into an array.

[amites]

as Revraz said try using a variable for "order by"

 

something like

 

$sortby = $_POST['sort']

$query = "SELECT * FROM tblproduct where type='Truck' ORDER BY ".$sortby;

[phpbeginner]

Thanks for your replies, I have never got this far into this. So I would simply use....

 

$sortby = $_POST['Featured','Regular','Coming Soon','Sold']

$query = "SELECT * FROM tblproduct where type='Truck' ORDER BY ".$sortby;

 

Again, Thanks So Much and Happy New Year to All !

[phpbeginner]

Ouch, what a mess I got now.....I guess I am way off on my assumption above.

[amites]

almost

 

inside the post is the name of the variable you want to use so

 

$_POST['sortby']

 

where your html would look like

 

<form id="form1" name="form1" method="post" action="scriptname.php">
  <select name="sortby" id="select">
    <option value="Featured" selected="selected">Featured</option>
    <option value="Regular">Regular</option>
    <option value="Coming Soon">Coming Soon</option>
    <option value="Sold">Sold</option>
  </select>
</form>

 

understand?

[phpbeginner]

Ahh, I already have the form inserting this into the database. I am trying to display it in the order above.

[phpbeginner]

bump.....anyone help me with this?

 

Appreciate it !

[ccrevcypsys]

no you dont have this form insert anything into a database you have it run with a get property so that it sends the data to the next page and if

$sortby = $_GET['sortby'];  or you can post it i guess ( i just like to use gets... )

and the query should look like this

 

$query = "SELECT * FROM tblproduct where type='Truck' ORDER BY ".$sortby;

 

it will order them by whatever was selected on the form... does this make sence?

[phpbeginner]

$sortby = $_POST['Featured','Regular','Coming Soon','Sold']

$query = "SELECT * FROM tblproduct where type='Truck' ORDER BY ".$sortby;

 

Yeah, this is what I have been trying but no go.....

[ccrevcypsys]

dude u dont get it u are doing it wrong here do it like this and tell me if it works or not

 

put this on the page that querys the info

(at the top or w/e it will act like a filter)

<form id="form1" name="form1" method="get" action="">
  <select name="sortby" id="select">
    <option value="Featured" selected="selected">Featured</option>
    <option value="Regular">Regular</option>
    <option value="Coming Soon">Coming Soon</option>
    <option value="Sold">Sold</option>
  </select>
<input name="submit" value="Sort" >
</form>

Then we do the php code that lets this work when you hit submit

<?php
$sortby = "ORDER BY ".$_GET['sortby'];
// now if you look at the address bar you will see that it says sortby w/e was selected.
//now for the query
$query = "SELECT * FROM tblproduct where type='Truck' ".$sortby;
//get rid of the ORDER BY thats there and put it into the $sortby. So that when you load this page up without having a filter active it will work right.
?>

[phpbeginner]

Thanks, but not working for me.....and your absolutely right, I do not get it. I have no idea why the form. I don't want the end user to query results by Featured, Coming Soon, Regular or Sold. I am just trying to get the results to query in that order (All Featured 1st, All Coming Soon 2nd, All Regular 3rd, All Sold 4th).

 

I apologize for my ignorance on this.

[GingerRobot]

I dont think you were being ignorant. The question was pretty clear. Unfortunately i can't bring you an easy answer.

 

This is why you need to plan your database. You're gonna have to alter the way you store the status of each product. You need to have a number represent the status, which can then by ordered in the normal way. e.g:

 

1 = Featured

2 = Coming Soon

3 = Regular

4 = Sold

 

You could store the names of each status with their relevant ID in a separate table and join the results if you also want to pull the name.

 

If its really not an option, then AFAIK, your only option is 4 separate queries. You could make one use of the mysql_query() function and UNION the results, but this is still 4 separate mysql queries and hence pretty inefficient.

 

Edit: If there's a limit clause to deal with, then it becomes more complicated. You'd have to perform the separate queries and stick the results in an array, then output only the required results. I'd definitely recommend changing the status to an integer.

[phpbeginner]

I appreciate that and had thought about my options and changing to a number as unfortunately I have a limit clause also so that's why I was looking for some help in here as I knew it would be a bit more complicated.

 

I thought I had planned the database to the fullest as it was simply going to be order by price....however a change in plans on the client end.

 

I appreciate all your help and insight GingerRobot. All the best in 2008!

[GingerRobot]

No problem, and you to.

 

If you're done here, can you mark this as solved?