Mysql where id is in array: Why Your Query Returns Empty Results
You have successfully parsed your string of IDs into a numeric array using explode and array_map. When you inspect the variable, you see five integers at indices 0 through 4. Logically, these are valid identifiers. However, when you construct the SQL query using that array directly inside single quotes within the PHP code, the database returns zero rows. This discrepancy isn't a bug in your logic; it is a fundamental misunderstanding of how MySQL interprets data types and how PHP handles variable interpolation inside strings.
The core issue lies in this line: WHERE id IN ('".$array."'). Even though $array contains integers, the syntax forces MySQL to treat the entire array as a single string literal because of the surrounding single quotes. When PHP interpolates $array into that string, it converts the array representation to something like Array (depending on the context) or simply fails to pass the individual values as separate arguments to the SQL statement. The database then tries to compare the column id against a string that looks like an array object, rather than comparing it against a set of discrete numbers.
To fix this, you must explicitly join the array elements into a comma-separated string before embedding them in the SQL query. The most robust method uses implode to combine the integers with commas and spaces. This creates a clean string representation that MySQL can parse correctly as a list of values.
Here is the corrected approach using raw PHP and MySQLi:
$string = "1,2,3,4,5";
$array = array_map('intval', explode(',', $string));
// Join the integers into a comma-separated string
$ids = implode(', ', $array);
$query = mysqli_query($conn, "SELECT name FROM users WHERE id IN ($ids)");
By replacing ($array) with ($ids), you are now passing a SQL-compatible list: (1, 2, 3, 4, 5). MySQL correctly interprets this as an IN clause containing five distinct values. Note that the parentheses around $ids are crucial; without them, or if you wrap it in quotes like '($ids)', the query structure breaks.
While this manual string construction works for simple cases, it introduces significant security risks if the source data is not strictly validated. If a user can inject malicious characters into the initial $string, they could break the implode output or alter the SQL logic. Therefore, in production environments where input comes from external sources, prepared statements are mandatory.
Using mysqli_prepare requires a slightly different strategy because you cannot bind an entire comma-separated list as a single parameter for an IN clause directly in older MySQL versions. You must construct the bound values dynamically or use a placeholder generation method that ensures the number of question marks matches the array length.
$ids = implode(',', $array); // Join without spaces if binding individually, or handle logic carefully
$query = "SELECT name FROM users WHERE id IN (" . implode('?,', $array) . ")";
$stmt = mysqli_prepare($conn, $query);
// Bind parameters one by one
$params = str_repeat('i', count($array)); // 'i' for integer
mysqli_stmt_bind_param($stmt, $params, ...$array);
However, the most modern and concise solution in the PHP ecosystem is to leverage Laravel. The framework abstracts away these string concatenation pitfalls entirely through its query builder or Eloquent ORM. If you are building a new application or migrating legacy code, consider using whereIn. This method accepts an array directly and handles the SQL generation safely.
// Using Laravel Query Builder
DB::table('users')
->whereIn('id', $array)
->get();
This approach automatically generates the correct IN (1, 2, 3, 4, 5) syntax and manages parameter binding to prevent SQL injection. It also handles edge cases where the array might be empty or contain duplicates more gracefully than manual string manipulation.
