PDO ManualPHP TutorialsWeb Development

Run SQL queries using PDO – PHP Data Objects (PDO) Manual – Part 2

In this session you will learn how to run SQL queries using PDO class in PHP. Firstly you must be connected to  a database for running sql queries using PDO. For connecting database please follow link : https://www.devildoxx.com/blog/web-development/php-tutorials/pdo-manual/pdo-database-connection/

How to run SQL queries using PDO ?

As we compare mysql, mysqli and PDO , commands in mysql and mysqli are easier as compared to PDO. PDO has many methods to run command from easy to typical like binding parameters. Binding parameters is not available in mysql but it is available in mysqli. Basic syntax for executing SQL queries using PDO is :-

<?php
try {
   //Trying to connect database connection
   $my_connect = new PDO("mysql:host=<!--your mysql host-->;dbname=<!--your database name-->", <!--Database Username-->, <!--Database Password-->);
} catch(PDOException $e){
    //Error in connection will be here
	echo $e;
}

$my_connect->query("SELECT * FROM tablename");


?>

Just call connection variable that is $my_connect in above code and then call query function of PDO class. Pass your sql query in this query function as shown above and your query will be executed. It is similar to mysql_query and mysqli_query. Similarly you can use where, or, and etc conditions inside it . For example, suppose I want to get data from a id then

<?php
try {
   //Trying to connect database connection
   $my_connect = new PDO("mysql:host=<!--your mysql host-->;dbname=<!--your database name-->", <!--Database Username-->, <!--Database Password-->);
} catch(PDOException $e){
    //Error in connection will be here
	echo $e;
}

$my_connect->query("SELECT * FROM tablename where id = '1'");


?>

This is the basic syntax of executing SQL queries using PDO. In next step we will discuss about preparedstatement using PDO. For fetching data from above query you is almost similar mysql and mysqli. Only difference mysql, mysqli and PDO is fetching syntax.

<?php
try {
   //Trying to connect database connection
   $my_connect = new PDO("mysql:host=<!--your mysql host-->;dbname=<!--your database name-->", <!--Database Username-->, <!--Database Password-->);
} catch(PDOException $e){
    //Error in connection will be here
	echo $e;
}

$my_query = $my_connect->query("SELECT * FROM tablename where id = '1'");
$fetch_Data = $my_query->fetch(PDO::FETCH_OBJ);

?>

PDO FETCHING FORMATS IN DETAILS

In above SQL query using PDO, we just execute the query and then fetch data using PDO::FETCH_OBJ. Result will be a standard array. Similarly you can use PDO::FETCH_ASSOC  , PDO::FETCH_BOTH and many others way according to your requirement. Here are fetch formats that are available in PDO for fetching data :-

  1. PDO::FETCH_OBJ : From the name it is clear that it results in a standard object array with properties name related to table column name.
  2. PDO::FETCH_ASSOC : Using PDO::FETCH_ASSOC in SQL query using PDO will return a array with indexing of column names.
  3. PDO::FETCH_NUM : It is similar to PDO::FETCH_ASSOC only difference is that it returns array which is indexed with column number not column name.
  4. PDO::FETCH_LAZY : PDO::FETCH_BOTH and PDO::FETCH_OBJ both together comes in this format, it returns object variable names as they are accessed.
  5. PDO::FETCH_NAMED : It is also similar of PDO::FETCH_ASSOC but it can not able to return similar column name values. It means that if there two column with same name then it will show one column with combo of both rows.
  6. PDO::FETCH_BOTH : returns an array indexed by both column name and 0-indexed column number as returned in your result set

These are the common fetch formats in PDO that are widely use in SQL queries using PDO. Except these PDO fetch formats, there are other formats that can used like

Miscellaneous  PDO FETCH Formats

  1. PDO::FETCH_PROPS_LATE: when used with PDO::FETCH_CLASS, the constructor of the class is called before the properties are assigned from the respective column values.
  2. PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method
  3. PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class, and calling the constructor afterwards, unless PDO::FETCH_PROPS_LATE is also given. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.
  4. PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class.

In next article we will discuss about prepare SQL queries using PDO that are used against sql injections. And also how to bind parameters in SQL queries using PDO.

Anil Mehra

I am Anil Mehra, a passionate, workaholic and a Full Stack Tech Savvy Programmer with true north towards growth. I have worked on 256 live projects in MNC. I am expertise in the field of Programming, Server Management, SEO, Blogging and SMO...

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button