0

Got a question for you all...

What would be the best way to search my table by array, that has an array in the table.

EG:

$var = (1,4,7,9,14)

$Query = "SELECT * FROM business_listings WHERE category IN ($var)";

'category' would have 4,27,89,101

How can I get this to match if one of the numbers in the $var matches one of the numbers in the table.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
lithiumdesign
  • 95
  • 1
  • 9

1 Answers1

0

If your database column is a list of comma separated values, and you're searching for one value in that list, then you're in a different situation.

If your category column contains the text value 410,406,149,152, like you commented below, and you're searching for fields whose category contains 152, then you'll need to use MySQL's FIND_IN_SET() function.

If you have to check multiple values, then you need to use more than one FIND_IN_SET. If you read the documentation, you'll see that the first argument for FIND_IN_SET must be a single string, not a string list (it can't contain a comma). Use the following instead:

$var = "401,320,152";
$items = explode(",", $var);
foreach ($items as &$i) {
    $i = "FIND_IN_SET('" . $i . "', `category`)";
}
$search = implode(" OR ", $items);
unset($i);
$query = "SELECT * FROM business_listings WHERE " . $items;

This will output:

SELECT * FROM business_listings WHERE
    FIND_IN_SET('401', `category`) OR
    FIND_IN_SET('320', `category`) OR
    FIND_IN_SET('152', `category`)

The above script will work even if $var contains only one value.

Finally, as tadman mentioned, since we're getting into queries that can be tricky to build with prepared statements, you need to make sure you're escaping and sanitizing your input properly. For an example, if $var is being retrieved from the user somehow, then before you modify it in any way, you need to escape it with mysqli_real_escape_string():

$var = $mysqli->real_escape_string($var);

Assuming that $mysqli is your open MySQLi connection.

Hope this helps!

Community
  • 1
  • 1
theftprevention
  • 5,083
  • 3
  • 18
  • 31
  • so my $var now reads '152' my table reads 410,406,149,152 but it only works if the 152 is the first number. It looks like its only looking at the first number in the table, not the others if $var = 410 it works if $var = 152 it doesn't work – lithiumdesign Sep 25 '13 at 16:49
  • 1
    Ah! I guess I misunderstood what you're trying to do. See my edit. – theftprevention Sep 25 '13 at 17:06
  • If you're going to compose a query using string concatenation, please **properly escape** anything going in there. This is a bad pattern to follow. – tadman Sep 25 '13 at 17:40
  • Thanks for the help theftprevention so if my $var ="401,152" it doesn't work – lithiumdesign Sep 25 '13 at 17:55
  • Everything seems to be working great, until I add LIMIT 10,0 if I add that I don't get anything. It seems like the OFFSET is causing the problem as LIMIT 10 works fine – lithiumdesign Sep 25 '13 at 20:12
  • Well, look at the [**`LIMIT` clause**](http://dev.mysql.com/doc/refman/5.0/en/select.html#idm46914929224272). When there's only one argument (i.e. `LIMIT 10`), the argument is interpreted as a `length`. You'll get 10 results, at most. But when there are two arguments, then the first is an `offset` and **the second is a `length`.** So, `LIMIT 10,0` tells MySQL to start at the eleventh row (because `offset` counts from 0), and to return a maximum of *zero rows.* So, it seems you got your arguments swapped. – theftprevention Sep 25 '13 at 20:42