Data not properly displayed in HTML table by using while loops to fetch data from MySQL database

General PHP Help
#1

Hello to all,

I have problem figuring out how to properly display data fetched from MySQL database in a HTML table.

In the below example I am using two while loops, where the second one is nested inside first one, that check two different expressions fetching data from tables found in a MySQL database. The second expression compares the two tables IDs and after their match it displays the email of the account holder in each column in the HTML table.

The main problem is that the ‘email’ row is displayed properly while its while expression is not nested and alone(meaning the other data is omitted or commented out), but either nested or neighbored to the first while loop, it is displayed horizontally and the other data (‘validity’, ‘valid_from’, ‘valid_to’) is not displayed.’

Can someone help me on this, I guess the problem lies in the while loop?
Here is part of the HTML code:

<thead>
    <tr>
        <th data-column-id="id" data-type="numeric">ID</th>
        <th data-column-id="email">Subscriber's Email</th>
        <th data-column-id="validity">Validity</th>
        <th data-column-id="valid_from">Valid From</th>
        <th data-column-id="valid_to">Valid To</th>
    </tr>
</thead>

Here is part of the PHP code:

<?php
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    echo '
        <tr>
            <td>'.$row["id"].'</td>
    ';
    while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
    echo '
            <td>'.$row1["email"].'</td>
    ';
    }
    if($row["validity"] == 1) {
        echo '<td>'.$row["validity"].' month</td>';
    }else{
        echo '<td>'.$row["validity"].' months</td>';
    }
    echo '  <td>'.$row["valid_from"].'</td>
            <td>'.$row["valid_to"].'</td>
    </tr>';
}
?>

P.S. Please know that I have purposely omitted the connection part and the methods in the class that are used in this code to keep the spot of the problem as clean as possible. If you need me to rephrase the question, please ask.

Thank you.

#2

var_dump() out:

$row
&
$row1

So you can see the formatting/layout…

#3

This is bad. You have a problem somewhere.

#4

To query for related data, use a single JOIN query, then simply loop over the fetched data to produce the output that you want.

You should also remove the database specific code from the html document, by simply fetching all the data from the query into an appropriately named php variable, then test/loop over this variable at the correct place in the html document.

#5

The query for the ‘email’ row I have set is:

SELECT users.email FROM users INNER JOIN user_subscr ON users.id = user_subscr.user_id ORDER BY user_id ASC

The query for the rest data is:

SELECT * FROM user_subscr ORDER BY id ASC

Both are in separate methods in a class. Should I join both queries they are querying data from the two tables - ‘users’ and ‘user_subscr’ in one method?
If that is the case, it would be nice if you provide me with some example on how to do this.

Are you pointing for the use of foreach loop in this situation?

Thanks

#6

Well, I am aware that I have problem somewhere. But where, can you show the way? Thanks.

#7

Thanks, but I see no problem at all while dumping the var values, unless you could point on what to look for.

#8

Share the outputs for both.

If the date is fine… then you are not accessing it correctly… but how can we know? YOU ARENT SHARING IT.

#9

Not with the limited code you have provided. @phdr has pointed you in the right direction though. If you really want the best help, put your app up on Github so we can review it as a whole.

#10

Given that you produced an existing JOIN query (that you would only need to add some SELECTed columns to), I’m not sure why are even asking (this is something of a rhetorical question, as you probably reached this point through copy/pasting rather than actually learning the meaning of what you are doing)?

#11

I’ve solved the problem and didn’t realize for the whole time that I had to be thinking as simple as possible. Here is the solution:
SELECT * FROM users, user_subscr WHERE users.id = user_subscr.user_id

@phdr, assumptions in this way might have negative impact upon all those who want to be a part of the PHP school, and without giving support for example, this will make them run away from the more experienced, like you are, I guess?
Even you are copy/pasting code lines once in a while. But giving solutions is a different task.

#12

No it isn’t. You need to use a JOIN.

#13

At first I have found the solution with JOIN clause but after a while wanted to find even shorter solution to my problem that is why I pasted the latter here.

comparing this:
SELECT email, user_subscr.* FROM users INNER JOIN user_subscr ON users.id = user_subscr.user_id

with this:
SELECT * FROM users, user_subscr WHERE users.id = user_subscr.user_id,

both are working very well and I don’t see any reason why I strictly need to use the INNER JOIN clause except that INNER JOIN defines relationship between tables by matching a row value in one table with a row value in other table(s) allowing querying of row values containing columns from both tables and WHERE specifies a condition to search for rows returned by a query.

So this means INNER JOIN is more socially acceptable or a way of standard thinking than WHERE?

Somewhere I’ve even read that JOIN and WHERE are the same thing.

Any suggestion on this?

#14

Yes. What we are talking about here is implicit joins vs explicit joins.

Rather than explaining, I have something for you to do. Download a large data set (100k records or more) and try both types of joins using several tables at once and tell us what happens.

Sponsor our Newsletter | Privacy Policy | Terms of Service