help with a join query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

help with a join query

Post by s.dot »

I have this query

Code: Select all

$forum_post_result = mysql_query("
	SELECT
		count(*) AS `forumnum`,
		`author`
	FROM
		`forumentries`
	GROUP BY
		`author`
	ORDER BY
		`forumnum`
	DESC LIMIT 20
") or die(mysql_error());
Basically i need to check the `username` in the `users` table to make sure that username still exists. I believe I need a join here, eh?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Code: Select all

SELECT
        count(`forumentries`.*) AS `forumnum`,
        `forumentries`.`author`,
        `users`.`username` as `exists`
    FROM
        `forumentries`
LEFT JOIN `users` on `users`.`username` = `forumentries`.`author`
    GROUP BY
        `forumentries`.`author`
    ORDER BY
        `forumentries`.`forumnum`
    DESC LIMIT 20

Something like this? `exists` will be empty (or NULL) if the user does not exist. By the way this thread should be moved to databases. You also might experience better performance running the query inside of your loop (when you're looping over the resultset), it all depends on your exact table setup.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

really?
i thought queries inside of loops were a big no-no

plus i can't run the query inside the loop anyways, because i want to limit it to the top 20 results, if one of the users doesn't exist, then it will return 19
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

It depends, on some databases it could perform a full-table join for each record, example


user1 a
user1 b
user2 a
user2 b

and then go down and refine it, in the case mysql is doing this the loop would be faster. Don't be afraid to use a lot of queries per page view, its the expense of the queries themselves that matters.
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post by duk »

try to see using DISTINCT very very much faster then JOIN or IN

check my last threat in databses forum about distinct

viewtopic.php?t=44117
Post Reply