SQL join and sum

[radarman2000]

Hello all

I have 3 tables to join but only want to sum one column from one table

my tables are forms, filters, and options

the column i want to sum is in the forms table and is named element_5

the column in the filter table is filter_keyword and the column in the options table is option_id

so element_5 in the forms table is a price. I have a filter to choose what I want to see, ie, service type, truck used, date, and a few others that populate the filter table when I apply the filter. then I just want to sum the price for that filter.

Making any sense? let me know

here is the code im trying, but it doesn't work

$query = "SELECT * from form_10556
                INNER JOIN element_options
                ON form_10556.element_6 = element_options.option_id
                INNER JOIN form_filters
                ON element_options.option_id = form_filters.filter_keyword";
                  
    $params = array($element_5,$element_6,$option_id,$filter_keyword);
    
    $sth = do_query($query,$params,$dbh);
    
    while ($row = do_fetch_result($sth)) {
    $totalPrice += $row['element_5'];
    }

Edited 23 hours ago by radarman2000

[Barand]

3 hours ago, radarman2000 said:

Making any sense?

Not a lot. "It doesn't work" tells us nothing. What is ir doing that it shouldn't or what is it not doing that it should?

• If element_5 contains a price, why not call it "price". Same goes for element_6 (option_id).
• I can't see what do_query() is doing but as you're passing an array of parameters I assume you are trying to use a prepared statement - but you have no placeholders in the query string for those parameters.
• If $filter_keyword contains a column name that could be the cause - you can only pass values as parameter.
• If any of your joins match more than 1 row in any of the tables, the resultant sum would be multiplied by the number of rows.

If you need more help, a dump of the structures and data for those tables would enable me to recreate the problem at my end a get you a working query.