-1

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
rohit rawat
  • 9
  • 2
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). 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](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Dec 25 '20 at 22:53

2 Answers2

1

Problems

  1. You need to substitute every variable in the query with a parameter, binding variables later
  2. 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

*/
Steven
  • 6,053
  • 2
  • 16
  • 28
  • @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? – Steven Dec 26 '20 at 12:34
  • 1
    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, https://stackoverflow.com/a/7537500/285587 – Your Common Sense Dec 26 '20 at 12:47
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

Sudhanshu Kumar
  • 1,926
  • 1
  • 9
  • 21