I have an array, $scans. I want to query MySQL with all the values in that array and get my results back in an array. For example, sample data in scans would be:
E1234
E2244
E3654
The MYSQL table PARTS has fields part, size, length, plate, side, type.
I want to end up with $output["E1234"][0] to be the size result for that part, 1 to be length, etc. and I want the array to be sortable by the MYSQL query. (order by SIDE desc, PLATE asc).
Right now, i'm just stepping through the $SCANS array and doing query after query, but I'm not able to then sort all the results properly.
Is this possible? This is what I'm doing now, but obviously since each query returns one row which is then outputted, there's no sortability. I want to be able to perform one query, sort the results within the array, and then output that data after the sort.
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 is now following 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 is:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/foo/bar/sort.php on line 35
Line 35 is while($row = mysql_fetch_array($result)){
FINAL EDIT:
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'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
?>