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 January 9 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.

[radarman2000]

Here are the tables in sql dump

The form is a generated form and the price column was given the name element_5 (not by me, but by the form generator)

I have also included the page code to view entries and where I want the totals to show based on price (element_5)

ap_element_options.sql.txt ap_form_10556.sql.txt ap_form_elements.sql.txt ap_form_filters.sql.txt manage_entries.php.txt

[Barand]

The last 2 lines of your query are ...

On 1/9/2025 at 2:34 PM, radarman2000 said:

 INNER JOIN form_filters
                ON element_options.option_id = form_filters.filter_keyword

where you are joining options and filters tables.

Your option_id column contains values between 1 and 12.

select * from ap_element_options LIMIT 15;
+--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+
| aeo_id | form_id | element_id | option_id | position | option        | option_is_default | option_is_hidden | live |
+--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+
|    431 |   10556 |          1 |         4 |        1 | MCSO          |                 0 |                0 |    1 |
|    432 |   10556 |          1 |         5 |        2 | CCSO          |                 0 |                0 |    1 |
|    433 |   10556 |          1 |         6 |        3 | LCSO          |                 0 |                0 |    1 |
|    434 |   10556 |          1 |         7 |        4 | DPD           |                 0 |                0 |    1 |
|    435 |   10556 |          1 |         8 |        5 | FPHM          |                 0 |                0 |    1 |
|    436 |   10556 |          1 |         9 |        6 | FHPC          |                 0 |                0 |    1 |
|    437 |   10556 |          1 |        10 |        7 | FHPL          |                 0 |                0 |    1 |
|    438 |   10556 |          1 |        11 |        8 | AUTO CLUB     |                 0 |                0 |    1 |
|    439 |   10556 |          1 |        12 |        9 | OWNER         |                 0 |                0 |    1 |
|    446 |   10556 |          2 |         4 |        1 | F550          |                 0 |                0 |    1 |
|    447 |   10556 |          2 |         5 |        2 | F350          |                 0 |                0 |    1 |
|    448 |   10556 |          2 |         6 |        3 | International |                 0 |                0 |    1 |
|    449 |   10556 |          2 |         7 |        4 | Ranger        |                 0 |                0 |    1 |
|    453 |   10556 |          4 |         1 |        1 | Credit Card   |                 0 |                0 |    1 |
|    454 |   10556 |          4 |         2 |        2 | Cash          |                 0 |                0 |    1 |
+--------+---------+------------+-----------+----------+---------------+-------------------+------------------+------+

Which of these values are you expecting to match the filter_keyword Tow ?

select * from ap_form_filters;
+--------+---------+---------+--------------------+--------------+------------------+----------------+
| aff_id | form_id | user_id | incomplete_entries | element_name | filter_condition | filter_keyword |
+--------+---------+---------+--------------------+--------------+------------------+----------------+
|    312 |   10556 |       3 |                  0 | element_6    | is               | Tow            |
+--------+---------+---------+--------------------+--------------+------------------+----------------+

I suggest you rethink your design and come back when you have something that could work.