Page 1 of 1

Procedural mysqli insert statement

Posted: Fri Aug 14, 2009 10:28 am
by rick.emmet
Hi Everyone,
I have a question that I have not been able to look up anywhere. I think I’m missing the proper nomenclature and therefore can’t find this in either a google search or at the PHP.net site. I going through "PHP and MySQL Web Development" by Welling and Thompson (which by the way is an excellent resource) and in chapter 11 they have a script to insert data (as variables) into the database. They are using an OO approach and in this case do not have the procedural code for inserting the variables. Here’s what the code looks like:

Code: Select all

<?php
  // create short variable names
  $isbn=$_POST['isbn'];
  $author=$_POST['author'];
  $title=$_POST['title'];
  $price=$_POST['price'];
 
  if (!$isbn || !$author || !$title || !$price) {
     echo "You have not entered all the required details.<br />"
          ."Please go back and try again.";
     exit;
  }
 
  if (!get_magic_quotes_gpc()) {
    $isbn = addslashes($isbn);
    $author = addslashes($author);
    $title = addslashes($title);
    $price = doubleval($price);
  }
 
  @ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books');
 
  if (mysqli_connect_errno()) {
     echo "Error: Could not connect to database.  Please try again later.";
     exit;
  }
 
  $query = "insert into books values
            ('".$isbn."', '".$author."', '".$title."', '".$price."')";
  $result = $db->query($query);
 
  if ($result) {
      echo  $db->affected_rows." book inserted into database.";
  } else {
      echo "An error has occurred.  The item was not added.";
  }
 
  $db->close();
?>
The snippet “$query = "insert into books values ('".$isbn."', '".$author."', '".$title."', '".$price."')";” is what I’m wondering about. Does anyone know what the proper syntax is for inserting variables into a MySQL database using a procedural approach? Or can someone provide me with the proper search phrase to google? Thanks much!
Cheers,
Rick

Re: Procedural mysqli insert statement

Posted: Fri Aug 14, 2009 11:34 am
by robnet
I think this should point you in the right direction: http://uk3.php.net/manual/en/mysqli.prepare.php

Re: Procedural mysqli insert statement

Posted: Fri Aug 14, 2009 6:11 pm
by rick.emmet
Thanks Robnet,
Of course it's more secure to use prepared statements for insert queries (I was a little too fixated on finding the answer to the question). Duh! What is needed here, was in the very next section. The book does have code using a prepared statement with variables. The code looks like this:

Code: Select all

<?php
  // create short variable names
  $isbn=$_POST['isbn'];
  $author=$_POST['author'];
  $title=$_POST['title'];
  $price=$_POST['price'];
 
  if (!$isbn || !$author || !$title || !$price) {
     echo "You have not entered all the required details.<br />"
          ."Please go back and try again.";
     exit;
  }
 
  if (!get_magic_quotes_gpc()) {
    $isbn = addslashes($isbn);
    $author = addslashes($author);
    $title = addslashes($title);
    $price = doubleval($price);
  }
 
     require_once('Connections/connbooks.php');
     
     echo "<p>The connection to the database was sucsessful.</p>";
 
  if (mysqli_connect_errno()) {
     echo "Error: Could not connect to database.  Please try again later.";
     exit;
  }
  
$query = "insert into books values (?, ?, ?, ?)";
  $stmt = mysqli_stmt_prepare($query, $connbooks);
  mysqli_stmt_bind_param("sssd", $isbn, $author, $title, $price);
  mysqli_stmt_execute();
  echo mysqli_stmt_bind_result().' book inserted into the database.';
  mysqli_stmt_close();
  
  $result = mysqli_query($connbooks, $query);
 
  if ($result) {
      echo  $result." book inserted into database.";
  } else {
      echo "An error has occurred.  The item was not added.";
  }
 
  mysqli_close($connbooks);
?>
Note: I did modify the connection - the book does it slightly differently. For anyone that doesn't know, the expression "sssd" refers to "string, string, string and a double. This tells the database what data types to expect. The code runs fine and this is a much better way to go about having users input data.
Thanks again,
Rick