TalkPHP - More rows from join clauses

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - More rows from join clauses (http://www.talkphp.com/mysql-databases/3192-more-rows-join-clauses.html)

Orc	07-30-2008 08:06 AM

More rows from join clauses

How can I get all the rows from a join clause? instead of 1?

Aaron

07-30-2008 11:39 AM

PHP Code:


		
			
$query = "SELECT user.name, message.to".

"FROM user, message";

// I wonder if this would work... LIMIT count(*);

Aaron

07-30-2008 11:40 AM

PHP Code:


		
			
$query = "SELECT user.name, message.to".

"FROM user, message";

// I wonder if this would work... LIMIT count(*);

Try that. I really don't use joins that much. The more queries the better.

CoryMathews

07-30-2008 12:35 PM

By default you should always get more then 1 if there is more then one. As long as you dont have a limit 1 on the end of the query. and aaron isnt that method really slow? (no backing on that statement)

Aaron

07-30-2008 05:07 PM

Count is extremely slow. That was just something I was wondering about (it had no point whatsoever)

The only thing about my query was not using WHERE.

Orc	07-30-2008 07:31 PM

Quote:

Originally Posted by CoryMathews (Post 17470)

tried it, although I have group by in there for something else.

Aaron

07-30-2008 07:33 PM

Then our SQL knowledge fails.

CoryMathews

07-30-2008 11:50 PM

Can you post the Query? or there isnt much else we can do.

Orc	07-31-2008 10:28 AM

sql Code:

SELECT
            t.tid,
            t.title AS thread_title,
            t.created,
            t.mid,
            t.fid,
            f.title AS forum_title,
            f.DESC  AS forum_desc,
            f.created AS forum_timestamp,
            fc.title AS fc_title,
            m.username AS member_username,
            m.mid AS member_uid,
            p.created AS post_time,
            p.msg AS post_msg,
            m.signature AS member_signature,
            p.pid,
            r.rid,
            COUNT(DISTINCT r.rid) AS rating_count
            FROM `threads` AS t
            
            LEFT JOIN `posts` AS p ON p.tid=t.tid
            LEFT JOIN `forums` AS f ON f.fid=t.fid
            LEFT JOIN `forum_categories` AS fc ON f.catid=fc.fcid
            LEFT JOIN `members` AS m ON m.mid=p.mid
            LEFT JOIN `member_ratings` AS r ON r.pid=p.pid
            
            
        
            
            
            WHERE t.tid = '".$tid."'
            AND   f.fid = '".$fid."'
            
            GROUP BY p.pid
            ORDER BY p.pid

I only get 1 row from member_ratings

CoryMathews

07-31-2008 12:59 PM

wow thats a hell of a query. Are you sure there even is more then 1 row that should be returned? you have quite a bit of restrictions on that. Also it might be better to break that into more then 1 query, I bet that runs hella slow im gonna guess at least 10 seconds, if the tables have some sort of data in them.

Orc	07-31-2008 01:03 PM

Tried multiple queries, didn't work, and I didn't like the idea in the first place.

By the way, it takes me 1 second. with a cache at least..

With cleared cache, it took 2 seconds. :P

Orc	07-31-2008 01:09 PM

Should I reconstruct my code to work with a multiple query? Cause that's what it is, or at least I believe it 100% :P

Orc	07-31-2008 01:38 PM

Nevermind, I DID reconstruct the queries and code, and I now have it where theres two queries for the post data and then for the ratings for the post data, inner while loops :p

Aaron

07-31-2008 02:05 PM

So is this solved?

Also, is it better to force the browser to cache your page, or cache it on the server, or both? O.o

Orc	08-02-2008 06:58 AM

Quote:

Originally Posted by Aaron (Post 17493)

So is this solved?

Also, is it better to force the browser to cache your page, or cache it on the server, or both? O.o

cache on server I believe.

All times are GMT. The time now is 05:55 PM.