How to put SQL query in a for() loop

Question

Look at this code first:

<?php

$dbhost = 'xxxxx';
$dbuser = 'xxxxxx';
$dbpass = 'xxxxxxx';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  //Some code
}

$sql1 = 'SELECT AVG(time) FROM lucky WHERE interviewer=1';
$sql2 = 'SELECT AVG(time) FROM lucky WHERE interviewer=2';
.
.
$sql100 = 'SELECT AVG(time) FROM lucky WHERE interviewer=100';

mysql_select_db('xxxxxx');

$retval1 = mysql_query( $sql1, $conn );
$avg1 = mysql_result($retval1,0);
.
.
$retval100 = mysql_query( $sql100, $conn );
$avg100 = mysql_result($retval100,0);

if (! $retval)
{
  //Some code
}

mysql_close($conn);

?>

<table width='1100' border='0' align='center'>
  <tr>
    <td>1</td>
    <td><?php echo round($avg1, 2); ?></td>
  </tr>
  .
  .
  <tr>
    <td>100</td>
    <td><?php echo round($avg100, 2); ?></td>
  </tr>
</table>

What actually happening is if interviewer 1 is found in db, then its average of time is to be calculated and then displayed in the table below. It has to be done for 100 interviewers. All I want is to do this using a shorter method like using a for() loop. Additionally, I want that if interviewer 1 is not found in db, I don't want to display the blank row. I want to display it only if there is a hit. Please help.

No man. Don't put queries in a loop especially not 100 queries. Just retrieve the interviewer and all his interviews with two queries. Than if interviewer result has found an entry loop the results of the interviews result and calculate the avg. time of interview. But don't put queries into loops. Why 100 Queries for what you can do in 1 Query, bad programming practice man. — Sterling Duchess
– Sterling Duchess, Commented Mar 7, 2014 at 10:55
Also for gods sake stop using mysql_ and start using mysqli/PDO... — Sterling Duchess
– Sterling Duchess, Commented Mar 7, 2014 at 10:56

Nick R · Accepted Answer · 2014-03-07 11:16:27Z

6

You REALLY don't want to put SQL in a loop You'll be executing 100 queries instead of a single one.

That will kill your website's performance, when you can just use simple aggregation as illustrated below for optimal performance.

Also, the mysql library is deprecated, use mysqli instead.

Do this:

$sql = 'SELECT interviewer, AVG(time) AS avg_time 
        FROM lucky 
        WHERE interviewer BETWEEN 1 AND 100
        GROUP BY interviewer';

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

// Execute the query
$result = $mysqli->query($sql);

// Display the results in a table
echo '<table><tbody>';
while ($row = $result->fetch_assoc()) {
    echo '<tr>';
    echo '<td>' . $row['interviewer'] . '</td>';
    echo '<td>' . round($row['avg_time'], 2) . '</td>';
    echo '</tr>';
}
echo '</tbody></table>';

// Close the resultset
$result->close();

What I'm doing above is pulling all interviewers' average times, grouping them together by interviewer #.

This will automatically eliminate missing interviewers at the database level.

Then, I'm looping through the DB results with a while loop.

This is the accepted programming practice for querying and displaying multiple items from your database. The DB is very fast at filtering and aggregating data. Use it for what it's good at. If you try to pull that logic out into PHP, and query the database 100 times, you'll add 100x the latency and connection handling for nothing.

edited Mar 7, 2014 at 11:16

Nick R

7,8142 gold badges24 silver badges33 bronze badges

answered Mar 7, 2014 at 10:57

Steven Moseley

16.4k4 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Niks Over a year ago

Specially, the explanation you gave, really helps. Being a beginner, I would have applied a for loop anyway... :p

Niks Over a year ago

Dear Steven: The example you gave can be used only to display Interviewers' avg time. If I want to show avg time per city as well, then what should I do?

Steven Moseley Over a year ago

@Niks - Create a 2nd query that does something like SELECT city, AVG(time) FROM lucky GROUP BY city

Naveed Yousaf · Accepted Answer · 2014-03-07 10:59:44Z

0

echo "<table width='1100' border='0' align='center'>";
  for($i=1; i<=100; $i++)
  {
   $sql1 = 'SELECT AVG(time) FROM lucky WHERE interviewer='.$i;
    echo "<tr>";

     mysql_select_db('xxxxxx');
     $retval1 = mysql_query( $sql1, $conn );
     $avg1 = mysql_result($retval1,0);
     echo "</tr>";
   }
 echo "</table>";

answered Mar 7, 2014 at 10:59

Naveed Yousaf

1332 silver badges13 bronze badges

Comments

Ajitha Ms · Accepted Answer · 2014-03-07 11:15:15Z

0

YOU may use this simple query.

SELECT avg(time) FROM lucky GROUP BY  interviewer

No need to looping.

answered Mar 7, 2014 at 11:15

Ajitha Ms

5555 silver badges19 bronze badges

Comments

Prafulla · Accepted Answer · 2014-03-07 11:22:26Z

0

try this code

for($i=1; i<=100; $i++)
{
$sql1 = 'SELECT AVG(time) FROM lucky WHERE interviewer='.$i;

}

OR

$sql1 = 'SELECT AVG(time) FROM lucky WHERE interviewer BETWEEN 1 AND 100

edited Mar 7, 2014 at 11:22

answered Mar 7, 2014 at 10:54

Prafulla

5987 silver badges18 bronze badges

5 Comments

Niks Over a year ago

It will assign all interviewers from 1 to 100 to $sql1. And how will I display it in the HTML table below ?

Sterling Duchess Over a year ago

Downvoted for horrible programming practice. At least you could have added an explanation why this is just horribly wrong. And gave him a heads up on mysql_. This is just horrible also I don't think it's exactly what he asked for. And the select_db statement is pointless also the query is never executed.

Prafulla Over a year ago

you can write for loop in <tr></tr>

Nick R Over a year ago

Don't use this - you're querying the DB 100 times, when you only need to query it once!

Prafulla Over a year ago

@NickR Ya I know that...But i answered as per his code as he wrote question 1 2...100..

Collectives™ on Stack Overflow

How to put SQL query in a for() loop

4 Answers 4

3 Comments

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related