Error in fetching multidimensional array from database using php [duplicate]

Question

There is a database exams having a table exam.
I want to run a query stored in $sql_query.

Output it is showing in localhost SQL server is : Result of this query in sql server.

But while using it in php it is showing me only single row.

$sql_query = "SELECT * FROM exam_timing AS et INNER JOIN exam_details AS ed
  ON et.exam_id = ed.Id WHERE et.Admin_Id = '$username'";
$ids = mysqli_fetch_array(mysqli_query($db_exams, $sql_query), MYSQLI_NUM);

foreach($ids as $id) {
 echo $id," ";
}

Output of this is :

27 113 15:59:00 2020-12-25 30 2 pb 113 php BCA 2 1

It should show two rows.

Warning: You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input! Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. Escaping is not enough! — Dharman
– Dharman ♦, Commented Dec 25, 2020 at 22:53

Steven · Accepted Answer · 2020-12-26 12:29:51Z

1

Problems

You need to substitute every variable in the query with a parameter, binding variables later
The way you fetch the result
- You only fetch the first result row
- Your loop loops through the columns not the result set

Solution

// SQL statement
$sql = "
    SELECT *
    FROM exam_timing AS et 
        INNER JOIN exam_details AS ed
            ON et.exam_id = ed.Id
    WHERE et.Admin_Id = ?
";

$query = $mysqli->prepare($sql);    // Prepare the query
$query->bind_param("s", $username); // `s` because you're variable was $username and I've assumed string; use `i` if it's an integer id
$query->execute();                  // Run the query
$result = $query->get_result();     // Get the result set from the database

// Loop through returned result sets
while( $row = $result->fetch_assoc() ){
    // Loop through the columns in the row
    foreach($row as $column){
        echo $column, ' ';
    }
    echo "\n";
}


/* Output:

27 113 15:59:00 2020-12-25 30 2 pb 113 php BCA 2 1
28 114 16:32:00 2021-01-09 23 2 pb 114 php BCA 3 7

*/

edited Dec 26, 2020 at 12:29

answered Dec 25, 2020 at 13:23

Steven

6,1582 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steven Over a year ago

@YourCommonSense I have replaced the content in question. However, I would like to follow up with a question: Suppose I were to use a (lets assume sanitised in the best way possible) variable as a table name... I can't bind that? So the terminology doesn't make sense (in that admittedly specific scenario)? I wonder if there's a catch all solution?

Your Common Sense Over a year ago

You are correct. Yes, of course you cannot bind a table name. The plus here, unlike escaping, a prepared statement will tell you it's impossible, but won't silently let it go and make injection possible. It took me many years to formulate a catch-all formula, which is "prepare all data variables and whitelist everything else". I wrote it in this answer, stackoverflow.com/a/7537500/285587

Sudhanshu Rai · Accepted Answer · 2020-12-26 09:15:01Z

0

mysqli_fetch_array only fetches the first row of the result as an array and increments the result set row pointer

Use the function mysqli_fetch_all

$ids = mysqli_fetch_all(mysqli_query($db_exams, $sql_query), MYSQLI_NUM);

More details on mysqli_fetch_all here

edited Dec 26, 2020 at 9:15

answered Dec 25, 2020 at 13:18

Sudhanshu Rai

2,0691 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

Error in fetching multidimensional array from database using php [duplicate]

2 Answers 2

Problems

Solution

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Problems

Solution

2 Comments

Comments

Linked

Related