Sorting Alphabetically etc etc

[widget]

My users need to be able to sort and search through a list of items in whats called their safety box. (screen shot below)

 

 

Each screen is limited to 50 items, they then need to click next 50 previous 50 etc.

For users with 100's of items it really hard to find the one they are looking for amongst pages and pages of items.

 

I would like to be able to have a menu a - z thats clickable to only show items begining with that letter and also a list of item types such as food clothing etc and be able to sort via that option. Heck even a search box would be amazing!! 

 

Heres my problem.

 

The code as it stands now

 

	$find_items2 = mysql_query("SELECT * FROM safety_box WHERE owner = '$userid' AND game = '$game' ORDER BY item_id LIMIT $start,50");


while ($find_items = mysql_fetch_array($find_items2))
{

$find_item2 = mysql_fetch_array(mysql_query("SELECT * FROM items2 WHERE id ='$find_items[item_id]' AND game = '$game'"));
$id = $find_items[id];

print "<TR BGCOLOR=\"$maincellColor\">
      <TD WIDTH=85>
         <CENTER><IMG SRC=\"images/user_images/opg_$game/items/item_$find_item2[id].gif\"></CENTER>
      </TD>
      <TD WIDTH=90>
         <CENTER><FONT SIZE=\"-1\"><B>$find_item2[item_name]</B></FONT><BR>
         </CENTER>
      </TD>
      <TD WIDTH=45>
         <CENTER><FONT SIZE=\"-1\">$find_items[stock]</FONT></CENTER>
      </TD>
      <TD WIDTH=55>
         <CENTER><FONT SIZE=\"-1\">$find_item2[item_value]</FONT></CENTER>
      </TD>
      <TD>
         <P><FONT SIZE=\"-1\">$find_item2[item_desc]</FONT></P>

 

The problem is, it originally gets the data from one table and converts the data based on item id from another table so I as a newbie have no idea where or how I should be doing the sorting. Anything I have tried has failed miserably.

 

So I ask you experts, please point me in the right direction to find a solution. I've googled but not found anything suitable.

 

The safety box table consists of

 

  id item_id owner stock game

 

The items table consist of

id item_name item_type item_value item_rarity item_image item_desc retired magic_num parts game

 

 

Thank you for your time its much appreciated

[psychowolvesbane]

Well for ordering in sql the word is ORDER BY itemname ASC, for Ascending or DESC for descendng. So you normally place this at the very end of the script for that table (in case you have multiple table queries).

 

So for you I would recommend placing that in the find_items2 so that will change the order of the id's in both tables.

 

For a search box you could have a separate version of find_items2 based on your search:

 

$find_items2 = mysql_query("SELECT * FROM safety_box WHERE owner = '$userid' AND itemnumber='$searchresult' AND game = '$game' ORDER BY item_id LIMIT $start,50");

 

As for a A to Z separator it's far more complicated, but if you had a menu list of letters from A to Z along the top of the page and they link to the page but with a difference. The links should each look like this: http://prod_list.php?searchcat=$letter

 

Then on the page you use the $letter variable to do another similar search as above:

 

$find_items2 = mysql_query("SELECT * FROM safety_box WHERE owner = '$userid' AND item_name LIKE ' "$letter."%' AND game = '$game' ORDER BY item_id LIMIT $start,50");

 

So this will only display those records that begin with that letter, or at least I hope it does as this is a just a quickie script.

 

Hope this helps or at least points you in the right direction.

 

[widget]

Thanks for your reply.

 

Sadly I've tried so many different ways of adding the most simplest of sorting methods and none of them work.

 

I have no idea what I am doing wrong.

 

I tried this code

 

	$find_items2 = mysql_query("SELECT * FROM safety_box WHERE owner = '$userid' AND game = '$game' ORDER BY item_id LIMIT $start,50");


while ($find_items = mysql_fetch_array($find_items2))
{

$find_item2 = mysql_fetch_array(mysql_query("SELECT * FROM items2 WHERE id ='$find_items[item_id]' AND game = '$game' ORDER BY item_name"));

[psychowolvesbane]

What error messages or lack thereof are you getting on the page when you load it?

 

Also, I'm not sure if this helps in any way but it might reduce confusion with the $find_items variable being used with little difference between the names.

$find_items_sql1 = mysql_query("SELECT * FROM safety_box WHERE owner = '$userid' AND game = '$game' ORDER BY item_id LIMIT $start,50");

while ($find_items = mysql_fetch_array($find_items_sql1))
{
   $find_item2 = mysql_fetch_array(mysql_query("SELECT * FROM items2 WHERE id ='$find_items['item_id']' AND game = '$game' ORDER BY item_name"));

   $Record1 = $find_item2['Record1'];
...
}

[widget]

I tried cleaning up the SQL queries so its easier to read and understand.

 

If i put the sort order on the last query it does nothing at all

 

Here is my new current code

 

	


$find_items_sql1 = mysql_query("SELECT * FROM safety_box WHERE owner = '$userid' AND game = '$game' LIMIT $start,50");

while ($find_items_sql2 = mysql_fetch_array($find_items_sql1))
{

$find_items_sql3 = mysql_fetch_array(mysql_query("SELECT * FROM items2 WHERE id ='$find_items_sql2[item_id]' AND game = '$game' ORDER BY item_name DESC"));

$Record0 = $find_items_sql3[id];
$Record1 = $find_items_sql3[item_name];
$Record2 = $find_items_sql2[stock];
$Record3 = $find_items_sql3[item_value];
$Record4 = $find_items_sql3[item_desc];

print "<TR BGCOLOR=\"$maincellColor\">
      <TD WIDTH=85>
         <CENTER><IMG SRC=\"images/user_images/opg_$game/items/item_$Record0.gif\"></CENTER>
      </TD>
      <TD WIDTH=90>
         <CENTER><FONT SIZE=\"-1\"><B>$Record1</B></FONT><BR>
         </CENTER>
      </TD>
      <TD WIDTH=45>
         <CENTER><FONT SIZE=\"-1\">$Record2</FONT></CENTER>
      </TD>
      <TD WIDTH=55>
         <CENTER><FONT SIZE=\"-1\">$Record3</FONT></CENTER>
      </TD>
      <TD>
         <P><FONT SIZE=\"-1\">$Record4</FONT></P>
      </TD>
      <TD WIDTH=100>
         <CENTER><FONT SIZE=\"-1\">Remove:
         <INPUT TYPE=text NAME=\"remove[$id]\" VALUE=\"0\" SIZE=7 MAXLENGTH=6><BR>
         item(s)</FONT></CENTER>
      </TD>
   </TR>";
}

 

 

I'm sure if I can get it to order then I will be able to easily code whats needed to sort alpha etc.

 

By taking off the ORDER BY item_id in $find_items_sql1 the items have changed the order that they appear but in no distinguishable pattern.

 

[psychowolvesbane]

Try this based off the code you gave me:

 

$find_items_sql1 = mysql_query("SELECT * FROM safety_box WHERE owner = '$userid' AND game = '$game' LIMIT $start,50");

while ($find_items_sql2 = mysql_fetch_array($find_items_sql1))
{
    $ItemID = $find_items_sql2['item_id'];
        $find_items_sql3 = mysql_fetch_array(mysql_query("SELECT * FROM items2 WHERE id ='$ItemID' AND game = '$game' ORDER BY item_name DESC"));
        $Record0 = $find_items_sql3['id'];
        $Record1 = $find_items_sql3['item_name'];
        $Record2 = $find_items_sql2['stock'];
        $Record3 = $find_items_sql3['item_value'];
        $Record4 = $find_items_sql3['item_desc'];

    echo "<TR BGCOLOR=\"$maincellColor\">
	      <TD WIDTH=85><CENTER><IMG SRC=\"images/user_images/opg_$game/items/item_$Record0.gif\"></CENTER></TD>
              <TD WIDTH=90><CENTER><FONT SIZE=\"-1\"><B>$Record1</B></FONT><BR></CENTER></TD>
          <TD WIDTH=45><CENTER><FONT SIZE=\"-1\">$Record2</FONT></CENTER></TD>
              <TD WIDTH=55><CENTER><FONT SIZE=\"-1\">$Record3</FONT></CENTER></TD>
              <TD><P><FONT SIZE=\"-1\">$Record4</FONT></P></TD>
              <TD WIDTH=100><CENTER><FONT SIZE=\"-1\">Remove: 
          <INPUT TYPE=text NAME=\"remove[$id]\" VALUE=\"0\" SIZE=7 MAXLENGTH=6><BR>
              item(s)</FONT></CENTER></TD>
		  </TR>";
    }

[widget]

nope, doesnt work.

 

Nothing changed