Sql

[klyan]

hi,

am runnin this code to search from 3 tables.
"select distinct z.categories_name, q.categories_id, p.products_id from products as p, products_to_categories as q , categories_description as z where p.products_status = '1' and z.language_id = '1' and p.products_id = q.products_id and q.categories_id = z.categories_id "

i get a list of categories name, categories_id and products_id.

now can i select distinct categories_name here.

should i use arrays to split it and then show.

thanks in advance

[eoghain]

I may have interpreted your question incorrectly but I believe that you want to use the results that you are getting out of the database to build a listing something like this:

Cat1:
   Item1
   Item2
   Item3
Cat2:
   Item1
   Item2
   Item3
   Item4
Cat3:
   Item1

If so what you need to do is order you dataset by the category_name then use this type of looping construct:

$currentCat = '';
for ($i = 0; $i < count($dataSet); $i++) {
   if ($currentCat != $dataSet[$i]['category_name']) {
      $currentCat = $dataSet[$i]['category_name'];
      print $currentCat . ':';
   }
   print '&nbsp&nbsp&nbsp' . $dataSet[$i]['product_id'];
}

Or something like that.

[klyan]

here are the codes.

$listing_sql1="select distinct p.products_id, q.categories_id, z.categories_name from products as p, products_to_categories as q , categories_description as z where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id order by categories_name";

$products_query1 = tep_db_query($listing_sql1);

$currentCat = '';
for ($i = 0; $i < count($products_query1); $i++) {
if ($currentCat != $products_query1[$i]['categories_name']) {
$currentCat = $products_query1[$i]['categories_name'];
print $currentCat . ':';
}
print '&nbsp&nbsp&nbsp' . $products_query1[$i]['products_id'];
}

anything wrong here in the codes.

i get 40 records with duplicate categories names picked up from a sql query in 3 tables.

how do i get dintinct category names.

thanks in advance

[eoghain]

Next time you post code use the PHP tags so your code is color coded and formated nicely.

Well from the code that you are showing it doesn't look like you are getting the results, just the result set id that is returned by tep_db_query(), by the way what is tep_db_query() it's not in the PHP manual. If it's your own function does it return an array that is formatted the way you are expecting it? From the looks of it you are expecting a multi-diminsional array where the first dimension is int indexed and the second dimension is associative.

Now the code I originally posted is expecting that you will get back a dataset that looks something like this:

Category_Name | Item_Name
Cat1 | Item1
Cat1 | Item2
Cat1 | Item3
Cat2 | Item1
Cat2 | Item2
Cat2 | Item3
Cat2 | Item4
Cat3 | Item1

This dataset has all of my items and displays the category name that each item is in. So I will have duplicate category names, but I only print out 1.

There is no way for you to get all items out of your database while only getting 1 category name using a single query.