[SOLVED] Adding 2 results together...

[Bunny]

Hi guys,

 

I'm having a little trouble working out how to display a running total of sales that have gone through my website.  The sales figures are stored across 2 tables, which I normally access using a Union query in SQL.  I am trying to devise a means of displaying the total figure on my website as a single number formatted with comma's for thousands.

 

So far I have managed to create the code, but the output shows the total's from each table as a separate figure.  I do not know how to display these as a total figure.  My code is below.

 

$sql= "SELECT sum(price) AS total_price FROM geodesic_classifieds_expired WHERE final_price>(0.00) 
UNION ALL
SELECT SUM(price) as total_price FROM `geodesic_classifieds` WHERE`final_price`>(0.00)";
$res= mysql_query($sql);
if (mysql_num_rows($res)>0) {
  while ($row=mysql_fetch_array($res)) {
    echo '<h3>'.number_format($row["total_price"], 0).'</h3>';
  };
};
echo mysql_error();

 

Can anyone help me to join the 2 output figures together?  I would be most grateful for the help.  I have no experience with PHP at all, so I surprised myself to even get this far.

[darkfreaks]

so what does it output ???

 

and why are you using Union All for the same table ???

[Bunny]

So far the code I have made generates 2 separate figures, one below the other, formatted in thousands.  Apart from the output being 2 separate figures, it is perfect.  What I need to do is merge the 2 rows of output into one single figure but I do not know how to do that.

 

PS:  I used the Union All fuction because the 2 select queries are from 2 different tables,  they aren't the same table although they have similar names.

[darkfreaks]

have you used JOIN or INNERJOIN instead ???

[Bunny]

As far as I understand the JOIN and INNERJOIN functions, they would only be relevant if I was trying to match records in one table with records in the other.  I am not trying to do that at all, I want a mathematical total of all the sales in both tables at once.

 

All I am trying to do is to display the total sales (in whole dollars) that have gone through my website since it began.  The issue is, the sales records that contain the dollar value are spread across 2 tables, a current table and an archive table.  Therefore, to get the total life-to-date sales figure I need to add the two columns called "price" in each table.  To do this, it is normal to use a UNION function.

 

The issue I have is that PHP wishes to display the results of the UNION as 2 separate rows in the array output.  I need these 2 rows of data to be added together in the final output.  I apologise in advance if what I am saying is unclear.

[darkfreaks]

actually innerjoin returns a match on columns in 2 tables might wanna read up again.

 

http://www.w3schools.com/Sql/sql_join_inner.asp

[Bunny]

Thank you for your prompt replies to my question.

 

I have managed to find the answer by modifying the PHP area of the code to add the 2 outputs together, and it is now working perfectly.  My final code is as follows:

 

$sql = <<<END
SELECT sum(price) AS total_price FROM `geodesic_classifieds_expired` WHERE final_price>(0.00) 
UNION ALL
SELECT sum(price) AS total_price FROM `geodesic_classifieds` WHERE final_price>(0.00)
END;
$res = mysql_query($sql);
if (!$res) {
  die('Error: ' . mysql_error() . ' in query ' . $sql);
}
$total = 0;
while ($row = mysql_fetch_array($res)) {
  $total += $row[0];
}
$total = number_format($total, 0);
echo '<h3>Total Sales are $' . $total . '</h3>';

 

You may mark this question as closed.  Thank you again.

[darkfreaks]

LOL just press topic solved button lower left corner

[Bunny]

Ah I see it.  Thank you  

 

PS:  I think I've done pretty well today considering I solve my own problem and had never even heard of PHP before this afternoon (I am not a programmer of any kind).  Thank you for making me feel better about my own abilities.