How do I properly iterate through a mysqli_fetch_a

Question

I have what I thought would be a simple process updating old "mysql" code to "mysqli." In a number of places there was code that iterated through a multi-dimensional array to get the values. I'm trying to make a similar loop and use either mysqli_fetch_array or mysqli_fetch_all and am just banging my head against a wall. Either the process fails (hang) or else it doesn't return any values. I have looked all over the web and found various examples, none of which have quite worked...often they're not for procedural method (https://stackoverflow.com/questions/12026979/how-do-iterate-the-result-of-mysqli-fetch-array).

I currently have:

$query2 = "SELECT * FROM mp WHERE email='$email'";

//connect to database    
require("../dbinfo.php");
$con = mysqli_connect($host,$username,$password,$database);                 

$result2=@mysqli_query($con, $query2);
$array = mysqli_fetch_array($result2, MYSQLI_BOTH);
@mysqli_close($con);  //question...does this need to come later, after num_rows?


$num2=0;
$num2=@mysqli_num_rows($result2);


$a=0;
while ($a < $num2)
        {
//cycle through to get info...this comes up blank, when it should have 3 sets of values.
            $var1=$array[$a]["column"];
            $var2=$array[$a]["id"];
            echo "Column: ".$var1." and ID: ".$var2;
}

I've seen a lot of answers that essentially say "Use PDO" or "why use procedural? Object oriented is the way to go" but I'm really not looking to do a different style. It would be great to just know what's causing the above to fail. I can't be too far off, right? Thank you in advance for the help!

Answer 1

Firstly, you should move @mysqli_close($con); to a point where you won't call any more mysqli-functions (basically, the bottom).

Secondly, you should look up the mysqli_fetch_array()-function in the PHP-manual:

Returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in resultset.

So it returns an array that corresponds to one row, not all rows, meaning that the statement $array[$a]["column"]; doesn't make much sense (I know you're trying to do what the other answer said, but you haven't included the appropriate code to do so [it's provided further down in that answer]). So instead, I recommend using the normal way: Try removing $array = mysqli_fetch_array($result2, MYSQLI_BOTH), and add this instead of your while-loop:

while ($row = mysqli_fetch_array($result2, MYSQLI_BOTH))
{
    echo "Column: " . $row["column"] . " and ID: " . $row["ID"];
}

Since mysqli_fetch_array() changes the result-object, you will get a different result for every loop, meaning that $row will consist of different values. Every different value will be a different row in the database, with the columns as the array keys, and the values as the array values. However, since you're using MYSQLI_BOTH, you can also access a value by using the column-number as the array key. If you don't need this, I suggest changing it to MYSQLI_ASSOC.

This also means that you can completely remove the $num2-variable, and the $a-variable. If you want to use the method you're using now, you need to use the second code provided in the answer to this question before the while-loop. Although note that you need to increase the variable $a with 1 for every loop too, which your current code won't do. Basically, you would need to change it to:

$var1=$array[$a]["column"];
$var2=$array[$a++]["id"]; //the variable $a is increased after $var2 is assigned
echo "Column: ".$var1." and ID: ".$var2;

Although this code is a lot more complicated if you're just going to print the variables... Instead of just printing them, you're assigning them to an array, and later printing them again, which requires 2 loops instead of one. Personally, I would recommend changing the entire code as follows:

//connect to database    
require("../dbinfo.php");

$con = mysqli_connect($host,$username,$password,$database);                 
$result2 = @mysqli_query($con, $query2);

while ($row = mysqli_fetch_array($result2, MYSQLI_BOTH))
{
    echo "Column: " . $row["column"] . " and ID: " . $row["ID"];
}

@mysqli_close($con);