Merging two queries

[creata.physics]

Hello all,

 

I have a forum script for my website, and the way I count the users viewing each forum is not to my satisfactory.

 

My query pulls all the categories and renders their data inside the foreach() loop, but to count the users viewing each category, I use another query inside the loop to grab the session data which has the users referrer, which is how i determine the users viewing the category.

 

So the loop pulls out about 7 categorys, and runs a query for each category, increase my query count and load times, I'd like to see about counting the users viewing each forum in the main category, but that is what I'm having issues with.

 

I've tried to do it on my own, but the farthest I've gotten is that the script only shows the forums that users are viewing, so I think.  This is because I'm developing on my localhost, and since nobody else is online but me, there is no way I can actually tell.

 

Let me show you the queries I use:

of course the code will be simplified for better understanding

<?php

$categories = $this->zext->db->get_table("SELECT f.fid AS fid, u.id as lastpost_author_id, f.name, f.description, f.parent, f.replies, f.topics, f.postable, f.redirecturl, f.redirect_hits, f.perms, f.is_news, f.lastpost_id, f.lastpost_time, f.lastpost_title, f.lastpost_author_id FROM lnx_forums AS f LEFT JOIN lnx_users AS u ON f.lastpost_author_id=u.id ORDER BY f.sort ASC");

foreach ($categories AS $category)
{
      $viewing = count($this->zext->db->get_table("SELECT f.fid, f.name, f.parent, s.sid, s.referrer, s.user_id, u.id AS uid, u.username, u.group_id FROM lnx_forums AS f LEFT JOIN lnx_sessions AS s ON f.fid = (SUBSTRING_INDEX((SUBSTRING_INDEX(s.referrer, 'fid=', -1)), '&', 1)) AND (SUBSTRING_INDEX(s.referrer, '&', 1)) = 'mod=forum' LEFT JOIN lnx_users AS u ON u.id = s.user_id WHERE f.fid = '{$category['fid']}' AND f.postable = 1"));

}
?>

 

I guess I'm wanting to left join the session table into the main query and use mysql's count function to count the rows of the viewing users?  I'm just stumped, I don't get any sql errors I just return 0 rows everytime.  Any help will be gladly appreciated.

 

Thanks, Matt.

 

I've tried just a basic query like so:

SELECT f.fid AS fid, u.id as lastpost_author_id, f.name, f.description, f.parent, f.replies, f.topics, f.postable, f.redirecturl, f.redirect_hits, f.perms, f.is_news, f.lastpost_id, f.lastpost_time, f.lastpost_title, f.lastpost_author_id, count(s.sid) AS viewing, s.referrer, s.user_id FROM lnx_sessions AS s LEFT JOIN lnx_forums AS f LEFT JOIN lnx_users AS u ON f.lastpost_author_id=u.id ORDER BY f.sort ASC

and I get an sql error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY f.sort ASC LIMIT 0, 30' at line 1

 

 

[Muddy_Funster]

Try it without the sensless and needless aliasing of your three tables and see what happens then.

[creata.physics]

What do you mean exactly? the s. f. and u.? I thought those were needed? 

If that is not what you're talking about the please be more elaborate.

[Muddy_Funster]

yeah, the s,f and u (glad there's not a t in there as well or it could get offensive )  you do not need to alias your table names, just use the table name where you are using the alias.  That said I think there is only one instance whare a column name would be ambiguous and need you to declare the table name prior to the FROM clause.  Trying to alias within a JOIN statement never a good idea, so just write it as a simple

SELECT field, list,.... FROM table1, table2 LEFT JOIN table3 ON table2.field = table3.field ORDER BY etc.