数据库查询，比较每两行，但只获得前两行

Question

Hi i am querying my db so that i can compare every two rows. ex 1 and 2, then 2 and 3, then 3 and 4. and so on and so forth. but it only compares the first two rows. any ideas? here is my code:

$result = mysql_query("SELECT *FROM attendance ORDER BY pid ASC") or die(mysql_error());

// check for empty result
if (mysql_num_rows($result) > 0) {
    // $response["nominees"] = array();

    while ($row = mysql_fetch_array($result)) {
        // temp user array
        $prev_sid = $row["sid"];
        $prev_pid = $row["pid"];

        $row2 = mysql_fetch_array($result);
            if($row2["pid"] == $prev_pid){
                if(($row2["sid"] - $prev_sid) == 1){

                    $attended_elec = mysql_query("SELECT pid FROM election_attendance where election_id = '$election_id'");

                    if(mysql_num_rows($attended_elec) > 0){

                        $not_officer = mysql_query("SELECT usertype FROM users WHERE pid = '$prev_pid'");
                        if(mysql_result($not_officer, 0) == "member"){
                            // echo "PID" . $prev_pid;
                            // $nominee["pid"] = $row2["pid"];
                            $user_details = mysql_query("SELECT *FROM users WHERE pid = '$prev_pid'");

                            if(mysql_num_rows($user_details) > 0){
                                $response["nominees"] = array();

                                while ($row3 = mysql_fetch_array($user_details)) {
                                    $nominee = array();
                                    $nominee["pid"] = $row3["pid"];
                                    $nominee["firstname"] = $row3["firstname"];
                                    $nominee["lastname"] = $row3["lastname"];
                                    $nominee["gender"] = $row3["gender"];
                                    $nominee["contact"] = $row3["contact"];
                                    $nominee["email"] = $row3["email"];
                                    $nominee["address"] = $row3["address"];
                                    $nominee["institution"] = $row3["institution"];
                                    $nominee["points"] = $row3["points"];
                                    $nominee["usertype"] = $row3["usertype"];

                                    // push single product into final response array
                                    array_push($response["nominees"], $nominee);
                                }
                            }
                        }
                    }
                }
            } 
    }
    // success
    $response["success"] = 1;

    // echoing JSON response
    echo json_encode($response);


} else {
    // no products found
    $response["success"] = 0;
    $response["message"] = "There is no candidate yet from the records.";

    // echo no users JSON
    echo json_encode($response);
}

thanks in advance, have a nice day

Answer 1

Theres a problem right at the while(), you're fetching the 1st row, and is correct. Then, inside it you fetch the 2nd, which is what you intend, but when the iteration is repeating, the while will fetch the 3rd, and the inner the 4th, so you lost there the comparisson between 2nd and 3rd.

// fetch data from DB
$results = "...";

if (mysql_num_rows($result) < 1) 
{
    // no products found tell your users
    return;
}

// lets make some variables to walk through the list
$previous = mysql_fetch_array($results);
$actual = null;

// and now lets walk through the list
while($actual = mysql_fetch_array($results))
{
    // execute your logic here

    // than at the end move the actual row to become the previous
    $previous = $actual;
}

NOTICE you shouldn't use mysql_ * methods they're are deprecated. you can use the brother mysqli_ * or PDO