Question about Sorting Inside While

TheMayhem

I know easily how to do it when you have a query and you want to sort it by a certain field. My question today is I have a query that is getting information from an item table. So it stores the item interger, price, and who put that item up for sell. Pretty simple and easy to follow.

My question is using php during that while command how can I sort everything by the item name in either ASC or DESC mode. The only problem is in the db everything is stored as the item id or number. Therefor I have to use a mysql_fetch_array to get the item name and so forth. How can I sort everything inside the while loop by that item name that I just retrieved from the fetch array. Could someone show me an example?

rebelo

It would help to see code, but if you are doing some kind of query and getting a result with multiple rows and then fetching each row one by one in a while loop, I don't understand how you can possibly do any sorting inside the loop because inside the loop, there's only a single row at a time.

If you want to sort your results by item name, then you just use the sql query to do that, such as:

$query = "SELECT * FROM items ORDER BY item_name ASC";

If your problem is that you need to sort by one field in the query and then another field as well, you can actually sort by two fields in the query like so:

$query = "SELECT * FROM items ORDER BY item_name ASC, username ASC";

It sounds from your post that you already know this, but if so, then what's the problem exactly? Again, specific examples of your code will help.

I'm not too clear on what the problem is but hopefully this is of some help. If not, give me some more details and we can go from there.

TheMayhem

$item_info = mysql_query("SELECT * FROM `sales` where buyerid=0 AND datecompleted=0");
                                while ($item = mysql_fetch_array($item_info)) {

$armor = mysql_fetch_array(mysql_query("SELECT * FROM `armor` where id=$item[moveid]"));

  <tr><td valign='bottom'>$item[date]</td><td valign='bottom'><input type='radio' name='move' value='$item[id]'></input>$armor[name]</td>
 <td valign='bottom'>$armor[damage]%</td><td valign='bottom'>" . number_format($item[price]) . "</td></tr>";

}

Let's say for this loop I want to sort everthing by $armor[name]. Help a bit?

kender

$armor = mysql_fetch_array(mysql_query("SELECT * FROM `armor` where id=$item[moveid] ORDER BY name"));

TheMayhem

When I do that it sorts by the default sales id in ASC sorting. Any other methods?

kender

so you are saying it does not sort by armor name?

or it sorts by armor name, then sales id?

you can change that sort by

$item_info = mysql_query("SELECT * FROM `sales` where buyerid=0 AND datecompleted=0" ORDER BY itemyouwantsortedinsalestable);

TheMayhem

What I'm saying is the only thing related to armor information in the sales table is the armor id. So at very max all I could sort on the sql_query is by the armor id and that'd have no use as its just a number.

If I choose to try to override it on the fetch_array statement it won't, and if it will it will in so doing it screw up the way the information needs to correctly be displayed with who posted and such all being out of whack.

Is there anyway I can run the query and use lets say some arrays like
$sort[0] = "line 1";

And in most cases there are 10 lines except for a few that are less then 10, and then reorder it before they are visually displayed to then sort by the armor name?

leatherback

Hi,

You need to use the join syntax:

SELECT * FROM sales left join ARMOR_DEFINITIONS on sales.armor_id = armor.id where buyerid=0 AND datecompleted=0 order by marmor.name desc

TheMayhem

Wow that really confuses me. Here are my 2 queries that I'm using as the one above won't work as I just used it as an example.

$item_info = mysql_query("SELECT * FROM sales where buyerid=0 AND datecompleted=0 AND type=1 AND ($time-datestarted)/86400 <= 5 ORDER BY id $order LIMIT $membersdisplay,$membersnum");

That is the query I use for the loop. Here is the fetch array query below.

$move = mysql_fetch_array(mysql_query("SELECT * FROM items where id=$item[moveid]"));

Now should I get rid of my fetch array query altogtether and what should the loop query look like?

Btw $order = ASC or DESC, depending on page.
$memberdisplay = an int, usually 0 or whatever the page is.
$membersnum = an int, usually 10 or whatever the page is.

leatherback

Hi,,

Since I cannot test it..

I think this should be it:

SELECT * FROM `sales` left join `items`
  on sales.id = items.moveid where 
  buyerid=0 AND datecompleted=0 AND type=1 AND ($time-datestarted)/86400 <= 5 
  order by items.name  $order  

  limit $membersdisplay,$membersnum

What it does:

You select certain items from the sales table. At the same time, you combine the sales table with the items table (left join) where you need the value in table sales, colum id to be the same as the value in table items, colum moveid.
At that moment, the colums in your item table, are directly availably, aand you can sort by a colum in the items table.