All geek questions in one place

PHP / mysql: query MySQL with an array and get the results in an array

I have an array, $ scans. I want to query MySQL with all the values ​​in this array and return the results to the array. For example, sample data in a scan will be as follows:

E1234
E2244
E3654

The MYSQL table PARTShas fields part, size, length, plate, side, type.

I want to end with $output["E1234"][0]as a result of the size for this part, 1 is the length, etc., and I want the array to be sorted by the MYSQL query. (order SIDE desc, PLATE asc).

Right now, I'm just stepping over the array $SCANSand making a query after the query, but I cannot sort all the results correctly.

Is it possible? This is what I am doing now, but obviously, since each query returns a single row, which is then output, there is no sorting. I want to be able to execute one query, sort the results in an array and then output this data after sorting.

    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 \>';
        }
    };

EDIT: this is the code as it now follows some suggestions here:

$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.
}

result:

Warning: mysql_fetch_array (): the provided argument is not a valid MySQL result resource in /home/foo/bar/sort.php on line 35

Line 35 while ($ row = mysql_fetch_array ($ result)) {

COMPLETION:

It works.

//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 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

?>
+3
source share
3 answers
<?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'];

?>

Courtesy of 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... 
}
0

, WHERE PHP, , - . - :

$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;
}
0

Fetch Array Function for MySQL

After viewing your update / editing you can use the IN clause

// 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 \>';
}
-1
source

Source: https://habr.com/ru/post/1760157/

More articles:


All Articles