SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Writing Two MySQL Sums

    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

    PHP Code:
    $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. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    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. #6
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, you can use single or double quotes for a column alias (but not a table alias)

    try it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •