将具有列ID的MySQL字符串绑定到PHP数组中的该ID

Question

I have an array composed by IDs in default keys, I want to link these IDs with there correspondent description in a MySQL table composed by these IDs and the description in different columns. Example of the Table and Array below:

Array
(
[0] => 1
[1] => 12
[2] => 17
[3] => 21
[4] => 26
)

+----+----------------------------+
| ID | description                |
+----+----------------------------+
| 1  | Example Description        |
+----+----------------------------+
| 2  | Wow, this is a description |
+----+----------------------------+
| 3  | Amazing description        |
+----+----------------------------+
| 4  | Description for ID4        |
+----+----------------------------+
| 5  | Yes, another description   |
+----+----------------------------+

The output have to look like the following(with or without commas) :

Description, Another description, description...

The array is named '$arraymanutenzionehwos' and the table 'interventi_hwos'

I have provided general case, but if needed I can provide my code and all the needed details.

The main process of the script were to pick the ID in each element of the array and bind it to the proper description in the Mysql table column.

Thanks a lot

Answer 1

I think you want to use a query something like this:

 SELECT GROUP_CONCAT(DISTINCT description ORDER BY description SEPARATOR ', ' ) AS d
   FROM interventi_hwos
  WHERE id IN (1,12,17,21,26)

This will yield one row containing one column with the comma-concatenated string you want.

To do that from php you're going to need to do something like this to put together your query string.

$query = <<< 'ENDQUERY'
SELECT GROUP_CONCAT(DISTINCT description ORDER BY description SEPARATOR ', ' ) AS d
  FROM interventi_hwos
 WHERE id IN 
ENDQUERY
$query .= ' (' . implode( ',', array_filter($arraymanutenzionehwos, 'is_int') . ')';

Notice that this sanitizes your array by eliminating any item from it that isn't an integer. That's helpful to prevent SQL injection.