使用数组的 MySQL 查询 [英] MySQL query using an array

问题描述

我正在尝试使用数组查询 MySQL 数据库，但遇到了问题！

I'm trying to query a MySQL database using an array but I'm having trouble!

我有一个名为 clients 的表，我希望能够从sector"列等于 $sectorlink 的所有行中选择name".

I have a table called clients, I want to be able to select 'name' from all rows whose 'sector' column is equal to $sectorlink.

然后我想将所有名称放入一个数组中，以便我可以执行我的下一个查询:从另一个表中选择所有行，该表的客户"列等于从第一个查询返回的名称之一.我做错了什么，因为它返回一个致命的 SQL 错误.我对所有变量感到困惑！

I then want to put all the names into an array so I can perform my next query: select all rows from another table whose 'client' column is equal to one of the names returned from the first query. I'm doing something wrong because it returns a fatal SQL error. I'm getting confused with all the variables!

$sectorlink 和 $connection 是在此代码之外定义的唯一变量

$sectorlink and $connection are the only variables that are defined outside of this code

有什么建议吗?

$query1 = "SELECT name FROM clients WHERE sector = '$sectorlink'";
$clientresult = mysql_query($query1, $connection) or trigger_error("SQL", E_USER_ERROR);

while($row = mysql_fetch_array($clientresult)){

foreach($row AS $key => $value){$temp[] = '"'.$value.'"';}
$thelist = implode(",",$temp);

$query = "SELECT count(*) FROM studies WHERE client IN ($row) ORDER BY (date) desc";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

}

推荐答案

第二个查询应该使用 $thelist 而不是 $row，并且应该在 $thelist 之外code>while 循环.处理单行时不需要 foreach 循环.您可以使用简单的 $row[0] 访问 $row 中的名称.像这样的东西(未经测试):

The second query should use $thelist not $row, and it should be outside of the while loop. The foreach loop is unnecessary when processing a single row. You can access the name in $row with a simple $row[0]. Something like this (untested):

$query1 = "SELECT name FROM clients WHERE sector = '$sectorlink'";
$clientresult = mysql_query($query1, $connection) or trigger_error("SQL", E_USER_ERROR);

while($row = mysql_fetch_array($clientresult)){
    $temp[] = '"'.$row[0].'"';
}

$thelist = implode(",",$temp);
$query = "SELECT count(*) FROM studies WHERE client IN ($thelist) ORDER BY (date) desc";
$result = mysql_query($query, $connection) or trigger_error("SQL", E_USER_ERROR);

注意:请注意，您的代码极易受到SQL 注入攻击.它适用于测试或内部开发，但如果此代码将运行 Fort Knox 网站，您将需要对其进行大量修复.仅供参考.:-)

Caution: Please be aware that your code is highly vulnerable to SQL injection attacks. It's fine for testing or internal development but if this code is going to be running the Fort Knox web site you're going to want to fix it up quite a bit. Just an FYI. :-)

这篇关于使用数组的 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！