Jump to content

Select Statement Help Needed


shujjah
 Share

Recommended Posts

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

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

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

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

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

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

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...