Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialRobert Young
11,742 PointsInserting data from form into database - PHP
Hi there,
I am doing an excellent tutorial on how to build my own blog from scratch using the PHP:PDO syntax, as recommended by Randy. However, I have come across some syntax I do not quite understand when building an 'add posts' page for users to perform inserts to a database. The syntax is the semicolon before the column name - anyone have any ideas?
$results= $db->prepare('INSERT INTO blog_posts (postTitle,postDesc,postCont,postDate) VALUES (:postTitle, :postDesc, :postCont, :postDate)') ;
$results->execute(array(
':postTitle' => $postTitle,
':postDesc' => $postDesc,
':postCont' => $postCont,
':postDate' => date('Y-m-d H:i:s')
));
//redirect to index page
header('Location: index.php?action=added');
exit;
Anyone know what the semicolon means, e.g. ':postTitle'?
Regards,
Robert London Uk
3 Answers
Nick Fuller
9,027 PointsHi Robert, these are what is called prepared statements.
The purpose of these is to escape SQL injections into your code. So if you didn't use the prepare method on the PDO object and you just inserted a string and say that string was from a form on your website then someone could write SQL and inject it into your query! If someone wrote something like "; DELETE FROM users;". If you have a table named "users" then everyone is gone! POOF!
So we "escape" any information passed into a SQL statement. The PDO object has this written into it to do the heavy lifting for us, all we need to do is use their DSL properly!
There are two ways to do it. The first is how you have it above. You're saying :postTitle will match the key in an associative array that I'm passing in and the value of that key is what I'm going to place into the query.
*Note: your associative array does NOT need to include the colon in the key name. Like below
$results= $db->prepare('INSERT INTO blog_posts (postTitle,postDesc,postCont,postDate) VALUES (:postTitle, :postDesc, :postCont, :postDate)') ;
$results->execute(array(
'postTitle' => $postTitle,
'postDesc' => $postDesc,
'postCont' => $postCont,
'postDate' => date('Y-m-d H:i:s')
));
Another way to do it is by passing a ? instead of the :postTitle. Then the array you pass in absolutely must line up with the order in which the ? occurred in the SQL.
$results= $db->prepare('INSERT INTO blog_posts (postTitle,postDesc,postCont,postDate) VALUES (?, ?, ?, ?)') ;
$data = array($postTitle, $postDesc, $postCont, date('Y-m-d H:i:s'));
$results->execute($data);
See how the first ? matches the postTitle in the query, that means in my array I have to make sure the $postTitle is the FIRST element in the array!
I personally prefer the array way better because there is less code, but that is absolutely personal preference. I can see how the :postTitle way, although more verbose, is a lot more descriptive of what is happening.
Here is the PDO docs for prepared statements
Robert Young
11,742 PointsThanks Nick, I have read the PHP manual and thats cleared things up for me. As has your answer which is very clear and well worded. I should try harder to understand what I am coding. If I copy out someone else's code (in a tutorial) without really understanding it - this will get me nowhere!
Nick Fuller
9,027 PointsAha! But, in this case it could be argued that by copying someone else's code, it prompted you to read the docs to learn it... So it did get you exactly where you wanted to be!
When it comes to learning coding... I like to believe that the path doesn't matter (except for stealing private work) as long as you're learning new stuff!
Your post here actually helped me a lot too. I know Ruby and Rails, but I'm learning PHP, Wordpress & PDO. I had just written some PDO when I saw your post and I had to reference the docs to make sure I gave you correct information. So, it came in at the right time! Thank you!