PHP query problem

wint69

I have 3 tables:

Categories: id, icon, name;
Subcategories: id, name, description, category_id;
Topics: id, title, text, author, created, subcategory_id;

ir toki query:

$string_query = "SELECT
categories.id AS catid,
categories.icon AS caticon,
categories.name AS catname,
subcategories.id AS scatid,
subcategories.name AS scatname,
subcategories.description AS scatdescription,
subcategories.category_id AS scatcatid,
COUNT(topics.id) AS tid,
topics.title AS ttitle,
topics.author AS tauthor,
topics.created AS tcreated
FROM
categories
LEFT JOIN
subcategories
ON
subcategories.category_id = categories.id
LEFT JOIN
topics
ON
subcategories.id = topics.subcategory_id
WHERE
subcategories.category_id = categories.id
GROUP BY
categories.id"

$string_result = mysql_query($string_query)or die(mysql_error());

while($string_row = mysql_fetch_array($string_result)){
}

Rezult: Categories table from 5 showing 5 OK,
Subcategories table from 4 showing only 1 and in first category. Whats wrong ? Thanks for any answer.

ixalmida

Your grouping is the problem. Remove the grouping and use something like this (table aliases added for my own convenience):

SELECT
CAT.id AS catid,
CAT.icon AS caticon,
CAT.name AS catname,
SCAT.id AS scatid,
SCAT.name AS scatname,
SCAT.description AS scatdescription,
SCAT.category_id AS scatcatid,
(SELECT COUNT(T.id) FROM topics T WHERE T.subcategory_id = SCAT.id) AS id_count,
TOP.title AS ttitle,
TOP.author AS tauthor,
TOP.created AS tcreated
FROM
categories CAT
LEFT JOIN subcategories SCAT
  ON SCAT.category_id = CAT.id
LEFT JOIN topics TOP
  ON SCAT.id = TOP.subcategory_id

I tested this with some random data and it returns every row. Be aware that it will return NULL values in columns where there is nothing in the "topics" table. But it will also give you an "id_count" of 0, so you can test for that.

I tried to mimic your tables, but you may want to plug this into a test query and see if you get any "unknown columns".

wint69

Now showing all subcategories, but multiply same category times as subcategories in where.

NOW:

Category1
subcategories1
Category1
subcategories2
Category1
subcategories3
Category2
--
Category3
--

Needed:

Category1
subcategories1
subcategories2
subcategories3
Category2
--
Category3
--

ixalmida

Right. You'll have to break this into separate queries if you want to just pull one record for each category or subcategory. Otherwise, you will pull record one for each topic, as well as one for each category/subcategory. It depends on what you are trying to accomplish.

One way of doing things is to add an "ORDER BY catid, subcatid", then use PHP to detect the changing categories and output them once.

$prev_catid = $prev_subcatid = "";
while($row = mysql_fetch_assoc($result))
{
  // Output new category (just once)...
  if($row['catid'] != $prev_catid)
    echo $row['category_name']." -- ";
  // Output new subcategory (just once)...
  if($row['subcatid'] != $prev_subcatid)
    echo $row['subcategory_name']." -- ";
  // Output topics (all)...
  echo $row['topic']."<br />";
  // At the end, set current categories to previous categories...
  $prev_catid = $row['catid'];
  $prev_subcatid = $row['subcatid'];
}

It's a trade-off here between web server and database. You get just one database query, which is nice, but you also get more data to process than you actually need. If you break the queries up, you get more database queries to run, but less data to process.