Nesting do while loops in PHP

Question

im trying to get the total monthly sales for each client in DB, i used the following code:

SELECT *, SUM(total_amount)
FROM orders
WHERE YEAR(order_date) = YEAR(NOW()) AND client = clientname
GROUP BY MONTH(datetime)

clientname is a variable holding the clients array.

here is the sql query execution (im using dreamweaver):

mysql_select_db($database, $server);

$query_clients = "SELECT client_name 
    FROM clients
    ORDER BY client_name ASC";
$clients = mysql_query($query_clients, $server)
    or die(mysql_error());
$row_clients = mysql_fetch_assoc($clients);
$totalRows_clients = mysql_num_rows($clients);

$clientname_MonthlySalesClient = "-1";

if (isset($row_clients['client_name'])) {
  $clientname_MonthlySalesClient = $row_clients['client_name'];
}

mysql_select_db($database_server, $server);

$query_MonthlySalesClient =
    sprintf("SELECT *, SUM(total_amount)
        FROM orders
        WHERE YEAR(order_date) = YEAR(NOW())
        AND client = %s
        GROUP BY MONTH(order_date)",
    GetSQLValueString($clientname_MonthlySalesClient, "text"));

$MonthlySalesClient = mysql_query($query_MonthlySalesClient, $server) or die(mysql_error());
$row_MonthlySalesClient = mysql_fetch_assoc($MonthlySalesClient);

$totalRows_MonthlySalesClient = mysql_num_rows($MonthlySalesClient);

then i used the following code in PHP:

<?php do { ?>
<tr>
    <td>
    <?php echo $row_clients['client_name']; ?>
    </td>
    <?php do { ?>
        <td>
        <?php
        echo number_format($row_MonthlySalesClient['SUM(total_amount)']);
        ?>
        </td>
    <?php
    } while ($row_MonthlySalesClient = mysql_fetch_assoc($MonthlySalesClient));
    ?>
</tr>
<?php }
while ($row_clients = mysql_fetch_assoc($clients));
?>

im getting the monthly sales for the first client correctly, but for following clients it shows zero result. i don't know which code im missing to get it work!

Answer 1

The code you've posted isn't going to work. From the docs:

do-while loops are very similar to while loops, except the truth expression is checked at the end of each iteration instead of in the beginning.

In other words, $row_MonthlySalesClient = mysql_fetch_assoc($MonthlySalesClient) hasn't been run on your first iteration of the inner do-while loop.

Also, I don't see where you're actually executing the SQL query. Is there more to your code?

Try changing to a while loop and detecting afterward if the set was empty to show an 'empty result' message. From the code's perspective, there's no guarantee that there will be any rows in the result set of the query, which the use of a do-while implies.

Edit: I think your problem is that you're only executing the client name query once, instead of once for each row. At least in the loops as you've shown them, the $MonthlySalesClient result will only ever have the result from the first client name in it.

Try moving everything you're doing with $MonthlySalesClient, including the $query_ formatting, the mysql_query() and the `mysql_fetch_assoc() call inside the outer loop. That should get you fresh monthly data for each table row of client information you're trying to construct.