[SOLVED] PHP/MySql Data Sorting Question

[lindenbridge612]

The PHP file I am currently editing has the MySql data sorted by item number.  There are four fields (categories, items, item_lookup, and images), and under the field "items" is the table "names".  I would like to select all data from these 4 fields and sort them alphabetically by the data in table "name".  More specifically, the page is setup to click on the "Previous" and "Next" item, and I want the previous and next item to appear alphabetically.  I have already tried editing the code and have had trouble displaying the newly sorted results.  Here is the original code, which sorts the items by item number.  Does anyone know how to edit this code to display my results alphabetically?:

 

The Sorting Code (before head of document):

 

$itemID = $id;

$result = @mysql_query("SELECT * FROM items , images WHERE items.id='$id' AND images.id = imageid");

if (!$result) {

exit('<p>Error performing query: ' . mysql_error() . '</p>');

}

$row = mysql_fetch_array($result);

 

if ($cat){

$catResult = @mysql_query("Select * FROM categories, item_lookup WHERE name = '$cat' AND categories.id = catID");

$catRow = mysql_fetch_array($catResult);

} else {

$catResult = @mysql_query("Select * FROM categories, item_lookup WHERE itemID= '$id' AND categories.id = catID");

$catRow = mysql_fetch_array($catResult);

}

$prevResult = @mysql_query("SELECT * FROM item_lookup WHERE itemID < '$id' AND catID = $catRow[catID] order by itemID DESC LIMIT 1");

$prevRow = mysql_fetch_array($prevResult);

$nextResult = @mysql_query("SELECT * FROM item_lookup WHERE itemID > '$id' AND catID = $catRow[catID] order by itemID ASC LIMIT 1");

$nextRow = mysql_fetch_array($nextResult);

 

The Display of the "Previous" and "Next" Links:

 

if ($prevRow['itemID']){ echo"<a href=\"{$_SERVER['PHP_SELF']}?id={$prevRow['itemID']}&cat=&cat\">« Previous Product</a>";}

if ($prevRow['itemID'] AND $nextRow['itemID']){ echo" | "; }

if ($nextRow['itemID']){ echo"<a href=\"{$_SERVER['PHP_SELF']}?id={$nextRow['itemID']}&cat=&cat\">Next Product »</a>";}

?>

 

 

 

[fenway]

Why not just the mysql order by clause?

[lindenbridge612]

I tried using "ORDER BY name", and while the order became now alphabetical, many items were skipped.  Basically, it would start from items beginning with "J" and jump to "Z".  I did not write the original code, so I am having trouble rewriting it to be ordered by item name instead of item code.  Any idea on how to include all items (A-Z) and not skip any?

[fenway]

By "skip", you mean there were no entries for them in your DB?

[lindenbridge612]

No, there are entries for them, but the database currently has ID numbers in two tables joined to each other.  The items in each of these tables are set as equal in the PHP code (via their ID number):

 

$itemID = $id;

$result = @mysql_query("SELECT * FROM items , images WHERE items.id='$id' AND images.id = imageid");

 

so when I try to claim that the items are joined by name and not number, the results skip around the data and instead of posting the first alphabetical item that begins with A, it posts the first alphabetical item that does not have an ID number and skips to the next alphabetical item without an ID.  There are only 2 items without an ID, so it only shows them.

 

Hmmm. . . I feel like I may have touched closer to the problem.

[lindenbridge612]

Ok, I am understanding the database better and realized that I made some mistakes in my original description.

 

First, I am using PHPMyAdmin 2.9.0.

 

The database is divided into four tables: categories, images, items, and item_lookup.

 

Table categories has the following fields: id and name

Table images has the following fields: id

Table items has the following fields: id, name, and imageid

Table item_lookup has the following fields: catID and itemID

 

categories.id = item_lookup.catID

item_lookup.itemID = items.id

items.imageid = images.id

 

I would like to display one item from the inventory at a time, with an option for “Previous” and “Next” item at the bottom of the page.  I would like the data sorted alphabetically by categories.name (category name), then items.name (item name), so when the viewer clicks on previous and next, they will see the previous and next item alphabetically.  Hypothetically speaking, if the item was a “Stuffed Teddy Bear” in the category “Stuffed Animals”, I would want the previous item to be “Stuffed Animals > Stuffed Rabbit” and the next item to be “Stuffed Animals > Stuffed Walrus”.  If the 1st item in “Stuffed Animals” is “Stuffed Alligator”, I would want the previous item to be “Ceramic Animals > Walrus”, and if the last item in Stuffed Animals” is “Stuffed Walrus”, I would want the next item to be “Terra Cotta Animals > Alligator”

 

Does that make sense?

 

[fenway]

Sorry, now you've lost me completely.

[lindenbridge612]

I figured out how to do this over the weekend, FINALLY!!! It took a lot of experimentation with the "Order By" syntax to make it work.

[revraz]

Nice, please mark as Solved

[fenway]

Nice, please mark as Solved

And post the working query.

[lindenbridge612]

Here is the solution.  After taking another look at my objective, I realized that I only needed to sort the data by the item name, which also involved addressing the item number in the process (see "The Display of the 'Previous' and 'Next' Links" section below).

 

The Sorting Code (before head of document):

 

<?php
// Connect to the database server
require('../../global.php');
/* Connecting, selecting database */
$link = mysql_connect("$hostname", "$username", "$password");
mysql_select_db("$database");

$itemID = $id;
$result = @mysql_query("SELECT * FROM items , images WHERE items.id='$id' AND images.id = imageid");
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
$row = mysql_fetch_array($result);

$name=$row['name'];

if ($cat){
$catResult = @mysql_query("Select * FROM categories, item_lookup WHERE name = '$cat' AND categories.id = catID");
$catRow = mysql_fetch_array($catResult);
} else {
$catResult = @mysql_query("Select * FROM categories, item_lookup WHERE itemID= '$id' AND categories.id = catID");
$catRow = mysql_fetch_array($catResult);
}
$prevResult = @mysql_query("SELECT * FROM items, item_lookup WHERE name < '$name' AND catID = $catRow[catID] order by name DESC LIMIT 1");
$prevRow = mysql_fetch_array($prevResult);
$nextResult = @mysql_query("SELECT * FROM items, item_lookup WHERE name > '$name' AND catID = $catRow[catID] order by name ASC LIMIT 1");
$nextRow = mysql_fetch_array($nextResult);
?>

 

The Display of the "Previous" and "Next" Links:

 

<?php 
if ($prevRow['name']){ echo"<a href=\"{$_SERVER['PHP_SELF']}?id={$prevRow['id']}&cat=&cat\">« Previous Product</a>";}
if ($prevRow['name'] AND $nextRow['name']){ echo" | "; }
if ($nextRow['name']){ echo"<a href=\"{$_SERVER['PHP_SELF']}?id={$nextRow['id']}&cat=&cat\">Next Product »</a>";}
?>