Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Using two queries in one table

Codes here !

Moderators: macek, egami, gesf

Using two queries in one table

Postby SwanseaMick » Mon Aug 20, 2012 1:40 am

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>";
}
SwanseaMick
New php-forum User
New php-forum User
 
Posts: 1
Joined: Mon Aug 20, 2012 1:08 am

Re: Using two queries in one table

Postby freshnet » Mon Aug 20, 2012 4:05 am

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.
User avatar
freshnet
php-forum Active User
php-forum Active User
 
Posts: 252
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

Re: Using two queries in one table

Postby Nullsig » Mon Aug 20, 2012 5:59 am

mysql_query is just as secure as mysqli... you just have to not be dumb when you use it.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Using two queries in one table

Postby freshnet » Fri Aug 24, 2012 3:17 am

fair point - but mysql should still not be used for new code!
User avatar
freshnet
php-forum Active User
php-forum Active User
 
Posts: 252
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

Re: Using two queries in one table

Postby Nullsig » Fri Aug 24, 2012 8:10 am

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.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 0 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.