shujjah Posted January 16, 2009 Share Posted January 16, 2009 Alright I am working on a top games script for my website. Basically what I wanted was to display all the games with the top scores in their reviews on the homepage. But I am stuckFirst I select the top 5 ratings and the article Id off a table ( limit 5 ) .. then I use that article Id to find the game Id's off another table.. now the problem is when in the second select statement I insert Select * From xxxx where articleId = '$articleId' AND deleted='0' .................. But the problem is when I fetch array and echo the results only one gameId is displayed .. whereas $articleId contains 5 values so it means that in the second select statement ( the one posted above ) only 1 value of $articleId is used ? So how can I use all those 5 values and select the results for each on of those values...Hope I made sense?Best Regards, Link to comment Share on other sites More sharing options...
Synook Posted January 17, 2009 Share Posted January 17, 2009 Why don't you just: SELECT table.articleId, xxxx.* FROM table, xxxx WHERE table.articleId = xxxx.articleId GROUP BY table.articleId ORDER BY table.score DESC LIMIT 5 Link to comment Share on other sites More sharing options...
shujjah Posted January 19, 2009 Author Share Posted January 19, 2009 Why don't you just:SELECT table.articleId, xxxx.* FROM table, xxxx WHERE table.articleId = xxxx.articleId GROUP BY table.articleId ORDER BY table.score DESC LIMIT 5 Ok I am bit confused .. I dont get the code you posted ... this is my code $game = mysql_query("SELECT * FROM gamecms_articleWHERE articleId='$articleId' AND deleted='0'"); can you give me the code that will select all the 5 values please ( or tell me how to make one ) ... Link to comment Share on other sites More sharing options...
jlhaslip Posted January 19, 2009 Share Posted January 19, 2009 Looks to me like the Query posted above your last posting should be the one to do that. Link to comment Share on other sites More sharing options...
shujjah Posted January 20, 2009 Author Share Posted January 20, 2009 As I said I dont get it i have to substitute this table.articleId = [b]xxxx.articleId[/b] by the variable $articleId? Link to comment Share on other sites More sharing options...
justsomeguy Posted January 20, 2009 Share Posted January 20, 2009 First off, $articleId is not 5 values, it's 1 value. A variable doesn't have more than one value. If it is a comma-separated list of numbers, then you can do this:$game = mysql_query("SELECT * FROM gamecms_article WHERE articleId IN ($articleId) AND deleted='0'");If it's an array of numbers, then you can implode the array to get a comma-separated list. If it's neither a comma-separated list, nor an array, then tell us what it is! Link to comment Share on other sites More sharing options...
Synook Posted January 21, 2009 Share Posted January 21, 2009 What I meant is at the moment you are performing one query, then using the result to perform another query. However, you could just perform a single query to get all the information you need (as per the query I suggested). Link to comment Share on other sites More sharing options...
shujjah Posted January 21, 2009 Author Share Posted January 21, 2009 Alright this is my previous query $result = mysql_query("SELECT * FROM gamecms_articleratingsWHERE ratingId='5'AND deleted='0' ORDER BY rating DESC, rating LIMIT 5");while($row = mysql_fetch_array($result)) {$articleId = $row['articleId']; $rating = $row['rating']; echo $rating."".$articleId;echo '<br/>'; } When I echo the results it displays all 5 values ... so $articleId is ? Link to comment Share on other sites More sharing options...
justsomeguy Posted January 21, 2009 Share Posted January 21, 2009 After the while loop finishes, $articleId is the ID from the last record. It's not all 5 IDs, it's just the last one. If you put your other query inside the while loop, then you could run the query for each ID. But it's never a good idea to put database queries inside loops, it's always better if you can get everything with 1 query like Synook is talking about. Link to comment Share on other sites More sharing options...
Synook Posted January 22, 2009 Share Posted January 22, 2009 http://en.wikipedia.org/wiki/Trace_tableUseful debugging tool. Link to comment Share on other sites More sharing options...
justsomeguy Posted January 22, 2009 Share Posted January 22, 2009 You could also build a list of the IDs to get in the second query, and then only need 2 queries instead of 1 per ID. That's still more than one, but it's another solution. $article_ids = array();while($row = mysql_fetch_array($result)) { $article_ids[] = $articleId = $row['articleId']; $rating = $row['rating']; echo $rating."".$articleId; echo '<br/>'; }$id_list = implode(',', $article_ids); // now $id_list is a comma-separated list of IDs you can use for SELECT .. IN Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.