Insert data using pdo classes in PHP

Download This Tutorial In PDF File


<form action="post.php" method="post">
<label for="joketext">Type your joke here:
<textarea id="joketext" name="joketext"
rows="3" cols="40">
<input type="submit" name="submit" value="Add">

Now lets’s handle the form request with our controller post.php. A prepared statement is a special kind of SQL query that you’ve sent to your database server ahead of time, giving the server a chance to prepare it for execution—but not actually execute it. Think of it like writing a .php script. The code is there, but doesn’t actually get run until you visit the page in your web browser. The SQL code in prepared statements can contain placeholders that you’ll supply the values for later, when the query is to be executed. When filling in these placeholders, PDO is smart enough to guard against “dangerous” characters automatically.
Here’s how to prepare an INSERT query and then execute it safely with $_POST[‘joketext’] as the text of the joke:

$sql = 'INSERT INTO `joke` SET
`joketext` = :joketext,
`jokedate` = "today's date"';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':joketext', $_POST['joketext']);

Let’s break this down one statement at a time. First, we write our SQL query as a PHP string and store it in a variable ($sql) as usual. What’s unusual about this INSERT query, however, is that no value is specified for the joketext column. Instead, it contains a placeholder for this value (:joketext). Don’t worry about the jokedate field just now—we’ll circle back to it in a moment. Next, we call the prepare method of our PDO object ($pdo), passing it our SQL query as an argument. This sends the query to the MySQL server, asking it to prepare to run the query. MySQL can’t run it yet—there’s no value for the joketext column. The prepare method returns a PDOStatement object (yes, the same kind of object that gives us the results from a SELECT query), which we store in $stmt. Now that MySQL has prepared our statement for execution, we can send it the missing value(s) by calling the bindValue method of our PDOStatement object ($stmt). We call this method once for each value to be supplied (in this case, we only need to supply one value—the joke text), passing as arguments the placeholder that we want to fill in (‘:joketext’) and the value we want to fill it with ($_POST[‘joketext’]). Because MySQL knows we’re sending it a discrete value, rather than SQL code that needs to be parsed, there’s no risk of characters in the value being interpreted as SQL code. Using prepared statements, SQL injection vulnerabilities simply aren’t possible! Finally, we call the PDOStatement object’s execute method to tell MySQL to execute the query with the value(s) we’ve supplied. One interesting thing you’ll notice about this code is that we never placed quotes around the joke text. :joketext exists inside the query without any quotes, and when we called bindValue we passed it the plain joke text from the $_POST array. When using prepared statements, you don’t need quotes because the database (in our case, MySQL) is smart enough to know that the text is a string and it will be treated as such when the query is executed. The lingering question in this code is how to assign today’s date to the jokedate field. We could write some fancy PHP code to generate today’s date in the YYYY-MM-DD format that MySQL requires, but it turns out that MySQL itself has a function to do this: CURDATE:

$sql = 'INSERT INTO `joke` SET
`joketext` = :joketext,
`jokedate` = CURDATE()';
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':joketext', $_POST['joketext']);

The MySQL CURDATE function is used here to assign the current date as the value of the jokedate column. MySQL actually has dozens of these functions, but I’ll introduce them only as required. Now that we have our query, we can complete the if statement we started earlier to handle submissions of the “Add Joke” form:

if (isset($_POST['joketext'])) {
try {
$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8', 'username', 'yourpass'); 
$sql = "INSERT INTO 'table' SET 'column'=:joketext 'column4date'=CURDATE()";
catch (PDOException $e) {
$title = 'An error has occurred';
$output = 'Database error: ' . $e->getMessage() . ' in '. $e->getFile() . ':' . $e->getLine();

Leave a Reply

Your email address will not be published.

%d bloggers like this: