Help with queries.

[Skepsis]

Hello, it seems i'm having problems with a mysql query that has been making me go crazy.

I'm trying to find the users viewing each topic and the query is not returning the session data.

 

This is the query i'm using:

<?php
                $topics = $db->getTable("
                                        SELECT 
                                            t.tid, t.title, t.forum, 
                                            s.sid, s.referrer, s.user_id, 
                                            u.id AS uid, 
                                            u.username, u.gid 
                                        FROM lnx_topics AS t 
                                            LEFT JOIN lnx_session_data AS s
                                                ON t.tid = (SUBSTRING_INDEX((SUBSTRING_INDEX(s.referrer, 'tid=', -1)), '&', 1)) 
                                                AND (SUBSTRING_INDEX(s.referrer, '&', 1)) = 'act=topic' 
                                                    LEFT JOIN lnx_users AS u ON u.id = s.user_id WHERE t.tid = '{$db->escape($_GET['tid'])}'");
  ?>

 

This query returns these results,

tid = 1

title = title name

forum = 2

sid,referrer,user_id,username,uid,gid = null.

 

What's really strange is i have a query for users browsing the forum that works...

<?php
                $parents = $db->getTable("
                                        SELECT 
                                            f.fid, f.name, f.parent, 
                                            s.sid, s.referrer, s.user_id, 
                                            u.id AS uid, 
                                            u.username, u.gid 
                                        FROM lnx_forums AS f 
                                            LEFT JOIN lnx_session_data AS s
                                                ON f.fid = (SUBSTRING_INDEX((SUBSTRING_INDEX(s.referrer, 'fid=', -1)), '&', 1)) 
                                                AND (SUBSTRING_INDEX(s.referrer, '&', 1)) = 'act=forum' 
                                                    LEFT JOIN lnx_users AS u ON u.id = s.user_id WHERE f.fid = '{$db->escape($_GET['fid'])}'");?>

 

This returns

fid	name	parent	sid	referrer	user_id	uid	username	gid
2	News	1	802830170fd819de302a321b314c016c	act=forum&fid=2	1	1	Matt	1

The only difference between the referrer is that the forums are:

act=forum&fid=$id

 

the topics are

act=topic&tid=$id

 

The queries have been changed accordingly but the topics returns null information, any ideas?  I hope my post was detailed enough, thanks in advance.

 

[Psycho]

Well, obviously there are no records to JOIN from the session table to the topics table. That seem slike a really inefficient way to do a JOIN. BUt, using what you have you would need to do a little debugging to see why yhe join is not occuring.

 

1) First echo the query to the page to ensure the $_GET['tid'] value is what you expect

2) Do a  query of records in the session table to see if any have tid=[TID_VALUE] in the referrer column AND 'act=topic' after the first ampersand.