PHP list while loop

Question

how do i put list in while loop?

PHP:

> $q = $db->query("SELECT * FROM
> bs_events LEFT JOIN bs_reservations ON
> bs_reservations.id_event = bs_events.id   
> GROUP BY bs_reservations.id_event 
> ORDER BY bs_events.eventDate ASC LIMIT 20");

HTML TABLE:

> while($r = $q->fetch_array(MYSQLI_ASSOC)):  
> echo '<tr>';
> echo '<td>' . $r['title'] . '</td>';  
> echo '<td>' . date('d/M/Y', strtotime($r['eventDate'])) . ' / ' . $r['eventTime'] . '</td>';  
> echo '<td>' . $r['location'] . '</td>';  
> echo '<td>' . array($r['username']) .'</td>';  
> echo '<td ></td>';  
> echo '<td ></td>';  
> echo '</tr>';  
> endwhile;

the above code will output

TITLE        DATE/TIME      LOCATION    USERNAME
=================================================
1       dd/mm/yyy / hh:mm   location    username
1       dd/mm/yyy / hh:mm   location    username1 
1       dd/mm/yyy / hh:mm   location    username2 
2       dd/mm/yyy / hh:mm   location    username

and i want the output

TITLE        DATE/TIME      LOCATION    USERNAME
=================================================
1       dd/mm/yyy / hh:mm   location    username, username1, username2  
2       dd/mm/yyy / hh:mm   location    username

or do i need to put foreach on echo '<td>' . array($r['username']) .'</td>';

Mark Baker · Accepted Answer · 2011-02-08 17:24:26Z

2

Something like

SELECT title,
       event_date,
       event_time,
       location,
       GROUP_CONCAT(username SEPARATOR ', ') AS username
  FROM bs_events 
  LEFT JOIN bs_reservations
         ON bs_reservations.id_event = bs_events.id   
 GROUP BY bs_reservations.id_event 
          bs_events.title,
          bs_events.event_date,
          bs_events.event_time,
          bs_events.location
 ORDER BY bs_events.eventDate ASC LIMIT 20

EDIT

Added alias for username

edited Feb 8, 2011 at 17:24

answered Feb 8, 2011 at 16:59

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tonoslfx Over a year ago

thanks .. but its showing blank on username. how do i put the username list $r['username']?

Mārtiņš Briedis · Accepted Answer · 2011-02-08 16:55:02Z

2

Check out GROUP_CONCAT

answered Feb 8, 2011 at 16:55

Mārtiņš Briedis

17.9k5 gold badges59 silver badges78 bronze badges

Collectives™ on Stack Overflow

PHP list while loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related