Hello again. As I mentioned in another post, I used to love coding, but I only do it as a hobby now and am interested in your opinion on this code. It is several examples, but the Bind_Param was never used. Can anyone tell me if this is a CORRECT way to handle Prepared Statements? Thank you so much. I value the community opinion very much and it has always been helpful to me.
// FETCH MULTIPLE POSTS
$author = 'Brad';
$is_published = true;
$id = 1;
// Positional Params
$sql = 'SELECT * FROM posts WHERE author = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$author]);
$posts = $stmt->fetchAll();
foreach ($posts as $post){
echo $post->title . '<br>';
}
// Named Params
$sql = 'SELECT * FROM posts WHERE author = :author && is_published = :is_published';
$stmt = $pdo->prepare($sql);
$stmt->execute(['author' => $author, 'is_published' => $is_published]);
$posts = $stmt->fetchAll();
var_dump($posts);
foreach ($posts as $post){
echo $post->title . '<br>';
}
// FETCH SINGLE POST
$sql = 'SELECT * FROM posts WHERE id = :id';
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => $id]);
$post = $stmt->fetch();
echo $post->body;
// GET ROW COUNT
$stmt = $pdo->prepare('SELECT * FROM posts WHERE author = ?');
$stmt->execute([$author]);
$postCount = $stmt->rowCount();
echo $postCount;
// INSERT DATA
$title = 'Post Five';
$body = 'This is Post Five.';
$author = 'Kevin';
$sql = 'INSERT INTO posts(title, body, author) VALUES (:title, :body, :author)';
$stmt = $pdo->prepare($sql);
$stmt->execute(['title' => $title, 'body' => $body, 'author' => $author]);
echo 'Post Added!';
The only time I don’t use bound parameters is when there is absolutely no user supplied input in the query. The majority of time I write multiple lines of ->bindParam followed by ->execute() mostly because of habit and I find them easier to read.
The difference between yours and @Mittineague’s example is that he’s got the : before any key index. This is how you’re supposed to bind the parameters in PDO. If you just do
As Mittineague said, then you’ll actually be inserting or updating them yourself. So for example. If the person types in say John Smith for the author name. What will actually be inserted instead is author. Or if you insert a date for is_published, then what actually gets inserted or updated is is_published and not the actual date. So you need to use : because the distinguishes your literal interpretation from placeholders.
Is there a reason to use bindParam rather than just putting the array into the execute or is it just a preference/habit of yours?
I normally prefer the square brackets in my execute, I guess it just seems neater, but have been caught out and had to revert on a site where the client is still running PHP5.2
@gregs - For what it is worth, I never use binds. They are basically a holdover from the mysql C interface. So your first example is good.
@Mittineague - It is buried somewhere in the docs but while you need the colon in the sql statement, it is optional when it comes to array keys. 'author and ‘:author’ will both work. Just one of the PDO things to help keep you on your toes.
Having said that, named parameters are overrated and PDO specific. I always just use ? placeholders. You might think that named parameters provide better long term documentation and are easier to maintain but I found them to be more work than they are worth. Naming things is hard.
It’s not just me then, I never use them either.
That’s why I asked:-
In case I was missing something, but I guess not.
I know, I know.
When I have access to do it myself, I will switch them to a newer version. But there is one I know still on it. I should maybe have a word.
My own personal preferenece is to use bound parameters, when there is any external value being plugged into a query, no matter what the source, that way I don’t accidentally create a security hole of the source later gets changed to that supplied by a user
