Join or Union in 5 tables on DATE clause

178
April 28, 2018, at 9:06 PM

My client have a database and inside have 5 tables, right now without relationship, just the date in each table. I know that these will be maybe impossible but my client need a report with the info in this tables. He need join the row profession that right now are in 4 of 5 tables with the amount and userId in the last one, all these with between dates. So I tried INNER JOIN ed all tables and in ON clause make the relation with the date row.

Here are the TABLES:

---DATES---
userId ---- Int
username -- varchar 150
amount ---- Float(5,2)
date ------ DATETIME

--- TABLE1, TABLE2, TABLE3, TABLE4 --- (same rows for different parts in the program)(have more rows than those)
profession --- varchar 100
date -------- DATETIME
userId ------ Int

I made this type of INNER JOIN, but right now, not shown any respond, without any error in console. so I don't know if there is another form to do this for show me the info that the client need:

<?php
$fromdate = filter_var($_POST["fromdate"], FILTER_SANITIZE_STRING);
$todate = filter_var($_POST["todate"], FILTER_SANITIZE_STRING);
 try{
$sql = $conn->prepare("SELECT DATE_FORMAT(C.date, '%d de %M de %Y') AS date,
C.userId, C.username, C.amount, (D.profession) AS diag1, (DN.profession) AS diag2, 
(X.professions) AS diag3, (DJ.profession) AS diag4 FROM DATES C,
INNER JOIN DIAGNOSTICO D
INNER JOIN DIAGNOSTICON DN
INNER JOIN CONSULTA X
INNER JOIN DIAGNOSTICOJUR DJ
ON DATE_FORMAT(C.date, '%Y-%m-%d') = DATE_FORMAT(D.date, '%Y-%m-%d')
AND DATE_FORMAT(C.date, '%Y-%m-%d') = DATE_FORMAT(DN.date, '%Y-%m-%d')
AND DATE_FORMAT(C.date, '%Y-%m-%d') = DATE_FORMAT(X.date, '%Y-%m-%d')
AND DATE_FORMAT(C.date, '%Y-%m-%d') = DATE_FORMAT(DJ.date, '%Y-%m-%d')
WHERE C.date BETWEEN '".$fromdate."' AND '".$todate."' ORDER BY C.date ASC");
$sql->execute();
while($row = $sql->fetch(PDO::FETCH_ASSOC)) {
?>
    <tr>
        <td><?php echo $row["date"]; ?></td>
        <td class="center"><?php echo $row["userId"]; ?></td>
        <td class="center"><?php echo $row["username"]; ?></td>
        <td class="center"><?php echo $row["amount"]; ?></td>
        <td class="center">
            <?php echo $row["diag1"].''.$row["diag2"].''.$row["diag3"].''.$row["diag4"]; ?>'
        </td> //
    </tr>
<?php } ?>

I tried with the date row because the client save only once the amount in the DATES table. That same day he (the client), save in the profession row only once , in which table? that depends on what the client has done to the user.(The program have multiple forms)

Answer 1

Use LEFT JOIN rather than INNER JOIN, so you will get rows for the date even if they don't have a match in one of the other tables. You'll need to use a separate ON clause for each join, to specify that specific relationship. If one of the tables is missing data for a specific date, you'll get NULL for the columns from that table.

Since you're using PDO, you should use bindParam() rather than substituting variables into the query.

You also had an extra , after FROM DATES C, causing a syntax error.

And use DATE(columnname) rather than DATE_FORMAT().

$sql = $conn->prepare("SELECT DATE_FORMAT(C.date, '%d de %M de %Y') AS date,
C.userId, C.username, C.amount, (D.profession) AS diag1, (DN.profession) AS diag2, 
(X.professions) AS diag3, (DJ.profession) AS diag4 
FROM DATES C
LEFT JOIN DIAGNOSTICO D ON DATE(C.date) = DATE(D.date)
LEFT JOIN DIAGNOSTICON DN ON DATE(C.date) = DATE(DN.date)
LEFT JOIN CONSULTA X ON DATE(C.date) = DATE(X.date)
LEFT JOIN DIAGNOSTICOJUR DJ ON DATE(C.date) = DATE(DJ.date)
WHERE C.date BETWEEN :fromdate AND :todate ORDER BY C.date ASC");
$sql->bindParam(':fromdate', $fromdate);
$sql->bindParam(':todate', $todate);
Rent Charter Buses Company
READ ALSO
Search Using id and name from two different table

Search Using id and name from two different table

I want to search product as per user inserted keywordCurrently i am doing by explode user keyword and by using SQL LIKE

242
How to connect php with CloudSQL

How to connect php with CloudSQL

I tried to connect cloud-sql like below, but it doesn't workCan anyone help me how to connect with Cloud sql using php ?

275
Call to a member function insert() on string

Call to a member function insert() on string

I want to add a watermark to my uploaded image using intervention image library in laravelI have installed it through command composer require intervention/image , added Intervention\Image\ImageServiceProvider::class in providers array and 'Image' => Intervention\Image\Facades\Image::class...

221