mysqli_fetch_array iteration issue

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?

Should be able to consolidate this into one query and just use the results directly. — Tieson T.
– Tieson T., Commented Jan 6, 2014 at 10:41
@TiesonT. I can't see how to do this given that there are multiple instances of each event. I am a total noob though :) — ElendilTheTall
– ElendilTheTall, Commented Jan 6, 2014 at 10:46
I'll admit that SQL isn't my strong suite, but try running the updated query below in MySQL Workbench or phpMyAdmin and see if you get the result you're after. — Tieson T.
– Tieson T., Commented Jan 6, 2014 at 10:55

Tieson T. · Accepted Answer · 2014-01-06 11:00:19Z

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.

edited Jan 6, 2014 at 11:00

answered Jan 6, 2014 at 10:49

Tieson T.

21.2k6 gold badges82 silver badges99 bronze badges

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

8 Comments

ElendilTheTall Over a year ago

This gives an error - 'Unknown column 'regs' in 'where clause'

Tieson T. Over a year ago

@ElendilTheTall Okay, one last try; see the updated query above.

ElendilTheTall Over a year ago

Will do. To help me understand what's going on here, am I correct in thinking that this should get only instances that have availability (ie less registrations than capacity)? So I don't need to do any checking of availability, I just need to output the results?

Tieson T. Over a year ago

@ElendilTheTall Yep. The sub-query should return a count of matching registrations. That value is then compared against the value of instance_cap for the "current row". If the evaluation is false the row is excluded from the result set.

Tieson T. Over a year ago

@ElendilTheTall You also will want to look into prepared statements; I didn't notice $eid being injected in the query string, which generally a bad idea. php.net/manual/en/mysqli.quickstart.prepared-statements.php

|

eevaa · Accepted Answer · 2014-01-06 10:38:22Z

1

Put

mysqli_data_seek ( $r , 0 );

before the second mysqli_fetch_array to reset the internal pointer to the first instance.

answered Jan 6, 2014 at 10:38

eevaa

1,4571 gold badge13 silver badges18 bronze badges

2 Comments

Michel Over a year ago

Or skip the first $row= and do a 'while loop' instead of a 'do while loop'

ElendilTheTall Over a year ago

This didn't work - perhaps I'm putting it in the wrong place? I'm putting it just before the do

Collectives™ on Stack Overflow

mysqli_fetch_array iteration issue

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related