I have and array with two values and I want to use it with sql IN operator in select query.
Here is the structure of my table
id comp_id 1 2 2 3 3 1
I have an array $arr
which have two values Array ( [0] => 1 [1] => 2 )
I want to fetch the record of comp_id 1 and comp_id 2. So I wrote the following query.
SELECT * from table Where comp_id IN ($arr)
But it does not return the results.
Define arrays as SQL variables. Use the ARRAY_AGG built-in function in a cursor declaration, to assign the rows of a single-column result table to elements of an array. Use the cursor to retrieve the array into an SQL out parameter. Use an array constructor to initialize an array.
PHP is the most popular scripting language for web development. It is free, open source and server-side (the code is executed on the server). MySQL is a Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).
The rules of adding a PHP variable inside of any MySQL statement are plain and simple: Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) MUST be added through a prepared statement. No exceptions.
Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL. The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both.
Since you have plain integers, you can simply join them with commas:
$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(',', $arr) . ")";
If working with with strings, particularly untrusted input:
$sql = "SELECT * FROM table WHERE comp_id IN ('" . implode("','", array_map('mysql_real_escape_string', $arr)) . "')";
Note this does not cope with values such as NULL
(will be saved as empty string), and will add quotes blindly around numeric values, which does not work if using strict mysql mode.
mysql_real_escape_string
is the function from the original mysql driver extension, if using a more recent driver like mysqli, use mysqli_real_escape_string
instead.
However, if you just want to work with untrusted numbers, you can use intval
or floatval
to sanitise the input:
$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(",", array_map('intval', $arr)) . ")";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With