select question

KashKurcura

hello:

i was wondering if anyone knows if it is possible to only display the rows that a user has not already selected from mysql?

Example:

// gets game_id's that user has already selected from 'selected_games'
$sql = "SELECT game_id FROM selected_games WHERE user_id = '$user_id'";

// displays game_id's that user has not selected from 'new_games'
$sql = "SELECT game_id FROM new_games WHERE game_id != '$game_id'";

now these will work if there is only one game that has been selected, what if the user has already select 3-4 game_id's aleady.. i dont want them to show. anyone know how i would go about getting this done?

Sxooter

where gameid not in (id1, id2, id3, id4);

KashKurcura

so how would i go about building 'not in (id1, id2, id3, id4)' from the first query? thanks

Sxooter

A subselect (Don't know if MySQL handles this kind, but everything else does🙂

select * from table1 where id not in (select id from table2 where select is false);

KashKurcura

I do not think mysql has subselects, is there not away to build that up from using the first query?

Sxooter

It has them, with some limited functionality, for a few cases. but, you can just build the in part by hand.

I.e. just grab the list and make the (1,2,3,4,5) part in PHP.

KashKurcura

I have version 3.23.56 but i how would i go about making the (1,2,3,4,5) part? thanks for your help

Sxooter

You'll need a for next loop. Here's the pseudo code for that:

$query = "select ...";  #<- query to get ids

$count = row_count(from previous query)
for ($i=0;$i<$count;$i++){
  $ids[] = result($res,$i);
}
$query = "select * from table where id not in (";
$query.= implode (",",$ids);
$query.= ")";

print $query