PDO in PHP

PDO is advance method/way to communicate with Database like MySQL. PDO is extension which you have to enable on server to use this. There are 2 more methods like MySQLi Procedural way & MySQL Object Oriented Way.

Check this. But PDO is more secure, fast way to connect with DB. It supports multiple database & has good error handling capacity. PDO supports 12 different databases.

Check Q 4 & 5 here about basic PDO.

How to connect with MySQL using PDO?

This is a dataconnectionstring mysql:host=hostname;dbname=databasename, that you have to use when create new object of PDO class as following example.

Always create a PDO class object in try..catch block to see a proper message in case of any.

$dsn = "mysql:host=localhost;dbname=2024php";
        try {
            $pdo = new pdo($dsn, 'root', '');
        } catch (PDOException $e) {
            echo $e->getMessage();
        }

What’s special in PDO? We never pass parameter in query like name=’dilip’ or id=’100′ etc. In place of passing parameters directly in query statement we pass either named parameter like :id, :name or positional parameter as ?

Now as we are not passing parameters directly in query, we later on need to bind parameter to something while is actually passing in query statement.

Step1 is – prepare a query with named id or positional id
Step2 is – bindValue the real value to that named id
Step3 is – execute that query in real sense.

So we are using prepare(), bindValue() & execute() methods for security purpose. Of course while you make SELECT * FROM TABLENAME , no parameters are passing here so you can skip prepare, bindValue & execute and simply run query() method.

Please note execute() and query() is different methods. Both runs a query but execute() uses in prepare,bindValue and execute method while query with no parameters can directly run with query() method. query() simply return results ( like rows in case of SELECT statement) & execute returns No of affected rows. (like INSERT & UPDATE & DELETE)

Check all the list of methods of PDO class here. 3 Main classes are there 1) PDO 2) PDOStatement & 3) PDOException

Now how to fetch the data. We can use various methods to fetch the rows from result query.

fetch() is use to get next row entry only. To get all remaining rows you have to do this in while loop.

while ($rowdata = $pdoStmtObj->fetch(PDO::FETCH_ASSOC)) {
                    echo '<pre>';
                    print_r($rowdata);
                    echo '</pre>';
                }

Now FETCH_ASSOC is PDO class constant. There are other constants as well like FETCH_NUM, FETCH_ASSOC. This will change the result either in associative array or object etc.

Now to remove the while loop there is another method in PDO which is fetchall(). It don’t required while loop and it is fast.

$rowdata = $pdoStmtObj->fetchAll(PDO::FETCH_ASSOC);
                echo '<pre>';
                print_r($rowdata);
                echo '</pre>';

How to use prepare, bindValue & execute?

$query = "INSERT INTO `users` (`uid`, `uname`, `uemail`, `upassword`, `created_on`) VALUES (NULL, :uname, :uemail, :upassword, now());";
            $pdostatement = $pdo->prepare($query);

            $pdostatement->bindValue(':uname', $username);
            $pdostatement->bindValue(':uemail', $useremail);
            $pdostatement->bindValue(':upassword', $password);

            $rowoutput = $pdostatement->execute();
 
            if ($rowoutput > 0) {
                echo 'Data inserted successfully';
            }

Please note when you pass named parameter into query (:uname) do not use quote around it means we are not declaring here that it is string or date or int etc. Use that when you bind value with it.

When you insert data it returns boolean but when you update or delete data, it returns affected rows.

That’s it for now.