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

问题描述

我试图用一个数组来查询MySQL数据库，但我有麻烦了！

我有一个表称为客户端，我希望能够从他们的部门列等于$ sectorlink所有行选择'名'。

然后，我希望把所有的名字到一个数组，所以我可以完成我的下一个查询：选择所有的行从另一个表的客户列等于第一个查询返回的名字之一。我做错了什么，因为它返回一个致命SQL错误。我越来越糊涂了所有变量！

$ sectorlink和$连接是被这个code以外定义的唯一变量

有什么建议？

  $ QUERY1 =选择的名字从客户WHERE部门='$ sectorlink';
$ clientresult =的mysql_query（$ QUERY1，$连接）或trigger_error（SQL，E_USER_ERROR）;而（$行= mysql_fetch_array（$ clientresult））{的foreach（$行作为$关键=＆GT; $值）{$临时[] =''$价值。';}
$的thelist =破灭（，，$温度）;$查询=SELECT COUNT（*）FROM研究WHERE客户端（$行）ORDER BY（日期）DESC;
$结果= mysql_query（$查询，$连接）或trigger_error（SQL，E_USER_ERROR）;}
 

解决方案

第二个查询应使用 $的thelist 不是 $行，它应该是外，而循环。处理单排时，的foreach 循环是不必要的。你可以用一个简单的访问 $行名称 $行[0] 。事情是这样的（未经测试）：

  $ QUERY1 =选择的名字从客户WHERE部门='$ sectorlink';
$ clientresult =的mysql_query（$ QUERY1，$连接）或trigger_error（SQL，E_USER_ERROR）;而（$行= mysql_fetch_array（$ clientresult））{
    $温度[] ='。$行[0]。'';
}$的thelist =破灭（，，$温度）;
$查询=SELECT COUNT（*）FROM研究WHERE客户端（$的thelist）ORDER BY（日期）DESC;
$结果= mysql_query（$查询，$连接）或trigger_error（SQL，E_USER_ERROR）;
 

注意：请注意，您的code是非常容易的 SQL注入攻击。它的罚款用于测试或内部开发，但如果这个code为要运行你将要修复它颇有几分诺克斯堡网站。只是一个供参考。 ： - ）

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

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

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 and $connection are the only variables that are defined outside of this code

Any suggestions?

$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);

}

解决方案

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);

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屋！