CODEWITHSUNDEEP
phpmysqljoinfetch
Astrid
Astrid

Reputation: 1312

Continue MYSQL output in PHP

I have a select on a database, like this:

$result = mysql_query("                 
SELECT dat_eb_registrants.id, dat_eb_registrants.first_name,  
    dat_eb_registrants.last_name, dat_eb_registrants.email, dat_eb_registrants.comment,   
    dat_eb_registrants.amount, dat_eb_registrants.published,   
    dat_eb_registrants.transaction_id, dat_eb_registrants.register_date,   
    dat_eb_field_values.field_value   
FROM dat_eb_registrants LEFT JOIN dat_eb_field_values 
  ON dat_eb_registrants.id=dat_eb_field_values.registrant_id
WHERE dat_eb_field_values.field_id='53' AND `event_id` >= 20 AND `event_id` <= 25
ORDER BY $sort $ascdsc
");

and it gets diplayed in a html table like so: echo " ID First name Last name Email Comment Value1 Value2 Value3 ";

while ($row = mysql_fetch_row($result)) {

echo "<tr>";
echo "<td>" . $row[0] . "</td>";
echo "<td>" . $row[1] . "</td>";
echo "<td>" . $row[2] . "</td>";
echo "<td>" . $row[3] . "</td>";
echo "<td>" . $row[4] . "</td>";

echo "<td>" . $row[9] . "</td>";

echo "<td>" . $row[?] . "</td>";
echo "<td>" . $row[?] . "</td>";
echo "</tr>";
echo "</table>";

}

Now, the first 4 values are being displayed correctly, just like the 5th one, but how about the 6th and the 7th one? they are not being called by MYSQL because dat_eb_field_values.field_value is only called one time, and is assigned the value WHERE dat_eb_field_values.field_id='53

How can i complete the table with the other values in the database?

Thanks in advance, Laurent

Upvotes: 0

Views: 97

Answers (2)

Thickey
Thickey

Reputation: 120

Voitek Zylinski answer is I think the best way to do this. but you could try a for each.

while ($row = mysql_fetch_row($result))
{
    echo "<tr>";
    foreach($row as $row_item)
    {
        echo "<td>".$row_item."</td>"
    }
    echo "</tr>";
}

This would work no matter how many field

Upvotes: 0

Sygmoral
Sygmoral

Reputation: 7181

If I understand correctly, you are trying to load multiple values for each person. These values are in multiple rows in a separate table.

You'll need to use a GROUP BY clause. JOIN the registrants table with the values table just like you do now, and after the WHERE clause, GROUP BY the registrant's id. And of course, don't restrict the WHERE to a certain value's ID, because then you won't get any other values. Finally, you can use GROUP_CONCAT() to get all the value rows into a single result value, delimited by ,. You can change this to be delimited by </td><td> to make it easier to put into your table.

SELECT registrants.name, GROUP_CONCAT(values.value SEPARATOR '</td><td>')
FROM registrants
LEFT JOIN values USING (registrant_id)
WHERE ...
GROUP BY registrant_id

Upvotes: 0

Related Questions