Tomas Tomas - 4 months ago 5
MySQL Question

Is there a way to perform a MySQL query that accepts an array of values and only returns results of the first value found?

I'm trying to figure out if there's a way to run a single query that accepts an array of values to search on and goes one by one until there are results found.

Basically I want to take this sort of setup and put it in a single query:

$arr_vals = [ array of values here ];

foreach( $arr_vals as $arr_val ){
$results = mysql_query( SELECT * FROM my_db WHERE some_field = $arr_val );
if( mysql_num_rows( $results ) ){
return $results;
}
}


So, if there is any way to not run multiple queries and return only results for the current value. I'd rather not limit and I'd rather not filter results with PHP after because the result count could be high.

Thanks.

EDIT!!!

I don't want just a 'single' match here. I want results from the first matched value which could be 1 row or 1000 rows... a LIMIT could be put in place later, but I don't want that to be the crutch of the answer... obviously this is a simple answer if the LIMIT is 1... then I'd just use IN().

Answer

So you have an array of values to use as criteria, but each value may match many rows, and you want only the matches of the first value that matches, but you want all the rows that match. So LIMIT is totally not going to do what you want.

Assuming PHP variable $values is a comma-separated list of integers, so these values are safe to interpolate (not going to create an SQL injection vulnerability):

SELECT t.*
FROM MyTable AS t
JOIN (
  SELECT someField
  FROM MyTable
  WHERE someField IN ($values)
  ORDER BY FIND_IN_SET(someField, '$values')
  LIMIT 1
) AS first ON (t.someField = first.someField);

If the values are not integers, you'll have to make two PHP variables, one where each value is quoted individually, and the other where they are a single string, not quoted.

Demo: SQLFiddle