Trying to get While in a while loop to work

Question

Im trying to get a while loop working inside another while loop I have got it to work before but can't manage it again.

im trying to count the total records in the flights query and then in the hours record im trying to add the time. I have got all the queries working individually but when I put them together it don't work.

Here is my code what could I change to make it work?

     <?php
        error_reporting (E_ALL ^ E_DEPRECATED);

            // get results from database
            $result = mysql_query("SELECT * FROM  `phpvms_pilots` WHERE  `confirmed` =  '1' AND  `retired` =  '0' ORDER BY pilotid ASC;") 
                or die(mysql_error());

            $resulttotalflights = mysql_query("SELECT COUNT(*) AS total_flights FROM `phpvms_pireps` WHERE `pilotid` = '$pid';") 
                or die(mysql_error());

            $resulttotalhours = mysql_query("SELECT SEC_TO_TIME( SUM(TIME_TO_SEC( `flighttime_stamp` ) ) ) AS total_hours FROM phpvms_pireps WHERE pilotid = '$pid'") 
                or die(mysqli_error());

            echo "<table width='100%' border='0' cellpadding='0' align='center'>";
            echo "<tr>
                    <th align='center'>Pilot ID</th>
                    <th align='center'>Name</th>
                    <th align='center'>Rank</th>
                    <th align='center'>Total Flights</th>
                    <th align='center'>Total Hours</th>
                    <th align='center'>Vatsim ID</th>
                  <tr>";

            // loop through results of database query, displaying them in the table
            while($row = mysql_fetch_array( $result )) {



            //$pid = 'RFR' . $row['pilotid'];
            $pid = $row['pilotid'];
            $first = $row['firstname'];
            $last = $row['lastname'];
            $vid = $row['vatsimid'];
            $rank = $row['rank'];

            while($row_flights = mysql_fetch_array( $resulttotalflights )) {$flights = $row_flights['total_flights'];}

            while($row_hours = mysql_fetch_array( $resulttotalhours )) {$hours = $row_hours['total_hours'];}

                // echo out the contents of each row into a table
                echo "<tr>";
                //echo '<td>' . $pid . '</td>';
                echo '<td><a href="' . $vaptams_profile . $pid . '">' . 'RFR' . $pid . '</a></td>';
                echo '<td>' . $first . ' ' . $last . '</td>';
                echo '<td>' . $rank . '</td>';
                echo '<td>' . $flights . '</td>';
                echo '<td>' . $hours . '</td>';
                echo '<td><a href="' . $vatawere . $vid . '">' . $vid . '</a></td>';
                echo "</tr>"; 
            } 

            // close table>
            echo "</table>";
        ?>

Thanks Scott

Your code is likely vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, May 02 '17 at 13:38
Stop using the `mysql_*` functions. They have been deprecated since v5.5 (Jun 2013) and removed since v7.0 (Dec 2015). Instead use the [**mysqli_***](https://secure.php.net/manual/en/book.mysqli.php) or [**PDO**](https://secure.php.net/manual/en/book.pdo.php) functions with [**prepared statements**](https://secure.php.net/manual/en/pdo.prepare.php) and [**bound parameters**](https://secure.php.net/manual/en/pdostatement.bindparam.php). — Alex Howansky, May 02 '17 at 13:38
Before you even worry about that, rewrite this code without the `mysql_*` functions. Use `mysqli_*` or PDO instead. — WillardSolutions, May 02 '17 at 13:38
`=` to assign a variable, `+=` to add the value on the right to the variable on the left. — aynber, May 02 '17 at 13:40
Quick fix. Move the sencond and third query into the while. Like `$resulttotalflights = mysql_.... ; while($row_flights` then you get the right data for each `$pid` you iterate via the first query and the main while. — JustOnUnderMillions, May 02 '17 at 13:41

score 0 · Accepted Answer · answered May 02 '17 at 13:46

Instead of using 3 while loops each one coming with a single query that will trigger the database, why don't you just use one and only SQL query like this one:

SELECT
    `phpvms_pilots`.*,
    COUNT(`phpvms_pireps`.*) AS total_flights,
    SEC_TO_TIME( SUM(TIME_TO_SEC( `flighttime_stamp` ) ) ) AS total_hours
FROM `phpvms_pilots`
LEFT JOIN `phpvms_pireps` ON `phpvms_pireps`.`pilotid` = `phpvms_pilots`.`pilotid`
WHERE
    `phpvms_pilots`.`confirmed` = '1'
    AND `phpvms_pilots`.`retired` = '0'
ORDER BY pilotid ASC;

Trying to get While in a while loop to work

1 Answers1