logo标签列表

PHP/MySQL：使用数组查询MySQL并以数组形式获取结果

phpmysqlarrayssorting
3

我有一个数组 $scans。我想用该数组中的所有值查询 MySQL，并将结果以数组形式返回。例如，$scans 中的样本数据如下：

E1234
E2244
E3654

MYSQL表PARTS具有字段part, size, length, plate, side, type。
我希望最终得到的是$output["E1234"][0]是该零件的尺寸结果，1是长度，以此类推。并且我希望可以通过MYSQL查询对数组进行排序（按SIDE desc, PLATE asc排序）。
目前，我只是在遍历$SCANS数组并执行一个接一个的查询，但我无法正确地对所有结果进行排序。
这是否可能？现在我正在做的就是每个查询返回一个行，然后输出，显然没有可排序性。我想能够执行一次查询，在数组内对结果进行排序，然后在排序后输出数据。
    foreach ($scans as $currentscan) {
        $partselect = substr(mysql_real_escape_string($currentscan), 0, 5);

        $query = "SELECT french, length, plate, side, type FROM parts WHERE part = '$partselect' ORDER BY side DESC, plate ASC LIMIT 1";
        $result = mysql_query($query);
        #echo 'query is '.$query.'   <br>';
        $error = mysql_error();
        $num_rows = mysql_num_rows($result);
        if ($num_rows == 0) {
            echo 'BAD PART: '.$currentscan.' '.$partselect.' error is '.$error.'<br \>
            ';
        } else {
            $row = mysql_fetch_array($result);
            print $partselect.' is a '.$row['french'].'/'.$row['length'].' - '.$row['type'].' - '.$row['plate'].'('.$row['side'].')<br \>';
        }
    };

编辑：根据这里的一些建议，现在以下是代码：

$scans = explode("\n",$_POST['scans']);

foreach ($scans as $currentscan) {
   if ($currentscan[0] == "E") { //this is a cheap trick to ignore a bad scan inherent in the scanning mechanism
   $partselect = substr(mysql_real_escape_string($currentscan), 0, 5);
   $tempQuery .= 'part = "'.$partselect.'" OR ';

   };
};//end foreach 
$tempQuery = substr($tempQuery, 0, -3); //remove last OR (fixed substr to 0,-3 to scrip final OR - stephen) 
$tempQuery .= ") ORDER BY side DESC, plate ASC LIMIT 1"; //add on end of query 
$query = "SELECT french, length, plate, side, type FROM parts WHERE ".$tempQuery;
$result = mysql_query($query);
echo $result;
while($row = mysql_fetch_array($result)){
   print $row['french']." / ".$row['length']; //just doing something pointless to verify data was pulled.
}

结果为：

警告：mysql_fetch_array（）：在/home/foo/bar/sort.php的第35行提供的参数不是有效的MySQL结果资源

第35行为while($row = mysql_fetch_array($result)){

最终编辑：

已解决。

//DECLARED CONSTANTS//

if (!$_POST) { // Have scans been entered? If not, display the form.
print '
<html>
<body>
Scans:
<form action="index.php" method="post">
<textarea rows="20" cols="6" name="scans" id="scans">
</textarea>
<br />
<input type="submit" value="Submit" />
</body>
</html>
';
} else { //Scans have been entered. Start scan processing logic

//==openDB==//
mysql_connect(SQLSERVER, SQLUSER, SQLPASSWORD) or die("Can not connect to DB server.");
mysql_select_db(DATABASE) or die("Can not connect to Database.");
//==openDB==//

$scans = explode("\n",$_POST['scans']); // Explode posted scans into scans array

foreach ($scans as $currentscan) { // step through each scan
   if ($currentscan[0] == "E") { //Cheap check for real part numbers.
   $partselect = substr(mysql_real_escape_string($currentscan), 0, 5); // Strip off the extraneous data from the scan
   $count{$partselect}++; //Count instances of particular parts. ideally this would be in an array in the form of $count[$partnumber] so I can easily display that data at the end. each part needs to be displayed every time it's scanned.
   $tempQuery .= 'part = "'.$partselect.'" OR '; //Starts building query

   };
};//end foreach 
$tempQuery = substr($tempQuery, 0, -3); //remove last OR 

$tempQuery .= ") ORDER BY side DESC, plate ASC"; //add on end of query 
$query = "SELECT part, french, length, plate, side, type FROM parts WHERE (".$tempQuery; //Form beginning of query
$result = mysql_query($query);// execute query
while($row = mysql_fetch_array($result)){ // step through results
   for ($i = 0; $i < $count{$row['part']}; $i++) { //if a part was scanned $count{$row['part']} times, display it that many times
   print $row['part']." ".$row['french']." / ".$row['length']." ".$row['plate']."(".$row['side'].")<br>";  //data parsing goes here. this will be expanded.
   };// close for loop
};//close while loop        
};//close else

?>
Stephen，你需要查看已构建的查询。它可能并不完全正确。在迭代之前，请检查 num_rows 是否返回了结果。 - Chris
3个回答
0
<?php
// Make a MySQL Connection
$query = "SELECT * FROM example"; 

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['name']. " - ". $row['age'];

?>

感谢http://www.tizag.com/mysqlTutorial/mysqlfetcharray.php提供的帮助。

如果您期望多个结果，也可以这样做：

<?php
// Make a MySQL Connection
$query = "SELECT * FROM example"; 

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
   echo $row['name']. " - ". $row['age'];
}


?>

已添加代码以供您评论：

foreach ($scans as $currentscan) {
   $partselect = substr(mysql_real_escape_string($currentscan), 0, 5);
   $tempQuery .= "(part = ".$partselect." OR";
}//end foreach 
$tempQuery = substr($tempQuery,-2); //remove last OR 
$tempQuery .= ") ORDER BY side DESC, plate ASC LIMIT 1"; //add on end of query 
$query = "SELECT french, length, plate, side, type FROM parts WHERE ".$tempQuery;
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
   //do something with row in result set... 
}
这并不是我正在寻找的内容。更新了代码示例以更清晰明了。 - Screevo
@stephen 我更新了你的代码，首先构建了 $scans 数组中所有项目的查询，然后在结果集上循环遍历... - Chris
这似乎可以工作，但是substr语法似乎不正确，因为在此之后，$tempQuery =“R”（R和一个空格）。 - Screevo
@stephen 更新了 for 循环，它需要使用 .= 来替代 =，这就是为什么你会得到错误值的原因。 - Chris
编辑了原始帖子，加入了我运行的代码和它抛出的错误。我要去阅读关于mysql_fetch_array的内容。 - Screevo
请注意，在输出HTML时不应使用“or die”。这会导致无效的HTML。 此外，不应向非管理员用户显示数据库错误消息；这样做会泄露太多信息。 - outis
0
你需要做的是在PHP中构建一个WHERE子句，它可以让你选择所有具有任何零件号的行。类似这样的语句：
$whereExpr = array();
for ($i = 0; $i < count($scans); $i++) {
  $whereExpr[] = "part = \"{$scans[$i]}\"";
}

$whereExpr = implode(" OR ", $whereExpr);

$query = "SELECT * FROM `parts` WHERE $whereExpr ORDER BY <whatev>";

$result = array();
while ($row = mysql_fetch_array) {
    $result[ $row['part'] ] = $row;
}
哦，这会创建一个巨大的查询。我喜欢这个。尝试一下。 - Screevo
如果这是用户生成的 $whereExpr，请小心，因为您正在向 SQL 注入打开数据库：http://en.wikipedia.org/wiki/SQL_injection - Phill Pafford
当然，你应该使用mysql_real_escape_string等函数。这只是一些快速拼凑的示例代码。 - eliego
这段代码是用于内部数据库应用程序的，不会公开使用，但是会进行转义处理。 - Screevo
-1

MySQL Fetch Array Function

看了你的更新/编辑后，你可以使用IN子句。

// Represents your $scans array
$arr = array('Hello','World!','Beautiful','Day!');

foreach ($arr as $currentscan) {
    $partselect[] = substr(mysql_real_escape_string($currentscan), 0, 5);
}

$inExpr = implode("','",$partselect);

$query = "SELECT french, length, plate, side, type FROM parts WHERE part IN('$inExpr') ORDER BY side DESC";

$result = mysql_query($query);

while($row = mysql_fetch_array($result)) {
    print $partselect.' is a '.$row['french'].'/'.$row['length'].' - '.$row['type'].' - '.$row['plate'].'('.$row['side'].')<br \>';
}
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接