Show the data based on a foreign key

Question

The code that you sent awhile ago works perfectly on my system so I also used it in another module in which the admin can view the also view the date. But I encountered problems.

This is the case, the first user who added his/her order first and even the next user who has same name and section is now being recorded and saved correctly. However, the problem is when the recording is done, it shows a duplicate value of the first order that is recorded.

  DATE   |   NAME   |   SECTION   |   PAYABLE   |   PRODUCT   |   QUANTITY
10/04/18 |   User1  |  Section 1  |     990     |  Magic Mug  |      3
                                                |   T-shirt   |      3
10/04/18 |   User1  |  Section 1  |     630     |  Magic Mug  |      3
                                                |   Thumbler  |      2
10/04/18 |   User1  |  Section 1  |     990     |  Magic Mug  |      3
                                                |   T-shirt   |      3

This is the PHP code I used in which I only added date to your code.

$last_date = NULL;
$last_name = NULL;
$last_section = NULL;
$last_payable = NULL;

while ($row = mysql_fetch_array($result)) {
    $date = "";
    $name = "";
    $section = "";
    $payable = "";
    if ($last_name === NULL || $last_date != $row['date'] ||
        $last_name != $row['name'] ||
        $last_section != $row['section'] ||
        $last_payable != $row['payable']) {

        $last_date = $row['date'];
        $last_name = $row['name'];
        $last_section = $row['section'];
        $last_payable = $row['payable'];
        $date = $row['date'];
        $name = $row['name'];
        $section = $row['section'];
        $payable = $row['payable'];
    }
    echo '<tr style="text-align:center;">';
    echo '<td>'.$date.'</td>';
    echo '<td>'.$name.'</td>';
    echo '<td>'.$section.'</td>';
    echo '<td>'.$payable.'</td>';
    echo '<td>'.$row['product'].'</td>';
    echo '<td>'.$row['qty'].'</td>';
    echo '</tr>';
}

Duplicate : https://stackoverflow.com/questions/52639451/remove-repeating-data-in-a-column/52640190#52640190 — Ashu, Oct 05 '18 at 04:50
Possible duplicate of [Remove repeating data in a column](https://stackoverflow.com/questions/52639451/remove-repeating-data-in-a-column) — Keval Mehta, Oct 05 '18 at 04:50
The columns/data names in your expected output table do not line up with the PHP code. I'm going to request that you fix your question so everything is in agreement. — Tim Biegeleisen, Oct 05 '18 at 04:55

Tim Biegeleisen · Accepted Answer · 2018-10-05T06:38:01.900

1

Your expected output suggests that we should display the name, section, and payable values once, when one of these three values change. So we can slightly modify the answer given to your previous question, to maintain state for these three columns while iterating the result set.

$last_date = NULL;
$last_name = NULL;
$last_section = NULL;
$last_payable = NULL;

while ($row = mysql_fetch_array($result)) {
    $date = "";
    $name = "";
    $section = "";
    $payable = "";
    if ($last_name === NULL ||
        $last_date != $row['date'] ||
        $last_name != $row['name'] ||
        $last_section != $row['section'] ||
        $last_payable != $row['payable']) {

        $last_date = $row['date'];
        $last_name = $row['name'];
        $last_section = $row['section'];
        $last_payable = $row['payable'];
        $date = $row['date'];
        $name = $row['name'];
        $section = $row['section'];
        $payable = $row['payable'];
    }
    echo '<tr style="text-align:center;">';
    echo '<td>'.$date.'</td>';
    echo '<td>'.$name.'</td>';
    echo '<td>'.$section.'</td>';
    echo '<td>'.$payable.'</td>';
    echo '<td>'.$row['product'].'</td>';
    echo '<td>'.$row['qty'].'</td>';
    echo '</tr>';
}

edited Oct 05 '18 at 06:38

answered Oct 05 '18 at 05:25

Tim Biegeleisen

502,043
27
286
360

Hi Sir @Tim. I used this code and again it worked perfectly. However, I encountered a problem using this in one of my module which has an additional column. I posted the question. Hoping that you can help me again this time. Thank you. – Benj Oct 05 '18 at 06:30
Problem still persists Sir @Tim. The first data added is duplicated. – Benj Oct 05 '18 at 06:43
Your expected output implies that a new group begins whenever one of the date, name, section, or payable columns has a new value. My answer should be covering this. I can't adjust my answer, because I don't see any data for which it should be failing. – Tim Biegeleisen Oct 05 '18 at 06:44
Your answer is now working Sir @Tim. I don't know what is with the system a while ago. Thank you! – Benj Oct 05 '18 at 07:09
Hi Sir @Tim is there any way to only get the data from now up to the past 5 days? What query should I use? Thank you. – Benj Oct 09 '18 at 13:07
@Benj [See here](https://stackoverflow.com/questions/24272335/mysql-select-last-7-days). If still unclear, then ask a new question. – Tim Biegeleisen Oct 09 '18 at 14:12
That helped a lot. Thanks again Sir @Tim! – Benj Oct 10 '18 at 00:09
Hi Sir @Tim is it possible to disable a website after a certain time of a day. For example. The website will show "The deadline has passed." when it is 9am. Is there any way to do that? – Benj Oct 11 '18 at 07:23

score 0 · Answer 2 · answered Oct 05 '18 at 05:08

Issue is not in your query. I checked issue is in your logic, which you put in while loop to print values. There may be cases where user firstname would be same for multiple users. So you can use confirmation, which will be unique for every order.

$result = mysql_query("
    SELECT reservation.firstname, reservation.lastname, reservation.payable, reservation.floor, reservation.section, orders.product, orders.qty, reservation.date, orders.confirmation
    FROM orders
    INNER JOIN reservation
    ON orders.confirmation = reservation.confirmation 
    WHERE reservation.date = CURDATE() && reservation.floor = '23rd Floor'
    ");

$last_confirmation_seen = NULL;

while ($row = mysql_fetch_array($result)) {
    $firstname = "";
    $lastname = "";
    $section = "";
    $payable = "";
    if ($last_confirmation_seen === NULL || $row['confirmation'] != $last_confirmation_seen) {
        $last_confirmation_seen = $row['confirmation'];
        $firstname = $row['firstname'];
        $lastname = $row['lastname'];
        $section = $row['section'];
        $payable = $row['payable'];
    }
    echo '<tr style="text-align:center;">';
    echo '<td>'.$firstname.' '.$lastname.'</td>';
    echo '<td>'.$section.'</td>';
    echo '<td>'.$payable.'</td>';
    echo '<td>'.$row['product'].'</td>';
    echo '<td>'.$row['qty'].'</td>';
    echo '</tr>';
}

Try this I think this will help you.

Show the data based on a foreign key

2 Answers2