how to make mysqli prepared statement and fetch result?

Question

I can't understand how to create a prepared statement, and all tutorials I have seen was fetching only column. My normal sql query

      $id = $_GET['id']; 
      $result = mysql_query("SELECT * FROM files WHERE id=$id ") or die(mysql_error());
      $row = mysql_fetch_array($result);
      $name = $row['name'];
      $date = $row['date'];

Please show me how to create a prepared statement and how to fetch more than one column and insert the date into variables.

score 2 · Accepted Answer · edited May 23 '17 at 12:05

First of all it's not a good idea to use SELECT * in production. Instead specify needed columns explicitly. Take a look at https://stackoverflow.com/a/65532/1920232.

Now your code might look like

$id = $_GET['id']; 

$db = new mysqli('localhost', 'user', 'password', 'dbname');
$sql = 'SELECT name, date FROM files WHERE id = ?'; //specify columns explicitly 
if ($stmt = $db->prepare($sql)) { //create a prepared statement
    $stmt->bind_param('i', $id); //bind parameters
    $stmt->execute(); //execute query
    $stmt->bind_result($name, $date); //bind result variables
    $stmt->fetch(); //fetch values
}
$db->close();

echo $id, ' ', $name, ' ', $date;

Note: All error handling intentionally skipped for brevity.

how to make mysqli prepared statement and fetch result?

1 Answers1