[RESOLVED] Multiple query to sort

asa_carter

Hi,

I have a table called 'categories' with the following fields:

id
title
parent_id

I have a script to return the total number of items in a given category and all of it's sub-categories:

function get_number_in_categories ($category_id_to_search)
{
  // returns the number of items in the current category
  $result = mysql_query("SELECT * FROM item WHERE auction_end > now() AND location_id='$category_id_to_search'");
  $total = mysql_num_rows($result);
  $result = mysql_query("SELECT * FROM category WHERE parent_id='$category_id_to_search'");
  while ($row = mysql_fetch_array($result))
  $total += get_number_in_categories ($row['id']);
  return $total;
}

I now need to list all the items in a given category and all of it's sub-categories, then sort them in various orders.

I'm not sure how to store these in an array and then sort them. Any idea's?

Regards

Asa

Roger_Ramjet

You should be doing this with 1 query anyway, and when you do it will return the results already sorted.


  $result = mysql_query("SELECT * FROM item c LEFT JOIN item s ON (c.id=s.parent_id)
     WHERE auction_end > now() AND location_id='$category_id_to_search'
     ORDER BY c.title, s.title");
  $total = mysql_num_rows($result);

The same query can be sorted by other criteria as required.

edit: I changed it to a LEFT JOIN in case a category has no sub-categories, with a left join it will still be returned and the subcategory columns will be null.

asa_carter

Roger Ramjet wrote:
  $result = mysql_query("SELECT * FROM item c LEFT JOIN item s ON (c.id=s.parent_id)
     WHERE auction_end > now() AND location_id='$category_id_to_search'
     ORDER BY c.title, s.title");
  $total = mysql_num_rows($result);
Where does the category table fit into this? There are two tables. Item that holds data on each item and cateegories for the category data.

Should it be:

$result = mysql_query("SELECT * FROM item c LEFT JOIN categories s ON (c.id=s.parent_id)
WHERE auction_end > now() AND location_id='$category_id_to_search'
ORDER BY c.title, s.title");

Roger_Ramjet

Well now, that was just me copy/pasting your query and not reading the post in detail, sorry. No time to correct it now - later this evening I will though.

asa_carter

Thanks. Any help you can give would be great. I'm sure some recursion will be needed to check every child in the category table.

Roger_Ramjet

Recursion eh! I'll bet you're storing hierarchical data here, not just a one-level parent-child relationship.

If it is just one level then the way you are storing the data is ok and the solution is straightforward: you join the table to itself to get the children of any parent, as in my query.

Now, your problem was : "I now need to list all the items in a given category and all of it's sub-categories, then sort them in various orders."

Not clear, but it sounds like items do not have to be in a sub-category, they can just be in the main category itself. Shame, it makes things messy.

The best way to clean that up is to store it's own id in the parent_id field of each parent - make it it's own parent. Then when you join the table to itself, the parent category shows up on both sides of the join along with all the children.

To get an orderly list of cat ids to join to the items table can then do this

$sql = "SELECT s.id, s.title FROM category c INNER JOIN category s
     ON c.id=s.parent_id
     WHERE c.id = $category_id_to_search";

That will give you the parent category listed once with all the children. You then extend it to the items table thus

$sql = "SELECT s.title,i.* FROM 
     category c INNER JOIN category s ON c.id=s.parent_id
     INNER JOIN items i ON s.id = i.location_id
     WHERE c.id = $category_id_to_search";

Unless I've completely missunderstood you, that should return what you want, every item in a cat or it's sub-cats.

Now, if it hierarchical data you are dealing with then just READ THIS

asa_carter

Hi,

Yep it is a hierarchical structure. Sorry if I didn't make it very clear.

It goes four levels deep but may be expanded in the future.

An example of the category structure:

id title parent_id
01000000 Antiques & Art 00000000
01010000 Antiques 01000000
01020000 Art 01000000
01020100 Modern Art 01020000

What i wish to do is, for example, if a user looks at items two levels deep (E.g. Art), display all the items in the art category and all of its sub categories (e.g. Modern Art). Then sort them in different ways such as by price, quantity etc. This data is stored in the item table. The item table also has field called location_id which represents its id in the category table.

Hope this makes it a little clearer. I will take a look at the tutorial in the link you provided

asa_carter

Hi,

I've read the tutorial and the modified preorder tree traversal algorithm seems far too complex.

I think I will stick with the adjacency list model but it doesn't mention sorting the data in any order in the tutorial.

After thinking about it, I think I will need to do something like this:

<?
include("connect.php");

function get_all_listings ($category_id_to_search)
{
  $result = mysql_query("SELECT id FROM item WHERE auction_end > now() AND location_id='$category_id_to_search'");
  // add item id into an array
  $result = mysql_query("SELECT id FROM category WHERE parent_id='$category_id_to_search'");
  while ($row = mysql_fetch_array($result))
    get_all_lisitngs ($row[0]);
}

$category_id="01000000";
get_all_listings ($category_id);

// Read all item id's, query and sort them
// Display sorted list
?>

Where I will create an array of item id's to be displayed. Then I will need to read from the array and sort the data in the item table before displaying it.

I know this isn't the fastest way of doing it. I haven't had much experience with using arrays in a database. What do I need to add in the commented area's to complete the script?

Thanks

asa_carter

Hi,

This is what I have so far but i'm not sure how (or if it's possible) to make a query from data stored in an array.

Example:
SELECT * FROM item WHERE location_id = ' { Array Values Here } '

<? 
include("connect.php"); 

function get_all_listings ($category_id_to_search, $i) 
{ 
global $res; 

  $item_row=mysql_fetch_row(mysql_query("SELECT id FROM item WHERE auction_end > now() AND location_id='$category_id_to_search'")); 
  // add item id into an array 
  if ($item_row) 
    $res[$i]=$item_row[0]; 
  $result2 = mysql_query("SELECT id FROM category WHERE parent_id='$category_id_to_search'"); 
  while ($row = mysql_fetch_array($result2)) 
  { 
    $i=$i+1; 
    get_all_listings ($row[0], $i); 
  } 
} 

$category_id="02000000"; 
get_all_listings ($category_id, 1); 

echo "<br>Stored Data<br>"; 
while (list($i, $c) = each($res)) 
  echo "<br> $i - $c"; 

// Read all item id's, query and sort them 
// Display sorted list 
?>

Thanks

asa_carter

The solution:

<?
include("connect.php");

function get_all_listings ($category_id_to_search)
{
  global $res;

  $item_row=mysql_fetch_row(mysql_query("SELECT id FROM item WHERE auction_end > now() AND location_id='$category_id_to_search'"));
  if ($item_row)
    $res[]=$item_row[0];
  $result2 = mysql_query("SELECT id FROM category WHERE parent_id='$category_id_to_search'");
  while ($row = mysql_fetch_array($result2))
    get_all_listings ($row[0]);
}

$category_id="02000000";
get_all_listings ($category_id);

$res= implode(",",$res); 

$result = mysql_query("SELECT * FROM `item` WHERE `id` IN ($res) ORDER BY opening_price DESC");
while ($row = mysql_fetch_row($result))
  echo "$row[1]<br>";
?>