mysqli_fetch_array迭代问题

Question

I have a page that is dynamically generated using PHP and a MySQL database. The page displays information about an event, and lists the dates that event will run.

My database has the following relevant tables:

events - containing the event name, cost etc

venue - containing venue information

instance - containing the event_id, venue_id, the date the event at that venue will run, and the capacity for that instance.

registration - containing the instance_id, and attendee_id.

To grab all the information to actually display the event information, I use the following code:

$eid = $_GET['event_id'];
        $q = "SELECT e.event_name, e.event_description, e.event_byline, e.event_benefit, e.event_cost, e.event_exam, e.event_resi, i.venue_id, i.instance_id, i.instance_cap, v.venue_name, 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
        INNER JOIN venue AS v ON i.venue_id = v.venue_id
        WHERE e.event_id = $eid
        ORDER BY i.venue_id, i.instance_date";

        $cur_venue = 0;
        $r = @mysqli_query ($dbc,$q) or die(mysqli_error($dbc));
        $row = mysqli_fetch_array($r, MYSQLI_ASSOC);

Now, what I want to do is display a list, sorted by venue, of the instances for the relevant event, which I have done up to a point. However, what I also want to do is only display the instance if there is space left on that particular instance.

Since I know the capacity of each instance (from my instance_cap column), and I can COUNT the number of attendees registered to each instance, I figure I can do this thuswise:

do
{
    $list_instance = $row['instance_id'];

    $qRegs = "SELECT COUNT(delegate_id) AS regs FROM registration
    WHERE registration.instance_id = $list_instance";
    $rRegs = mysqli_query($dbc,$qRegs);
    $registrations = mysqli_fetch_object($rRegs);

    $capacity = $row['instance_cap'];

    $availability = $capacity - $registrations->regs;

    if ($availability > 0){ //if event has places available...
        if ($cur_venue != $row['venue_id']) //and if the current venue is not the same as the venue id
        {
            echo '<li id="'.$row['venue_name'].'">'
            $cur_venue = $row['venue_id']; 
            echo '<h4>'.$row['venue_name'].'</h4>';//display the venue name
        }
        echo '<a href="#">'.$row['DATE'].'</a>' //display the date for current instance
        echo '</li>';//close list tag
    }
} while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC));';

The problem I have is that this misses out the first instance and skips straight to the second one. I understand that this is probably due to the fact that I have called mysqli_fetch_array twice, so how I can work it so that this doesn't happen?

Answer 1

You should be able to run one query, and then just use the resultset. Try this:

SELECT e.event_name, e.event_description, e.event_byline, 
e.event_benefit, e.event_cost, e.event_exam, e.event_resi, 
i.venue_id, i.instance_id, i.instance_cap, v.venue_name, 
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
INNER JOIN venue AS v ON i.venue_id = v.venue_id
WHERE e.event_id = $eid AND i.instance_cap > (SELECT COUNT(r.delegate_id) FROM registration AS r WHERE r.instance_id = i.instance_id)
ORDER BY i.venue_id, i.instance_date

Granted, this is untested, so it might not work as-is.

EDIT: a sub-query is probably more correct. See the edited query above.