PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
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:
<?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
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:
<?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