GROUP by inside INNER join with displaying in html drop down list using php

Question

I have the following tables:

Workers (id, total_pay, date_of_pay, projects_id)
Payments (id, payment, date, projects_id)
building_materials(id, pay_of_materials, date, projects_id)
additional(id, add_pay, date, projects_id)

Where projects_id is a foreign key for all of them. I tried to make a query to select all dates from all tables where projects_id = projects_id, I have this statement now:

SELECT workers.date_of_pay, payments.date, building_materials.date, additional.date FROM
workers 
INNER JOIN 
payments ON workers.projects_id = payments.projects_id
INNER JOIN 
building_materials ON payments.projects_id = building_materials.projects_id
INNER JOIN 
additional ON building_materials.projects_id = additional.projects_id;

What exactly I want, and can't figure it out, is how to use GROUP BY to group by every field, and how to display all columns into one, in one drop down list box ? I have this list box code:

<select name="Date" required class="form-control" id="Date">
                    <option value="">اختر التاريخ</option>
                    <?php $sql="    SELECT workers.date_of_pay, payments.date, building_materials.date, additional.date FROM
    workers 
    INNER JOIN 
    payments ON workers.projects_id = payments.projects_id
    INNER JOIN 
    building_materials ON payments.projects_id = building_materials.projects_id
    INNER JOIN 
    additional 

ON 
building_materials.projects_id = additional.projects_id AND additional.projects_id = ".$id;
                $result = mysqli_query($con, $sql) or die($sql."<br/><br/>".mysql_error());

                    while($rows=mysqli_fetch_array($result)){?>
                        <option value="<?php echo $rows['date'] ?>"><?php echo $rows['date'] ?></option>
                    <?php } ?>
                    </select>

So, later, I can select a date from drop list and get the info. So how I can group by, and echo result into drop down list.

EDIT After using concat():

SELECT concat(workers.date_of_pay, payments.date, building_materials.date, additional.date) AS date FROM
    workers 
    INNER JOIN 
    payments ON workers.projects_id = payments.projects_id
    INNER JOIN 
    building_materials ON payments.projects_id = building_materials.projects_id
    INNER JOIN 
    additional ON building_materials.projects_id = additional.projects_id

I have got this:

But I want only one date in each row.

you have to concatenate different columns into one to show in one drop down list. — PK20, Nov 27 '15 at 07:41
you can use CONCAT() function to concatenate all the columns into one. Since you don't use any aggregate functions, you can go for ORDER BY to sort results in specific order instead of GROUP BY. — PK20, Nov 27 '15 at 07:45
Sir see my edit, concat put result of rows together, what I want is that each date form a row. See edited question — am90, Nov 27 '15 at 07:47

score 1 · Accepted Answer · answered Nov 27 '15 at 07:49

1

Use UNION ALL and GROUP BY like the following. With the available code I can write the solution like this. You can reduce the join depends upon the table schema. And also try to use mysqli instead of mysql.

$sql = "SELECT * FROM (
       (SELECT workers.date_of_pay AS real_date FROM
            workers 
            INNER JOIN 
            payments ON workers.projects_id = payments.projects_id
            INNER JOIN 
            building_materials ON payments.projects_id = building_materials.projects_id
            INNER JOIN 
            additional 
            ON building_materials.projects_id = additional.projects_id AND additional.projects_id = $id)"
        . " UNION ALL"
        ."(SELECT payments.date AS real_date FROM
            workers 
            INNER JOIN 
            payments ON workers.projects_id = payments.projects_id
            INNER JOIN 
            building_materials ON payments.projects_id = building_materials.projects_id
            INNER JOIN 
            additional 
            ON building_materials.projects_id = additional.projects_id AND additional.projects_id = $id)"
        . " UNION ALL"
        . "(SELECT building_materials.date AS real_date FROM
            workers 
            INNER JOIN 
            payments ON workers.projects_id = payments.projects_id
            INNER JOIN 
            building_materials ON payments.projects_id = building_materials.projects_id
            INNER JOIN 
            additional 
            ON building_materials.projects_id = additional.projects_id AND additional.projects_id = $id)"
        . " UNION ALL"
        . "(SELECT additional.date AS real_date FROM
            workers 
            INNER JOIN 
            payments ON workers.projects_id = payments.projects_id
            INNER JOIN 
            building_materials ON payments.projects_id = building_materials.projects_id
            INNER JOIN 
            additional 
            ON building_materials.projects_id = additional.projects_id AND additional.projects_id = $id)"
        . ") AS tab GROUP BY real_date";

answered Nov 27 '15 at 07:49

Sanjay Kumar N S

4,653
4
23
38

Oh thanks, It works, But sir, how to use it in mysqli ? Can you help by adding mysqli codes ? – am90 Nov 27 '15 at 07:53
OR pdo, because for insert. delete and update I am using PDO codes, but when showing some data in html table i am using mysql or mysqli – am90 Nov 27 '15 at 07:54
You can have a look at migrating from mysql to mysqli :http://stackoverflow.com/questions/1390607/how-could-i-change-this-mysql-to-mysqli – Sanjay Kumar N S Nov 27 '15 at 08:04
Sir, I have a problem with your code, when a project have an empty field of date, it will not show anything in the drop list ???!!!! – am90 Nov 27 '15 at 08:30
I mean, if workers table do not contain a date for a specific projects_id number, nothing is shown, unless we have at least one date in each table for this project_id number – am90 Nov 27 '15 at 08:32
1

Since you used INNER JOIN, it won't be taking if any of the table doesn't have entry. Use LEFT JOIN for such tables – Sanjay Kumar N S Nov 27 '15 at 09:28
I used it, now the list box shows me the same dates (Grouped) whatever the $id value is ????! – am90 Nov 27 '15 at 09:49
[link](http://stackoverflow.com/questions/33953406/drop-down-list-dont-show-dates-if-at-least-one-table-contain-empty-date-field/33953500?noredirect=1#comment55666427_33953500) – am90 Nov 27 '15 at 09:50
I used it, now the list box shows me the same dates (Grouped) whatever the $id value is ????! – am90 Nov 27 '15 at 09:50
The order of selecting tables also you may have to change. First you are selecting from workers table, where itself returns NULL. You can join the optional tables at last . – Sanjay Kumar N S Nov 27 '15 at 09:50
How ? Can you change some line to see how should I do it, it 2 hours and still can't find an answer – am90 Nov 27 '15 at 10:47
I hope this will work: SELECT workers.date_of_pay AS real_date FROM additional INNER JOIN building_materials ON building_materials.projects_id = additional.projects_id AND additional.projects_id = $id INNER JOIN payments ON payments.projects_id = building_materials.projects_id LEFT JOIN workers ON workers.projects_id = payments.projects_id; – Sanjay Kumar N S Nov 27 '15 at 12:13

GROUP by inside INNER join with displaying in html drop down list using php

1 Answers1