Using two queries in one table

[SwanseaMick]

Being a php newbie, I am slowly getting the hang of it, and have succesfully used it with MySQL to connect to my database and retrieve data, and have correctly populated the first 4 columns of my table (see extract below, which is inside a <?php and ?> pair of tags).

What I wish to do is add data in the 5th column, containing the result of a SELECT SUM() query, using a WHERE clause based on value of ['NextInvoice'] from column 3.
e.g SELECT SUM(Amount) as Earnings FROM Bookings_tbl WHERE Invoice = 1234

There may or may not be a value returned from this query! An 'if' statement checking value of mysql_num_rows should take care of this, but I'm having trouble finding where I should place the code, and exactly how to incorporate the value returned by $row['NextInvoice']

Any help would be appreciated.

$result = mysql_query("SELECT * from currentcustomer_vue");
//Table starting tag and header cells
echo " <table style='width: 95%; text-align: left; margin-left: auto; margin-right: auto;' border='0' cellpadding='2' cellspacing='2'><tr><th>Company ID</th><th>Company Name</th><th>Next Invoice</th><th>Pay Rate</th><th>Amount Earnt to date</th></tr>";
while($row = mysql_fetch_array($result)){
//Display the results in different cells
echo "<tr>
<td>" . $row['Company_ID'] . "</td>
<td>" . $row['CompanyName'] . "</td>
<td>" . $row['NextInvoice'] . "</td>
<td>" . $row['PayRate'] . "</td>
<td> WHAT GOES IN HERE? </td>
</tr>";
}

[freshnet]

Firstly, you shouldn't be using mysql_query. Mysqli_query is more secure, although PDO is far better. You can read about these on the PHP manual pages.

If you want to put in another query, just do what you've already done, e.g.

Code: Select all

$result2 = mysql_query("SELECT SUM.....");
$resultRow = mysql_fetch_row($result2);


Then you can output the row in whichever cell you want.

[Nullsig]

mysql_query is just as secure as mysqli... you just have to not be dumb when you use it.

[freshnet]

fair point - but mysql should still not be used for new code!

[Nullsig]

My response to your other post stands.

As far as using the mysql on new code... it doesn't matter as long as the PHP build they are currently using supports it. There is no valid reason to convert to mysqli until it is necessary. Especially in the case where existing code on the server is using mysql; converting to mysqli would then create a disjunction in the code of the website. It is an all or nothing thing.