Jump to content
MakeWebGames

long query problems...


Recommended Posts

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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']

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...