Mysqli_Fetch_Array Returning Only One Result

mysqli_fetch_array returning only one result

mysqli_fetch_array works by pointers each time it's called

Imagine the following

$result = mysqli_query($connection, "select university from universities_alpha");
$row = mysqli_fetch_array($result); // this is the first row
$row = mysqli_fetch_array($result); // now it's the second row
$row = mysqli_fetch_array($result); // third row

To actually display the data the way you want it to, I suggest you do the following

$rows = array();
$result = mysqli_query($connection, "select university from universities_alpha");
while($row = mysqli_fetch_array($result)) {
    $rows[] = $row;
}

print_r($rows);

fetch_array gets only one row

This is what fetch_array is supposed to do. It fetches one row from the data.

To go through all rows you would have to do something like this:

while ($invdata = $result->fetch_array(MYSQLI_ASSOC) and $invdata2 = $result2->fetch_array(MYSQLI_ASSOC)) {
   $the_data = array_merge($invdata,$invdata2);
   // Do something with this row of data
}

It's also important to note that the way you create the queries right now could lead to SQL Injection if $invoice_id is user input. An attacker could insert a quote ' to close the string and execute malicious SQL commands. To prevent this you need to use prepared statements to ensure that user input doesn't get interpreted as code. Here's a post explaining how to do that with mysqli: How can I prevent SQL injection in PHP?

As @El_Vajna pointed out this will stop looping when any of the two results end. To make it go further even if only one result has data you can change the and inside the if statement to an or. Then only one needs to contain more rows

mysqli_fetch_array only displays the first result

The return statement in your while loop causes the function to terminate immediately, returning just the first row. Instead, you should collect the results in an array and return the array at the end:

$rows = array();

while($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
  $rows[] = $row['name'] . " " . // ...
}

mysqli_free_result($result);
return $rows;

MySQLi why am I only getting 1 result?

There's many things wrong with your code!

As pointed by @IsThisJavascript and @Cashbee:

• You are executing a query with a LIMIT 1 statement, it will only return one record.

As pointed by myself:

• Doing echo $row[0] will have the same result, if you are only echoing the first value of the array you can't expect to have multiples can you?

As pointed by @IsThisJavascript:

• You need to loop the results array, like so:

 while($row = mysqli_fetch_array($result)){  
     echo $row['result_category'];  
 }

• Consider switching the query from a '=' to a '%like%' statement, to maximize results if you want to get the values that partionaly cointain the string.

mysql_fetch_array return only one row

That's because the array represents a single row in the returned result set. You need to execute the mysql_fetch_array() function again to get the next record. Example:

while($data = mysql_fetch_array($array)) {
  //will output all data on each loop.
  var_dump($data);
}

mysqli_fetch_array how to get one row at a time?

Firstly, your code is dangerous because can be hacked via sql injection. You always should use parameter bindings.

The simplest way is passing an id of the question stored in mst_question and selecting one by WHERE clause (like test_id).

//...
$test_id=$_POST["test_id"];
$questionId = filter_var($_POST['question_id'],FILTER_VALIDATE_INT);
if (!$questionId){
   die('done');
}

$stmt= mysqli_prepare($con,"select * from mst_question where test_id='$test_id' AND id=?");
mysqli_stmt_bind_param(**$stmt**, 'd',$questionId);
mysqli_stmt_execute(**$stmt**);
// work with $stmt. 
// f.e. your loop but now there will be only one execution
mysqli_stmt_close($stmt);
//...
$createTable .= '<input type="hidden" name="nextQuestionId" value="'.$nextQuestionId.'"/>';
//...

With input field you will return id of the next question which can be passed in url argument within javascript code.

if you are worried about quiz-cheaters, you can increase security by hashing an nextQuestionId.

//...
$stmt = mysqli_prepare($con,"select * from mst_question where test_id='$test_id' AND sha1(CONCAT('slat_',id))=?");
//...
$createTable .= '<input type="hidden" name="nextQuestionId" value="'.sha1('salt_'.$nextQuestionId).'"/>';
//...

It's not the best solution but requiring minimal changes of your code.

I would like to suggest switching to PDO - very friendly and powerful way to interact with database. See an example.

mysqli_fetch_array return value for session but not variable

Your problem is that you keep trying to fetch more results. Each time you call mysqli_fetch_array() you ask for the next row - but you only expect one.

Instead, assign the row to a variable $row and use that where you need the columns.

Also, I've changed your role-checks to simplify it a bit. Your current logic was slightly incorrect, as it would always be a customer.

session_start();
$row =  mysqli_fetch_array($result);
$_SESSION['uId'] = $row[0];
$_SESSION['user'] = $user;
$_SESSION['pass'] = $pass;
$role = $row[3];
if (!in_array($role, ['admin', 'staff', 'manager']))
{
    $_SESSION['role'] = 'customer';
} else {
    $_SESSION['role'] = $role;
}
echo $role;

That said, you probably can alter your approach to have one table users, and a column to indicate what role they have. No need to have separate tables.

You should also use a prepared statement, and proper hash of your passwords. See How to use password_hash and How can I prevent SQL injection in PHP?

mysql_query - mysql_fetch_array only returning last input row

if you want to fetch all the record

$query = mysql_query("SELECT * from table_name");
while($returned_array = mysql_fetch_array($query)){
/*.......*/
}

mysqli fetch array displays only the first row

You need to iterate through the result set returned by MySQL. That means calling mysqli_fetch_array() for each row of that result set. You can do that using a while loop:

while($row = mysqli_fetch_assoc($result)) {
    $rows[] = array("name"      => $row['name'],
                    "city"      => $row['bill_city'],
                    "code"      => $row['bill_code'],
                    "country"   => $row['bill_country'],
                    "street"    => $row['bill_street'],
                    "latitude"  => $row['latitude'],
                    "longitude" => $row['longitude'],
                    "type"      => $row['setype']);
    }
}

---

Related Topics