Jump to content

can you query with an list?


onedumbcoder

Recommended Posts

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,...)).

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!

 

 

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

}

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'];
}
?>

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.