How to execute a PHP PDO prepared statement

PHP, SQL
PDO

Read {count} times since 2020

PDO – a method used to execute SQL statements risk free. Fast, secure and easy when compared to mysqli and mysql functions in PHP.
A lot of newbies including me heard about PDO mostly in the 2010 – 2013 years when a lot of sites got hacked of Mysql Injection. PDO is the easiest and safest way to execute an SQL code, complete risk free.
In this post I’m gonna tell you how to execute a prepared statement in PDO and the common mistakes made by programmers which results an error while executing SQL code.

Contents

<ul class="toc_list">
  <li>
    <a href="#how-to-execute-a-prepared-statement"><span class="toc_number toc_depth_1">1</span> How to execute a prepared statement ?</a>
  </li>
  <li>
    <a href="#common-mistakes"><span class="toc_number toc_depth_1">2</span> Common mistakes</a><ul>
      <li>
        <ul>
          <li>
            <a href="#-right-way"><span class="toc_number toc_depth_3">2.0.1</span>    Right Way:</a>
          </li>
          <li>
            <a href="#-right-way-2"><span class="toc_number toc_depth_3">2.0.2</span>    Right Way:</a>
          </li>
        </ul>
      </li>
    </ul>
  </li>
</ul>

How to execute a prepared statement ?

The following example shows how to execute an INSERT statement with the credentials {database_name:"db",host:"localhost",username:"root",password:"subins",table:"users",fields:"name,age,city"}.
<?php
$name=$_POST['name'];
$age=$_POST['age'];
$city=$_POST['city'];
$db = new PDO('mysql:dbname=<span style="color: red;">db</span>;host=<span style="color: red;">127.0.0.1</span>', '<span style="color: red;">root</span>', '<span style="color: red;">subins</span>');
$sql=$db->prepare("INSERT INTO users (name,age,city) VALUES(?,?,?)");
$sql->execute(array($name,$age,$city));
?>

Common mistakes

Now the common mistakes made while executing a code like above.
1. Some will put variables inside execute function instead of putting it in array like below:

$sql->execute($name,$age,$city);

   Right Way:

$sql->execute(array($name,$age,$city));

2. The other mistake is calling a function from $db instead of $sql like the following:

$sql=$db->prepare("INSERT INTO users (name,age,city) VALUES(?,?,?)");
$db->execute(array($name,$age,$city));

   Right Way:

$sql=$db->prepare("INSERT INTO users (name,age,city) VALUES(?,?,?)");
$sql->execute(array($name,$age,$city));

Show Comments