douyue9704 2014-01-06 10:27
浏览 47
已采纳

mysqli_fetch_array迭代问题

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?

  • 写回答

2条回答 默认 最新

  • dongxia1390 2014-01-06 10:49
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题