Looping through MySQL rows in PHP

Question

I have a MySQL database with 3 tables - events,venues,instance. events contains informations on events such as event id, name and description. venues contains venue id and venue name. instance contains event id, venue id and date.

I perform a join and load the results into an array so I can display the event information on a page using PHP:

$eid = $_GET['event_id'];

$q = "SELECT e.event_name, e.event_description, i.venue_id, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM events AS e
INNER JOIN instance AS i ON e.event_id = i.event_id
WHERE e.event_id = $eid";

$r = @mysqli_query ($dbc,$q);
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);

The name and description only appear once on the page so they're easy to display with:

echo $row['event_name']

However, what I also need to do is display a list of dates sorted by venue, like:

**New York**

18th December, 2013
19th January, 2014
21st February, 2013

**Washington, DC**

18th December, 2013
22nd December, 2013
12th March, 2014

Can I do this using the existing query, or would it be simpler to write a new query? If so, how? Also, how would I loop it so that I display the venue, then the dates, then the next venue etc?

If you ORDER BY cityname, you can keep the query. While looping over all your entries, check if the cityname is the same as the cityname of the last iteration. If i understand your question correctly at least :S — Viridis
– Viridis, Commented Dec 10, 2013 at 10:46

Kickstart · Accepted Answer · 2013-12-10 12:12:46Z

2

Put the value of the venue out when it changes. Something like this:-

<?php

$eid = $_GET['event_id'];

$q = "SELECT e.event_name, e.event_description, i.venue_id, i.venue_name, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM events AS e
INNER JOIN instance AS i ON e.event_id = i.event_id
WHERE e.event_id = $eid
ORDER BY i.venue_id, i.instance_date";
$cur_venue = 0;
$r = @mysqli_query ($dbc,$q);
if ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
    $cur_venue = $row['venue_id'];
    echo $row['event_name']."<br />";
    echo $row['venue_name']."<br />";
    echo $row['DATE']."<br />";
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
    {
        if ($cur_venue != $row['venue_id'])
        {
            $cur_venue = $row['venue_id'];
            echo $row['venue_name']."<br />";
        }
        echo $row['DATE']."<br />";
    }
}

?>

Possibly clean it up using a do...while loop:-

<?php

$eid = $_GET['event_id'];

$q = "SELECT e.event_name, e.event_description, i.venue_id, i.venue_name, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM events AS e
INNER JOIN instance AS i ON e.event_id = i.event_id
WHERE e.event_id = $eid
ORDER BY i.venue_id, i.instance_date";
$cur_venue = 0;
$r = @mysqli_query ($dbc,$q);
if ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
    echo $row['event_name']."<br />";

    do
    {
        if ($cur_venue != $row['venue_id'])
        {
            $cur_venue = $row['venue_id'];
            echo $row['venue_name']."<br />";
        }
        echo $row['DATE']."<br />";
    } while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC));
}

?>

EDIT - Had a play with the test data and the below appears to work.

<?php

#------ database connections -------
define('MYSQLHOST','localhost');
define('MYSQLUSER','');
define('MYSQLPASS','');
define('MYSQLDATABASE','test');
define('MYSQLDATABASE_INTERNAL', '');// sf orders database

$dbc = mysqli_connect(MYSQLHOST, MYSQLUSER, MYSQLPASS, MYSQLDATABASE);


$eid = $_GET['course_id'];

$q = "SELECT e.course_name, e.course_description, i.venue_id, v.venue_name, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM courses AS e
INNER JOIN instance AS i ON e.course_id = i.event_id
INNER JOIN venue AS v ON i.venue_id = v.venue_id
WHERE e.course_id = $eid
ORDER BY i.venue_id, i.instance_date";
$cur_venue = 0;
$r = @mysqli_query ($dbc,$q) or die(mysqli_error($dbc));
if ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
    echo $row['course_name']."<br />";

    do
    {
        if ($cur_venue != $row['venue_id'])
        {
            $cur_venue = $row['venue_id'];
            echo $row['venue_name']."<br />";
        }
        echo $row['DATE']."<br />";
    } while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC));
}

?>

edited Dec 10, 2013 at 12:12

answered Dec 10, 2013 at 10:50

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

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

10 Comments

ElendilTheTall Over a year ago

This works perfectly - apart from the fact that it doesn't display the first venue or its dates. I have 2 venues in my test database, and it displays the second one only (venue_id=2). EDIT: having added another venue, id=3, it also displays that. So a loop iterator issue?

Kickstart Over a year ago

Just need to add an echo of the details before the WHERE (I have edited the above now)

ElendilTheTall Over a year ago

This too works nearly perfectly, but only displays one date in the first venue when there is actually two.

Kickstart Over a year ago

Ooops. Think it would have put that date out, just for the wrong venue. Need to output the venue prior to the while loop as well. Also added a slightly different variation using do...while (not tested). This removes a couple of repeated lines of code.

ElendilTheTall Over a year ago

Again it works, but still only outputs one date for the first venue. It outputs multiple dates for the other two, just not the first one. :)

|

Muhammad Raheel · Accepted Answer · 2013-12-10 10:45:26Z

1

You can use Mysql GROUP_CONCAT

SELECT 
    e.event_name, 
    e.event_description, 
    i.venue_id, 
    i.instance_avail, 
    GROUP_CONCAT(DATE_FORMAT( i.instance_date,  '%M %D, %Y' )) AS DATE
FROM events AS e
INNER JOIN instance AS i ON e.event_id = i.event_id
WHERE e.event_id = $eid
GROUP BY e.event_id

This will give you all dates comma seperated which can be exploded to an array in php.

answered Dec 10, 2013 at 10:45

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Collectives™ on Stack Overflow

Looping through MySQL rows in PHP

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related