How can I use mysqli_fetch_array() twice?

Question 1

I am using the entries of a db to fill a row and a column in a table. But I cannot access the SQL returned data twice using mysqli_fetch_array() twice. This doesn't work:

//Copy the result
$db_res = mysqli_query( $db_link, $sql );
$db_res2=$db_res;

//Top row
while ($row = mysqli_fetch_array( $db_res, MYSQL_ASSOC))
{
        echo "<td>". $row['Title'] . "</td>";
}

//leftmost column
while ($row = mysqli_fetch_array( $db_res2, MYSQL_ASSOC))
{
                    echo "<tr>";
        echo "<td>". $row['Title'] . "</td>";
                    .....
                    echo "</tr>";
}

How can I apply mysqli_fetch_array twice on the same result?

Question 2

You don't need the while loop and you don't need to use mysqli_fetch_array() at all.

You can simply loop on the mysqli_result object itself many times.

//Top row
foreach($db_res as $row) {
    echo "<td>". $row['Title'] . "</td>";
}

//leftmost column
foreach($db_res as $row) {
    echo "<tr>";
    echo "<td>". $row['Title'] . "</td>";
    .....
    echo "</tr>";
}

However, you should separate your DB logic from your display logic and to achieve this it is best to use fetch_all(MYSQLI_ASSOC) in your DB logic to retrieve all records into an array.

Question 3

The issue is that you are inserting a reference to $tempArray into $arr. Then you change the reference. By the third loop you have 3 references to the same array. That is why the values are showing that way... you can solve this in a rather non intuitive way.

try:

$stmt->bind_result($tempArray["val1"], $tempArray["val2"],$tempArray["val3"]);
while ( $stmt->fetch () ) {
    $x = $tempArray; //This copies the values of $tempArray to $x and each loop will create a new x.
    array_push($arr, $x);
}

Question 4

I can't imagine that the user_info table is of any benefit to JOIN in, so I'm removing it as a reasonable guess. I am also assuming that your desired columns are all coming from the courses table, so I am nominating the table name with the column names in the SELECT.

For reader clarity, I like to use INNER JOIN instead of JOIN. (they are the same beast)

Casting $user_id as an integer is just a best practices that I am throwing in, just in case that variable is being fed by user-supplied/untrusted input.

You count the number of rows in the result set with mysqli_num_rows().

If you only want to access the result set data using the associative keys, generate a result set with mysqli_fetch_assoc().

When writing a query with JOINs it is often helpful to declare aliases for each table. This largely reduces code bloat and reader-strain.

Untested Code:

$query = "SELECT c.course_name, t.t_fname, t.t_othernames, t.email, f.faculty_name
          FROM reg_students r
          INNER JOIN courses c ON r.course_id = c.course_id
          INNER JOIN faculty f ON c.faculty_id = f.faculty_id
          INNER JOIN tutors t ON c.tutor_id = t.tutor_id
          WHERE r.user_id = " . (int)$user_id;
if (!$result = mysqli_query($conn, $query)) {
    echo "Syntax Error";
} elseif (!mysqli_num_rows($result)) {
    echo "No Qualifying Rows";
} else {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "{$row["course_name"]}<br>";
        echo "{$row["t_fname"]}<br>";
        echo "{$row["t_othernames"]}<br>";
        echo "{$row["email"]}<br>";
        echo "{$row["faculty_name"]}<br><br>";
    }
}

Question 5

If you want an atomic database UPSERT command without a stored procedure and you're not worried about the context being updated, it might worth mentioning that you can also wrap an embedded MERGE statement in an ExecuteSqlCommand call:

public void SaveOrUpdate(MyEntity entity)
{
    var sql =  @"MERGE INTO MyEntity
                USING 
                (
                   SELECT   @id as Id
                            @myField AS MyField
                ) AS entity
                ON  MyEntity.Id = entity.Id
                WHEN MATCHED THEN
                    UPDATE 
                    SET     Id = @id
                            MyField = @myField
                WHEN NOT MATCHED THEN
                    INSERT (Id, MyField)
                    VALUES (@Id, @myField);"

    object[] parameters = {
        new SqlParameter("@id", entity.Id),
        new SqlParameter("@myField", entity.myField)
    };
    context.Database.ExecuteSqlCommand(sql, parameters);
}

This isn't pretty because it works outside EF's abstraction over entities but it will allow you to leverage the MERGE command.

Question 6

To use the DATA filehandle twice you need to rewind it. The tricky bit is that if you do seek(DATA, 0, 0), it'll be positioned to the first source line, not the line after __DATA__. Therefore you need to save the position first:

my $data_start = tell DATA; # save the position
print while (<DATA>);
seek DATA, $data_start, 0;  # reposition the filehandle right past __DATA__
print while (<DATA>);

How can I use mysqli_fetch_array() twice?

Answers

php,sql,mysqli,mysqli-fetch-array