Writing Two MySQL Sums

Databases
1

Hi all, I’m trying to write a mysql_query containing two SUMs, though I can’t seem to get it to work. I’m sure it’s something very simple. Any help and advice would be greatly appreciated. Cheers guys :slight_smile:

$query = mysql_query("SELECT SUM(sub_total) AS 'block', SUM(vat) AS 'vat' FROM order_products WHERE receipt_id='$_GET[ref]'");
		$query = mysql_fetch_array($query);
2

Stop putting user data directly into SQL queries! Please.
This isn’t directed just at you coxdadb, but it’s dangerous, and after years it’s still so common for people posting to be making this mistake. (using $_GET or $_POST in a query. See SQL Injection).

Can I ask was there some particular site or tutorials you’ve followed that neglected to touch on this point?

Anyway, back on topic I don’t see a problem with your SQL (apart from above!). What is happening when you say it doesn’t work?

Try echoing the query to debug. Is $_GET[‘ref’] what you expect?

3

Hey cranial-bore, I completely agree with you. I have a nasty habit of securing my code once I finished each script. I really must break the habit, it’s bad practice. No bad tutorials (I think :)), just me being a little special. I don’t have access to my code right now but will take a closer examination at things in the morning!

Thank you for the helpful pointers and advice.

4

SELECT SUM(sub_total) AS ‘block’
is assigning the sum to a string
SELECT SUM(sub_total) AS block
would be assigning an alias name

check that isn’t the problem in both cases. Remember don’t wrap column names or column aliases in single quotes, when you do you change them to a string which isn’t what you expected.

make sure too you use mysql_error() trapping in your script.

5

Hey dude, don’t quite understand what you’re saying (apologies, still learning!). My code is below. If I remove , SUM(vat) AS ‘vat’ from the query everything works perfectly but I need to calculate the VAT but it doesn’t appear to be working - I have already calculated the VAT before it enters the database, I’m just adding all the rows up.

function dupSub(){	
	$query = mysql_query("SELECT SUM(sub_total) AS 'block', SUM(vat) AS 'vat' FROM order_products WHERE receipt_id='$_GET[ref]'");
		$query = mysql_fetch_array($query);
			$delivery = mysql_query("SELECT * FROM orders WHERE receipt_id='$_GET[ref]'");
				$delivery = mysql_fetch_array($delivery);
					//Get Grand Total Result
					$tots = number_format($query['block']-$delivery['part_exchange']+$delivery['delivery_costs'],2);
			
	echo 'Order Subtotal &pound;'.number_format($query['block'],2).'<br/>
	Delivery &pound;'.$delivery['delivery_costs'].'<br/>';
		if ($delivery['part_exchange']>0.00){
			echo 'Part Exchange Value: &pound;'.$delivery['part_exchange'].'<br/>
			<b>Grand Total: &pound;'.$tots.'</b><br/>
			<b>VAT Amount:</b> &pound;'.$query['vat'].'
			';
		
		}
		else {
		echo '<b>Grand Total: &pound;'.$tots.'</b><br/>
		<b>VAT Amount:</b> &pound;'.$query['vat'].'
		';
		}
}

** I know I need to secure the queries instead of using $_GET **

6

I’ve been really special! I’ve been working on a duplicate database instead of the ‘live’ one and I didn’t create the column ‘vat’ on the duplicate, sorry guys for taking your time. Have a good day :slight_smile:

7

When you put single quotes around a column name or an alias to that column name you are no longer referring to the column or the alias you’ve assigned. Something with quote marks around it turns it into a string.

Booktitles - this is a column name
‘Booktitles’ - this is no longer referring to the column but a string of text

8

actually, you can use single or double quotes for a column alias (but not a table alias)

try it :wink: