PHP PDO foreach loop gives extra table output

Question

Hope someone can solve this.

I have the following MySQL query:

$sql = $db->prepare("SELECT 
                dienaren.id,
                dienaren.achternaam_minister,
                dienaren.email_minister,
                dienaren.gemeente,
                users.achternaam,
                users.email,
                users.periode_van,
                users.periode_tot
                FROM dienaren 
                LEFT JOIN users 
                ON dienaren.id = users.minister_id
                WHERE users.periode_tot < DATE_ADD(CURDATE(), INTERVAL + 31 DAY)
                ORDER BY dienaren.id,users.periode_tot ASC");
$sql->execute();

This is giving me the result of 29 rows (which is correct as I checked)

Now I want to use a kind of loop to show a table where person A has x rows that correspond with the query. The same counts for person B, person C etc.

Here is screenshot with the current output:

screenshot

As you can see there are 2 records that are true for this person. But it is showing two lists instead of one total list.

Here is my code that produces this table:

<?php
$count = $sql->rowCount();
echo '<p>Total result SQL query: '.$count.'</p>';
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Document</title>
</head>
<body>

<?php foreach($sql->fetchAll(PDO::FETCH_OBJ) as $voorganger): ?>

    <div><strong>Person ID: <?= $voorganger->id;?></strong></div>
    <div>Is in control of the following users:</div>

    <table>
      <tr>
        <th>Voorletter(s)</th>
        <th>Periode Tot</th>
        <th>Status</th>
      </tr>

    <?php
    $query = $db->prepare("SELECT * FROM users WHERE minister_id = '{$voorganger->id}' AND periode_tot < DATE_ADD(CURDATE(), INTERVAL + 31 DAY) ORDER BY minister_id ASC");
    $query->execute();

    foreach($query->fetchAll(PDO::FETCH_OBJ) as $user) : ?>

        <?php $date = new DateTime($user->periode_tot); ?>

        <tr style="background:rgba(244, 65, 55, 0.35);">
            <td><?= $user->voorletters; ?></td>
            <td><?= $date->format('d-m-Y'); ?></td>

            <?php if (dateDifference($user->periode_tot,$currentdate) > 0 && dateDifference($user->periode_tot,$currentdate) <= 30) { ?>
                <td>Verloopt over&nbsp;<?= dateDifference($user->periode_tot,$currentdate); ?>&nbsp;dagen</td>
            <?php } else { ?>
                <td>Verlopen</td>
            <?php } ?>
        </tr>

    <?php endforeach; ?>

    </table>
    <hr>

<?php endforeach; ?>

</body>
</html>

Hope this is clear to the one to wants to help me. If not just let me know to explain more. (Btw: I changed some outcome in the table because it holds actual data which it's private)

Thanks in advance.

EDIT:

Just pointing out that in the database I have a dienaren table that has many relationship on T2 (holding users with a field called minister_id)

Looping through the users table having eg. 5 results there is a table created 5 times with all the 5 results (zie screenshot 2). What I want instead is just ONE table that holds those 5 results. I think I am not using the right loop function or I am not using it right anyway.

SECOND EDIT:

I took a different approach which got me near to my solution. I changed from a JOIN query to SELECT * FROM T1. Only downside is that it also show all the records that are having 0 results. So how can I prefend the creation of a table from the person from T1 that has no results from the query in T2 ?

(In this case ID:861 holds 5 records, but ID:862 has no records but is also has a table which is empty. I want to get rid of tables that are empty)

Can you print first one query, and look the how many person id are print, it's print 861 one time or many time — Imran Saleem
– Imran Saleem, Commented Jan 4, 2017 at 6:25
@ImranSaleem I took a different approach that worked out better than my first one. I will update my Q — Brnovich
– Brnovich, Commented Jan 4, 2017 at 7:07

Pang · Accepted Answer · 2018-11-01 01:40:49Z

Got it working:

foreach($sql->fetchAll(PDO::FETCH_OBJ) as $person):

$person_id = $person->id;

$query = $db->prepare("SELECT * FROM users WHERE person_id = '{$person_id}' AND periode_tot < DATE_ADD(CURDATE(), INTERVAL + 31 DAY)");
$query->execute();

$total = $query->rowCount();

if ($total > 0) {
?>
    <div><?= $person_id; ?></div>
    <div><?= $person->achternaam_person; ?></div>
    <div><?= $person->gemeente; ?></div>

    <table>
      <tr>
        <th>Gemeente</th>
        <th>Achternaam(s)</th>
        <th>Voorletter(s)</th>
        <th>Periode Tot</th>
        <th>Status</th>
        <th>Email</th>
      </tr>
<?php   
    while ($user = $query->fetch(PDO::FETCH_OBJ)) {
        $date = new DateTime($user->periode_tot);
?>
    <tr style="background:rgba(244, 65, 55, 0.35);">
        <td><?= $user->gemeente; ?></td>
        <td><?= $user->achternaam; ?></td>
        <td><?= $user->voorletters; ?></td>
        <td><?= $date->format('d-m-Y'); ?></td>
        <td>Verloopt over&nbsp;<?= dateDifference($user->periode_tot,$currentdate); ?>&nbsp;dagen</td>
        <td><?= $user->email; ?></td>
    </tr>

<?php } } ?>

    </table>

<?php endforeach; ?>

Uberswe · Accepted Answer · 2017-01-03 21:44:18Z

0

You could use GROUP BY dienaren.id as it seems you dont want the id to repeat in your first query.

$sql = $db->prepare("SELECT 
                dienaren.id,
                dienaren.achternaam_minister,
                dienaren.email_minister,
                dienaren.gemeente,
                users.achternaam,
                users.email,
                users.periode_van,
                users.periode_tot
                FROM dienaren 
                LEFT JOIN users 
                ON dienaren.id = users.minister_id
                WHERE users.periode_tot < DATE_ADD(CURDATE(), INTERVAL + 31 DAY)
                GROUP BY dienaren.id
                ORDER BY dienaren.id,users.periode_tot ASC");
$sql->execute();

It seems like you are selecting a lot of things in your first query which are unused, you could remove some things or maybe edit your php code to only utilize the first query with the left join since it could give you all the info you need I believe.

answered Jan 3, 2017 at 21:44

Uberswe

1,0782 gold badges18 silver badges37 bronze badges

2 Comments

Brnovich Over a year ago

Your answer is not what I am looking for. This wil only return a list of dienaren and only with 1 record. I have 2 tables. T1 and T2. The relationship is that the ID field of T1 has many records of T2 which holds the minister_id field. I will edit my question and will add another screenshot which will hopefully explains my Q better.

Uberswe Over a year ago

I still don't understand exactly what you were asking I guess. It looked like you were selecting all the T2 table rows in your second query. I was trying to get rid of the duplicates from T1 which would have given you one list for each person. Maybe showing the table structure would have made it easier to answer. I am glad you solved it and thanks for the feedback, update and for sharing your own solution.

Collectives™ on Stack Overflow

PHP PDO foreach loop gives extra table output

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related