Multiple query data into single HTML Table (PHP, MySQL)

Question

Here is my current code. I'm trying to display data from three separate queries into a single table with multiple columns. Is my while statement wrong here? It's printing 1 table data, then the one after, instead of next to it in the same row.

echo "<table border='1'>
<tr>
<TH COLSPAN=2>July 2010</TH>
<TH COLSPAN=2>August 2010</TH>
<TH COLSPAN=2>September 2010</TH>
</tr>
<tr>
<th>User</th>
<th>Posts</th>
<th>User</th>
<th>Posts</th>
<th>User</th>
<th>Posts</th>
</tr>";

while (($row = mysql_fetch_assoc($july)) || ($row2 = mysql_fetch_assoc($aug)) || ($row3 = mysql_fetch_assoc($sept))) {
echo "<tr>";
echo "<td>" . $row['cUsername'] . "</td>";
echo "<td>" . $row['postCount'] . "</td>";
echo "<td>" . $row2['cUsername'] . "</td>";
echo "<td>" . $row2['postCount'] . "</td>";
echo "<td>" . $row3['cUsername'] . "</td>";
echo "<td>" . $row3['postCount'] . "</td>";
echo "</tr>";
}

echo "</table>";

Tell more about what you wanna to do exactly, because I think you are doing it wrong. — good_evening
– good_evening, Commented Aug 31, 2010 at 22:00
I'm trying to post data from 3 separate sql queries into a single html table. — BigMike
– BigMike, Commented Aug 31, 2010 at 22:12

RobertPitt · Accepted Answer · 2010-09-01 00:36:26Z

$data = array();

while($row = mysql_fetch_assoc($july)) {$data['row'][] = $row;}
while($row = mysql_fetch_assoc($aug))  {$data['row2'][] = $row;}
while($row = mysql_fetch_assoc($sept)) {$data['row3'][] = $row;}

$count = count($data['row']);

for($i=0;$i<=$count;$i++)
{
    echo '<tr>';
        if(($i % 3) == 1)
        {
            echo "<td>" . $data['row3'][$i]['cUsername'] . "</td>";
            echo "<td>" . $data['row3'][$i]['postCount'] . "</td>";
        }else if(($i % 2) == 1)
        {
            echo "<td>" . $data['row2'][$i]['cUsername'] . "</td>";
            echo "<td>" . $data['row2'][$i]['postCount'] . "</td>";
        }else /*Never try find remainder of 1 as theres always a multiple of 1*/
        {
            echo "<td>" . $data['row'][$i]['cUsername'] . "</td>";
            echo "<td>" . $data['row'][$i]['postCount'] . "</td>";
        }
    echo '</tr>';
}

By fetching the results individually into a local array instead of trying to fetch 3 different rows at the same time you should do them individually and store them in a local variable, just unset the variable after words if its a large array.

my code is offered as untested.

MikeTheReader · Accepted Answer · 2010-08-31 21:58:18Z

2

The while loop is taking an OR clause. This means if the first one returns true, it won't execute the second one.

answered Aug 31, 2010 at 21:58

MikeTheReader

4,1903 gold badges27 silver badges38 bronze badges

3 Comments

BigMike Over a year ago

Thanks Dante, how do I get it to run ALL of them?

You Over a year ago

You need to use the and operator (&&)

RobertPitt Over a year ago

also note using the && logical operator will stop the loop if anyone of the three fail.

Quassnoi · Accepted Answer · 2010-08-31 22:01:12Z

This will require opening three separate connections to MySQL aтв running three queries, each in its own connection.

You better rewrite your query as this

SELECT  user_id,
        (
        SELECT  COUNT(*)
        FROM    posts p
        WHERE   p.user_id = u.user_id
                AND p.date >= '2010-06-01'
                AND p.date < '2010-07-01'
        ) AS june_count,
        (
        SELECT  COUNT(*)
        FROM    posts p
        WHERE   p.user_id = u.user_id
                AND p.date >= '2010-07-01'
                AND p.date < '2010-08-01'
        ) AS july_count,
        (
        SELECT  COUNT(*)
        FROM    posts p
        WHERE   p.user_id = u.user_id
                AND p.date >= '2010-08-01'
                AND p.date < '2010-09-01'
        ) AS aug_count
FROM    users u

Collectives™ on Stack Overflow

Multiple query data into single HTML Table (PHP, MySQL)

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related