Really slow MSQL

julian_chamberl

Hi, I have a bit of code that requests 4 seperate queries from different tables. It has to because certain info is contained on different ones. Is there any way of speeding it up or sending the mail in the background?

Code:

$result = mysqli_query($con,"SELECT * FROM bids WHERE quote_id='$id' ORDER BY quoted_price ASC LIMIT 1");
while($row = mysqli_fetch_array($result)) {
$lowest= $row['quoted_price'];
}

$result = mysqli_query($con,"SELECT * FROM bids WHERE quote_id='$id'");
while($row = mysqli_fetch_array($result)) {
$client= $row['client_id'];
$v.= $client.":";
$count++;
}

$a = explode(':', trim($v, ':'));

foreach ((array_unique($a)) as $value) {

//// Code goes here to get all lowest bid for job, compare it again lowest and email if they not the lowest"
$result = mysqli_query($con,"SELECT * FROM clients WHERE id='$value'");
while($row = mysqli_fetch_array($result)) {
$nameb= $row['name'];
$idb= $row['id'];
$passwordb= $row['password'];
$emailb= $row['email'];
$contactb= $row['contact'];
}

$result = mysqli_query($con,"SELECT * FROM bids WHERE quote_id='$id' AND client_id='$value' ORDER BY quoted_price ASC LIMIT 1");
while($row = mysqli_fetch_array($result)) {
$quote= $row['quoted_price'];
echo $nameb." lowest bid is ".$quote."<BR>";
}

///email the news and add link!

}

Thanks

Jules

Derokorian

Please use [noparse]

...

[/noparse] tags when posting code.

$result = mysqli_query($con,"SELECT * FROM bids WHERE quote_id='$id' ORDER BY quoted_price ASC LIMIT 1");
while($row = mysqli_fetch_array($result)) {
$lowest= $row['quoted_price'];
}

$result = mysqli_query($con,"SELECT * FROM bids WHERE quote_id='$id'");
while($row = mysqli_fetch_array($result)) {
$client= $row['client_id'];
$v.= $client.":";
$count++;	
}

There is no information in this first query, that you can't attain while looping over the second query. Example:

$lowest = PHP_INT_MAX;
foreach($rows as $row) {
   if( $row['column'] < $lowest ) {
      $lowest = $row['column'];
   }
   // do some other stuff here
}

a = explode(':', trim($v, ':')); 

foreach ((array_unique($a)) as $value) {

//// Code goes here to get all lowest bid for job, compare it again lowest and email if they not the lowest"
$result = mysqli_query($con,"SELECT * FROM clients WHERE id='$value'");
while($row = mysqli_fetch_array($result)) {
$nameb= $row['name'];
$idb= $row['id'];
$passwordb= $row['password'];
$emailb= $row['email'];
$contactb= $row['contact'];
}

$result = mysqli_query($con,"SELECT * FROM bids WHERE quote_id='$id' AND client_id='$value' ORDER BY quoted_price ASC LIMIT 1");
while($row = mysqli_fetch_array($result)) {
$quote= $row['quoted_price'];
echo $nameb." lowest bid is ".$quote."<BR>";
}

///email the news and add link!

}

This could be simplified by dropping the foreach all together, and changing both queries to use an in clause. Example:

$array = array(1, 2, 4, 7);
$values = "('" . implode("'.'", $array) . "')";
$result = mysqli_query($con,"SELECT * FROM clients WHERE id IN $values");

I didn't look in a lot of detail, these things just really stood out to me. My examples may contain bugs, but should demonstrate the general idea behind the techniques. Hope that helps!