phpmyadmin Question posted in
The official documentation can be found here.

Unable to get all rows from mysqli result to transfer to array – Phpmyadmin

When query this select statement directly to my database (in phpmyadmin) I get back 5 rows. : 

SELECT class.* FROM class, professor, administers WHERE professor.professorID = administers.professorID AND class.classID = administers.classID AND professor.uName='username';

When i query using mysqli I am getting back 5 rows in my mysqli_num_rows($result). However when I try to transfer each row of $result to an array, I am losing the first row. I know this is an error on my part in my PHP but I cannot figure out where I’ve gone wrong. I’ve tried many variations on my while loop as well as some variations of for loops and I’ve exhausted my ideas.

On my logic page i use the following:

$stmt = mysqli_stmt_init($conn);
$sql = "SELECT class.* FROM class, professor, administers WHERE professor.professorID = administers.professorID AND class.classID = administers.classID AND professor.uName=?;";
$acct = $_SESSION['userUid'];

    if (!mysqli_stmt_prepare($stmt, $sql))
        {
            header("Location: ../index.php?error=sqlerror");
            exit();
        }
    else{
    mysqli_stmt_bind_param($stmt, "s", $acct);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    }
    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        exit();
    }
mysqli_fetch_assoc($result);

while ($row = mysqli_fetch_assoc($result))
{$asocres[] = $row;}

and when I print_r($asocres); on the front end page I get back 4 rows (first row missing)

My question is why am i losing the first row of my query?

—–EDIT—–

i realized that it was the first instance of mysqli_fetch_assoc($result); before the while loop. Why was this causing an issue though? how did this result in the first row being skipped in my while loop?

Tags:

3

Answers


  1. 0

    You need to remove:

    mysqli_fetch_assoc($result);

    And you are good to go.

    Note:-mysqli_fetch_assoc($result); will fetch first record and moves the internal data pointer to the next record. So while() will start from second record.

    Login or Signup to reply.
  2. 0

    mysqli_fetch_assoc returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.

    Calling mysqli_fetch_assoc simply moves the result set’s internal pointer further down the list of rows, eventually pointing to NULL (end of list). The rows are all still there, you haven’t removed any of them: you’ve simply traversed the list, and you could run through them again by using something like mysqli_data_seek to reset the internal pointer to a specific row.

    For this issue use mysqli_fetch_assoc only inside loop.

    Additional Info

    You’d need to call mysqli_free_result to “free” the result set.

    Login or Signup to reply.
  3. 0

    Try this way 

    $stmt = mysqli_stmt_init($conn);
$sql = "SELECT class.* FROM class, professor, administers WHERE professor.professorID = administers.professorID AND class.classID = administers.classID AND professor.uName=?;";
$acct = $_SESSION['userUid'];

    if (!mysqli_stmt_prepare($stmt, $sql))
        {
            header("Location: ../index.php?error=sqlerror");
            exit();
        }
    else{
    mysqli_stmt_bind_param($stmt, "s", $acct);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    }
    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        exit();
    }

while ($row = mysqli_fetch_assoc($result))
{$asocres[] = $row;}

    Try to print the rows in while loop and see if you get the 1st row.

    Why its happening is beacuse the first call sets the array pointer to the first element, and then the second call -in while loop- set the pointer to the second element.
    I hope my is answer is helpful.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search