long query problems...

[Sim]

My query is returning 0 for all COUNT's and NULL for rest any ideas.

 

SELECT COUNT( cheat.cheatID )AS cheats, COUNT( review.reviewID )AS reviews, COUNT( image.screenID )AS images, COUNT( news.newsID )AS newz, COUNT( video.videoID )AS videos, game.gameID, game.gameName, game.gameFType
FROM games AS game, game_cheats AS cheat, game_reviews AS review, game_screens AS image, game_news AS news, game_videos AS video
WHERE cheat.cheatGameID = game.gameID
AND review.reviewGameID = game.gameID
AND image.screenGameID = game.gameID
AND news.newsGameID = game.gameID
AND video.videoGameID = game.gameID
ORDERBY game.gameName DESC 
LIMIT 0 , 50

[Spudinski]

That's a "long" query?

Do a EXPLAIN.

I.e.

EXPLAIN your sql

[a_bertrand]

You are doing here what's called (if I remember right) a "full join", which would returns results ONLY if all the conditions are true, which means if there is a row in each table.

You may want to change the full join with a left join:

Full join

select users.username, user_logs.action from users, user_logs where users.id = user_logs.user_id;

 

The left join

select users.username, user_logs.action from users left join user_logs on users.id = user_logs.id;

 

The left join will take all the rows of the first table and try to join them with rows of the second table. If there is no match all the columns of the second table will be set to NULL. So basically you are guaranteed to have the number of rows of the first table.

Not sure if it's what you want, but you could investigate.

Another option is to make unions of different queries

 

select count(id) as nb_users, 0 as nb_logs from users 
union
select 0 as nb_users, count(id) as nb_logs from user_logs;

 

that would run the first query and attach the result of the second query. To make it run well the names of the columns should match.

[HauntedDawg]

Try

SELECT 
COUNT(`cheat`.`cheatID`) AS `cheats_count`, 
COUNT(`review`.`reviewID`) AS `reviews_count`, 
COUNT(`image`.`screenID`) AS `images_count`, 
COUNT(`news`.`newsID`) AS `newz_count`, 
COUNT(`video.videoID`) AS `videos_count`,
`game`.`gameID`, `game`.`gameName`, `game`.`gameFType`
FROM 
`games` `game` LEFT JOIN `game_cheats` `cheat` ON `game`.`cheatGameID` = `cheat`.`cheatID`
LEFT JOIN `game_reviews` `review` ON `review`.`reviewGameID` = `game`.`gameID`
LEFT JOIN `game_screens` `image` ON `image`.`screenGameID` = `game`.`gameID`
LEFT JOIN `game_news` `news` ON `news`.`newsGameID` = `game`.`gameID`
LEFT JOIN `game_videos` `video` ON `video`.`videoGameID` = `game`.`gameID`
ORDER BY `game`.`gameName` DESC LIMIT 0,50

 

Untested

PS: Your count's are no longer just the plain one, so just add _count to it. EG:

$query['cheats'] => $query['cheats_count']