can you query with an list?

[onedumbcoder]

i was wondering if there is anyway i can do this

 

lets say i have the string 

 

$e = "1, 3, 4, 5, 6";

 

then can i do this?

 

"SELECT * FROM user WHERE id !='" . $e . "'"?

 

If not, is there a way?

[ansarka]

 

"SELECT * FROM user WHERE id not in (" . $e . ")"?

[onedumbcoder]

does that actually work? if so thanks, i just need confirmation because of your "?" at the end  :'(

[PFMaBiSmAd]

For numeric values that should work (you could always try it and see). From the mysql manual -

 

expr IN (value,...)

 

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 10.2.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.

 

mysql> SELECT 2 IN (0,3,5,7);

        -> 0

mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');

        -> 1

 

You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN expression like this:

 

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

 

Instead, write it like this:

 

SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

 

The number of values in the IN list is only limited by the max_allowed_packet value.

 

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

 

IN() syntax can also be used to write certain types of subqueries. See Section 11.2.8.3, “Subqueries with ANY, IN, and SOME”.

 

expr NOT IN (value,...)

 

This is the same as NOT (expr IN (value,...)).

[onedumbcoder]

awesome, so then this should work right, if i want to grab a random user who has not been seen yet.

 

$query = "SELECT * FROM user WHERE id not in (" . $seenIds . ") ORDER BY RAND() LIMIT 1";
$result = mysql_query($query);

if(mysql_affected_rows() == 0)
{
     $seenIds = '';
     $query = "SELECT * FROM user WHERE 1 ORDER BY RAND() LIMIT 1";
     $result = mysql_query($query);
}

$user = mysql_fetch_array[$result];
$seenIds .= $userinfo['id'] . ", ";

 

Also if i know im only getting back one element is there something else i can use besides mysql_fetch_array() that is more efficient?

 

Thanks!

 

[ansarka]

 

$query = "SELECT count(*) as counter FROM user WHERE id not in (" . $seenIds . ") ORDER BY RAND() LIMIT 1";

$result = mysql_query($query);

$row_number= mysql_fetch_array($result);

if($row_number['counter'] > 0)

{

    $seenIds = '';

    $query = "SELECT * FROM user WHERE 1 ORDER BY RAND() LIMIT 1";

    $result = mysql_query($query);

}

 

while($row=mysql_fetch_array($result))

{

  echo $row['name'];

  echo $row['age'];

}

[onedumbcoder]

Thank you ansarka for the help.

 

Can you please translate this in english: "count(*) as counter"

 

is that saying create a variable counter that equals to how many elements this query gets back?

[GingerRobot]

Pretty much - you're selecting the number of rows that match, and your giving that the name 'counter' in your record set. Personally i'd just use the one query:

 

<?php
$query = "SELECT * FROM user WHERE id not in (" . $seenIds . ") ORDER BY RAND() LIMIT 1";
$result = mysql_query($query);
$num = mysql_num_rows($result);
if($num > 0)
{
    $row = mysql_fetch_assoc($result);
    echo $row['name'];
    echo $row['age'];
}
?>

 

[onedumbcoder]

hmm for some reason its still not working:

 

SELECT * FROM picture WHERE id not in (0, 41, 34, 28, 21, 13, 40, 12, 42, 12, 16, 10, 42) ORDER BY RAND() LIMIT 1

 

 

---- UPDATE ---

 

NM, i had "id" instead of "user_id".

 

Thanks everyone for the help!