如何循环遍历MySQL行？ [英] How to cycle through MySQL rows?

问题描述

我正在尝试实现一个Web PHP程序，该程序将以HTML形式显示MySQL数据库中记录的数据。我有几个导航按钮，希望循环到数据库中的下一个和上一个记录。

I am trying to achieve a web PHP program that will display data from a records in a MySQL database in a HTML form. I have a couple of navigation buttons that I wish to cycle to the next and previous records in the database.

我的问题是，我似乎无法循环回去并通过记录。 mysql_fetch_assoc仅获得一行，或者仅在涉及while循环时才有用。

My problem is, I can't seem to cycle back and forth through the records. mysql_fetch_assoc just gets one row or is only good when it comes to a while loop.

任何帮助将不胜感激！
这就是我到目前为止所拥有的...

Any help would be appreciated! This is what I have so far...

    $page = intval($_GET['page']);

    $limitStart = $page - 1;
    if($limitStart < 0) {
        $limitStart = 0;
    }

    $query = "SELECT caleadID, region, siteaddr1, siteaddr2, siteaddr3, siteaddr4, sitepcode, ";
    $query .= "addgennotes, description, value, award, awardaddr1, awardaddr2, awardaddr3, awardaddr4, awardpcode, ";
    $query .= "phone_number, fax_number, fldAwardedWebsite, fldAwardedEmail, contact_name, date FROM tblTradesman_Awarded WHERE 1";
    $query .= ' LIMIT ' . $limitStart . ',2';
    $result = mysqli_query($connection, $query);
    confirm_query($result);

    $result_array = array();
    while($row = mysqli_fetch_assoc($result)) {
        $result_array[] = $row;
    }
    if(intval($page) === 0) {
        $previousRecord = NULL;
        $currentRecord = $result_array[0];
        $nextRecord = $result_array[1];
    } else {
        $previousRecord = $result_array[0];
        $currentRecord = $result_array[1];
        $nextRecord = $result_array[2]; 
    }
    if($previousRecord) {

    }

    if($nextRecord) {

}
?>
<?PHP
    $caleadID = $currentRecord['caleadID'];
    $region = $currentRecord['region'];
    $siteaddr1 = $currentRecord['siteaddr1'];
    $siteaddr2 = $currentRecord['siteaddr2'];
    $siteaddr3 = $currentRecord['siteaddr3'];
    $siteaddr4 = $currentRecord['siteaddr4'];
    $sitepcode = $currentRecord['sitepcode'];
    $addgennotes = $currentRecord['addgennotes'];
    $description = $currentRecord['description'];
    $value = $currentRecord['value'];
    $award = $currentRecord['award'];
    $awardaddr1 = $currentRecord['awardaddr1'];
    $awardaddr2 = $currentRecord['awardaddr2'];
    $awardaddr3 = $currentRecord['awardaddr3'];
    $awardaddr4 = $currentRecord['awardaddr4'];
    $awardpcode = $currentRecord['awardpcode'];
    $phone_number = $currentRecord['phone_number'];
    $fax_number = $currentRecord['fax_number'];
    $fldAwardedWebsite = $currentRecord['fldAwardedWebsite'];
    $fldAwardedEmail = $currentRecord['fldAwardedEmail'];
    $contact_name = $currentRecord['contact_name'];
    $date = $currentRecord['date'];

?>

<div id="tradesmen_data">
    <div id="primary">
        <label for="caleadID">Tradesman Lead ID: </label>
        <input type="text" name="caleadID" value="<?PHP echo $caleadID ?>" />&nbsp;&nbsp;&nbsp;&nbsp;
        <label for="value">Job Value GBP: </label>
        <input type="text" name="value" value="<?PHP echo $value ?>" />&nbsp;&nbsp;&nbsp;&nbsp;
        <label for="date">Tradesman Publish Date: </label>
        <input type="text" name="date" value="<?PHP echo $date ?>" />
    </div>
    <div class="columns">
        <fieldset id="Site">
            <legend>Site Details</legend>
            <dl>
                <dt><label for="siteaddr1">Site Address: </label></dt>
                <dd><input type="text" name="siteaddr1" value="<?PHP echo $siteaddr1 ?>" /></dd>
            </dl>
            <dl>
                <dt><label></label></dt>
                <dd><input type="text" name="siteaddr2" value="<?PHP echo $siteaddr2 ?>" /></dd>
            </dl>
            <dl>            
                <dt><label></label></dt>
                <dd><input type="text" name="siteaddr3" value="<?PHP echo $siteaddr3 ?>" /></dd>
            </dl>
            <dl>            
                <dt><label></label></dt>
                <dd><input type="text" name="siteaddr4" value="<?PHP echo $siteaddr4 ?>" /></dd>
            </dl>
            <dl>            
                <dt><label for="sitepcode">Postcode: </label></dt>
                <dd><input type="text" name="sitepcode" value="<?PHP echo $sitepcode ?>" /></dd>
            </dl>
            <dl>            
                <dt><label for="region">Region: </label></dt>
                <dd><input type="text" name="region" value="<?PHP echo $region ?>" /></dd>
            </dl>
            <dl>                
                <dt><label for="addgennotes">General Notes: </label></dt>
                <dd><textarea rows="4" cols="30" name="addgennotes"><?PHP echo $addgennotes ?></textarea></dd>
            </dl>
            <dl>            
                <dt><label for="description">Description: </label></dt>
                <dd><textarea rows="4" cols="30" name="description"><?PHP echo $description ?></textarea></dd>
            </dl>
        </fieldset>
    </div>
    <div class="columns">
        <fieldset id="Contractor">
            <legend>Contractor Details</legend>
            <dl>
                <dt><label for="award">Awarded To: </label></dt>
                <dd><input type="text" name="award" value="<?PHP echo $award ?>" /></dd>
            </dl>
            <dl>
                <dt><label for="awardaddr1">Address: </label></dt>
                <dd><input type="text" name="awardaddr1" value="<?PHP echo $awardaddr1 ?>" /></dd>
            </dl>
            <dl>
                <dt><label></label></dt>
                <dd><input type="text" name="awardaddr2" value="<?PHP echo $awardaddr2 ?>" /></dd>
            </dl>
            <dl>
                <dt><label></label></dt>
                <dd><input type="text" name="awardaddr3" value="<?PHP echo $awardaddr3 ?>" /></dd>
            </dl>
            <dl>
                <dt><label></label></dt>
                <dd><input type="text" name="awardaddr4" value="<?PHP echo $awardaddr4 ?>" /></dd>
            </dl>
            <dl>
                <dt><label for="awardpcode">Postcode: </label></dt>
                <dd><input type="text" name="awardpcode" value="<?PHP echo $awardpcode ?>" /></dd>
            </dl>
            <dl>
                <dt><label for="phone_number">Telephone: </label></dt>
                <dd><input type="text" name="phone_number" value="<?PHP echo $phone_number ?>" /></dd>
            </dl>
            <dl>
                <dt><label for="fax_number">Fax: </label></dt>
                <dd><input type="text" name="fax_number" value="<?PHP echo $fax_number ?>" /></dd>
            </dl>
            <dl>
                <dt><label for="fldAwardedWebsite">Website: </label></dt>
                <dd><input type="text" name="fldAwardedWebsite" value="<?PHP echo $fldAwardedWebsite ?>" /></dd>
            </dl>
            <dl>
                <dt><label for="fldAwardedEmail">Email: </label></dt>
                <dd><input type="text" name="fldAwardedEmail" value="<?PHP echo $fldAwardedEmail ?>" /></dd>
            </dl>
            <dl>
                <dt><label for="contact_name">Contact Name: </label></dt>
                <dd><input type="text" name="contact_name" value="<?PHP echo $contact_name ?>" /></dd>
            </dl>
        </fieldset>
        <?PHP
            $contact_made = true;
            if ($contact_made) {
                echo "<div id=\"contact_made\">";
                echo "Contact has been made";
                echo "</div>";
            }
        ?>
    </div>
    <div class="clear_float"></div>
</div>
<div id="tradesmen_data_navigation">
    <a href="#"><input type="submit" name="previous" value="Previous Record" /></a>
    <a href="#"><input type="submit" name="view_lead" value="Generate Lead" /></a>
    <a href="#"><input type="submit" name="next" value="Next Record" /></a>
</div>

推荐答案

该代码未经测试，但应该指出

The code is untested, but it should point you into the right direction.

GET参数 page 控制在设置 LIMIT 使用 page 的值。

The GET parameter page controls which section of the result set to show as you set the LIMIT of the query using the value of page.

您可以从数据库中选择当前记录以及上一个和下一个。这样，您知道是否可以生成指向应用程序中下一个/上一个记录的链接。

You select the current record from DB as well as the previous and the next. This way, you know if you can generate a link to the next/previous record in your application.

$page = intval($_GET['page']);

$limitStart = $page - 1;
if($limitStart < 0) {
    $limitStart = 0;
}

$query = "SELECT caleadID, region, siteaddr1, siteaddr2, siteaddr3, siteaddr4, sitepcode, ";
$query .= "addgennotes, description, value, award, awardaddr1, awardaddr2, awardaddr3, awardaddr4, awardpcode, ";
$query .= "phone_number, fax_number, fldAwardedWebsite, fldAwardedEmail, contact_name, date FROM tblTradesman_Awarded WHERE 1";
$query .= ' LIMIT ' . $limitStart . ',3';
$result = mysqli_query($connection, $query);
confirm_query($result);
$result_array = array();
while($row = mysqli_fetch_assoc($result)) {
    $result_array[] = $row;
}

if(intval($page) === 0) {
    $previousRecord = NULL;
    $currentRecord = $result_array[0];
    $nextRecord = $result_array[1];
} else {
    $previousRecord = $result_array[0];
    $currentRecord = $result_array[1];
    $nextRecord = $result_array[2]; 
}

if($previousRecord) {
    $prevPage = $page - 1;
    if($prevPage < 0) {
        $pagePage = 0;
    }

    echo '<a href="index.php?page=' . $prevPage . '">previous</a>';
}

if($nextRecord) {
    echo '<a href="index.php?page=' . ($page + 1) . '">next</a>';
}

这篇关于如何循环遍历MySQL行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！