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

问题描述：

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

我有一个由默认密钥中的ID组成的数组，我想将这些ID与MySQL中的对应描述相关联由这些ID组成的表和不同列中的描述。下面的表和数组示例： p>

 数组
（
 [0] =＆gt; 1 
 [1] =＆gt; 12 
 [2] =  ＆gt; 17 
 [3] =＆gt; 21 
 [4] =＆gt; 26 
）
 
 + ---- + -----------------  ----------- + \ N |  ID |  description | 
 + ---- + ---------------------------- + 
 |  1 | 示例描述| 
 + ---- + ---------------------------- + 
 |  2 | 哇，这是一个描述| 
 + ---- + ---------------------------- + 
 |  3 | 惊人的描述| 
 + ---- + ---------------------------- + 
 |  4 |  ID4 | 
 + ---- + ---------------------------- + 
 |的描述 5 | 是的，另一个描述| 
 + ---- + ---------------------------- + 
  code>  pre  > 
 
 输出必须如下所示（带或不带逗号）： p> 
 
 
 描述，另一种描述，描述...... 
   code>  pre> 
 
 数组名为'$ arraymanutenzionehwos'，表'interventi_hwos' p> 
 
 
我提供了一般情况，但如果需要，我可以提供 我的代码和所有需要的细节。 p> 
 
 
脚本的主要过程是在数组的每个元素中选择ID并将其绑定到Mysql表列中的正确描述。  / p> 
 
 
 非常感谢 strong>  p> 
  div>

答

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.

答

Without more details it's hard to provide a complete solution, but here is a query you can use:

$sql = "
SELECT description FROM interventi_hwos
WHERE id IN (" . implode(',', $arraymanutenzionehwos). ")
ORDER BY FIELD(id, " . implode(',', $arraymanutenzionehwos). ")
";

Given your example data, this will produce a query like:

SELECT description FROM interventi_hwos
WHERE id IN (1,12,17,21,26)
ORDER BY FIELD (id, 1,12,17,21,26)

See a demo of this query

This will give you the list of descriptions based on the id's in your array, in the same order as the original id array. You can use PHP to format the resulting array however you please. Note, if the order of the result doesn't matter, remove the ORDER BY clause.

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

相关推荐