Insert new data using PDO

Inserting data into database is the most common operation. Using PDO this is just two step process. in this article lets see how to inset data using PDO 

Bellow is very simple insert code.. lets look at it..

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table VALUES('my php pages')");
$STH->execute(); 

This operation can be done using exec() function also. but it is good to use prepare method because it helps to protect you from sql injections attack. for this protection we have use placeholders. it automatically making data used in the placeholders safe from sql injection attacks.

We can divided Prepare placeholders into three types

1. No placeholders
2. Unnamed placeholders
3. Named placeholders

No placeholders

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES('my php pages',123)");
$STH->execute(); 

Unnamed placeholders

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(?,?)");
$STH->execute(); 

Named placeholders

//STH means Statement Handler 
$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(:name,:id)");
$STH->execute(); 

Setting data values to Unnamed and Named Placeholders

Lets see how to set data values to Unnamed Placeholder. there is two methods we can use, one is assign values one buy one to variables or assign values to array.

Unnamed Placeholders

Assign values to each placeholder using variable

//Add variable to each placeholder
$SDH->bindParam(1,$name);
$SDH->bindParam(2,$id);

//insert new row
$name = 'my php pages PAGE 1';
$id = 1;
$SDH->execute();

//insert another row with different values
$name = 'my php pages PAGE 2';
$id = 2;
$SDH->execute();

Use an array to set data to placeholder

$data = array('my php pages PAGE 1',1);

$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(?,?)");
$STH->execute($data);

Named Placeholders

Assign values to each placeholder using variable

//Add variable to each placeholder
$SDH->bindParam(':name',$name);
$SDH->bindParam(':id',$id);

//insert new row
$name = 'my php pages PAGE 1';
$id = 1;
$SDH->execute();

//insert another row with different values
$name = 'my php pages PAGE 2';
$id = 2;
$SDH->execute();

Use an array to set data to placeholder

$data = array('name' => 'my php pages PAGE 1', 'id' = > 1);

$STH = $DBH->prepare("INSERT INTO my_table(name,id) VALUES(:name,:id)");
$STH->execute($data);

Read More..

Exceptions and PDO

PDO can use exceptions and handle errors. Every time when you use PDO it is good to use it with in try catch block. there is three error modes available in PDO and you can set the error mode attribute on your database.

Lets look at the code.

$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );  
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );  
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); 

No matter what exception mode you set up it will always return an exception value and it must be inside try catch blocks.

Lets see error modes one by one.

PDO::ERRMODE_SILENT

This is the default error mode. If you leave it in this mode, you’ll have to check for errors in the way you’re probably used to if you used the mysql or mysqli extensions. The other two methods are more ideal for DRY programming.

PDO::ERRMODE_WARNING

This mode will issue a standard PHP warning, and allow the program to continue execution. It’s useful for debugging.

PDO::ERRMODE_EXCEPTION

This is the mode you should want in most situations. It fires an exception, allowing you to handle errors gracefully and hide data that might help someone exploit your system. Here’s an example of taking advantage of exceptions:

setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
  
  // Note : Typed DELECT instead of SELECT syntax  
  $DBH->prepare('DELECT name FROM people');  
}  
catch(PDOException $e) {  
    echo "Error Occurred : ".$e->getMessage();  
}  

Read More..

Introduction To PDO (PHP Data Objects)

Most of the PHP developers using Mysql or Mysqli php functions when they work with databases. but PHP has introduced new extension call PDO in PHP 5.1. this methord is way better and very productive. PDO is stands for PHP Data Objects. 

Note : Mysql functions is removed from PHP 5.5. so its better to use PDO or Mysqi. I strongly recommend to use PDO.

PDO Supported Databases Drivers List

You can use bellow code to find which driver is installed on your system.

 print_r(PDO::getAvailableDrivers()); 

1. PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )
2. PDO_FIREBIRD ( Firebird/Interbase 6 )
3. PDO_IBM ( IBM DB2 )
4. PDO_INFORMIX ( IBM Informix Dynamic Server )
5. PDO_MYSQL ( MySQL 3.x/4.x/5.x )
6. PDO_OCI ( Oracle Call Interface )
7. PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )
8. PDO_PGSQL ( PostgreSQL )
9. PDO_SQLITE ( SQLite 3 and SQLite 2 )
10. PDO_4D ( 4D )

Lets Connect to the database using PDO

Note :  Different database drivers may have different connection methods. i am not going to all of them i will just explain how to connect with most common databases.

Connect Mysql using PDO_Mysql

$user = "root";
$pass="";
$host="localhost";
$dbname="my_db";
try
{
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOexception $e)
{
echo $e->getMassage();
}

Connect Ms Sql Server Using PDO

$user = "root";
$pass="";
$host="localhost";
$dbname="my_db";
try
{
$DBH = new PDO("mssql:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOexception $e)
{
echo $e->getMassage();
}

Connect Sybase using PDO

$user = "root";
$pass="";
$host="localhost";
$dbname="my_db";
try
{
$DBH = new PDO("mybase:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOexception $e)
{
echo $e->getMassage();
}

Connect SQLite using PDO

try
{
$DBH = new PDO("sqlite:my/database/path/database.db");  
}
catch(PDOexception $e)
{
echo $e->getMassage();
}

Close PDO Connection

$DBH = null;

Every time you use PDO, use it inside try catch statement. cheers...

Read More..

Update & Delete Mysql Data using PHP

You can easily update and delete data in mysql tables using php. This update & delete functionality is almost same as insert data. just sql query is difference.

Lets look at how to update data. for this you have to use UPDATE sql statement. lets look at the code below.

//make connection
$con = mysql_connect("localhost","root","123");
//check connection
if(!$con)
{
die('Could not connect to the database:'.mysql_error());
}

//select database
$db=mysql_select_db("my_database",$con);
 if(!$db)
{
die('Could not connect to the database:'.mysql_error());

}
//making sql update query string.
$sql="UPDATE my_table SET name = 'Nimal' WHERE tp_num = 0771239845";

//Execute the mysql query and check is execution process is succeeded
$values = mysql_query($con,$sql);
if(isset($values))
{
echo "Successfully Updated..!";
}
else
{
die('Error Occurred'.mysql_error($con));
}

//close connection
mysql_close($con);

I think above script is not hard to understand  so lets move to the Delete statement. to delete a value we need to use sql DELETE statement. lets roll to the cording part.

//make connection
$con = mysql_connect("localhost","root","123");
//check connection
if(!$con)
{
die('Could not connect to the database:'.mysql_error());
}

//select database
$db=mysql_select_db("my_database",$con);
 if(!$db)
{
die('Could not connect to the database:'.mysql_error());

}
//making sql delete query string.
$sql="DELETE FROM my_table WHERE tp_num = 0771239845";

//Execute the mysql query and check is execution process is succeeded
$values = mysql_query($con,$sql);
if(isset($values))
{
echo "Successfully Deleted..!";
}
else
{
die('Error Occurred'.mysql_error($con));
}

//close connection
mysql_close($con);

So basically we have covered basic things that you can do using mysql and php. On this tutorials we've been using mysql_xxx functions but PHP has introduce new set of functions like mysqli_xxx and PDO. we will go through those functions here after.Cheers...

Read More..

Retrieve Data From Mysql Database Using PHP

Today we gonna talk about how to retrieve data from a mysql database. in last three lessons we learned about how to create a connection to database, how to create database & tables and how to insert data. if you have any problem with those things you better read few last post before go through this. ok lets begin.

In this case we have to use Sql SELECT statement through mysql_query() function to fetch the data from mysql tables. There is several options that you can use to fetch data from mysql.

The most common function to use for this is mysql_fetch_array(). this function can return numeric array or associated array maybe in some cases it can return both types. it will return false if there is no values.

Lets look at the sample code below.

//make connection
$con = mysql_connect("localhost","root","123");
//check connection
if(!$con)
{
die('Could not connect to the database:'.mysql_error());
}

//select database
$db=mysql_select_db("my_database",$con);
 if(!$db)
{
die('Could not connect to the database:'.mysql_error());

}
//making sql query string. This table has two columns as name and tp_num (telephone number)
$sql="SELECT * FROM my_table";

//Execute the mysql query and check is execution process is succeeded
$values = mysql_query($con,$sql);
if(isset($values))
{


      while($row=mysql_fetch_array($values))
        {

         echo "Name :".$row['name'];
         echo "Contact Number :".$row['tp_num'];
         echo "<hr/>";
         }
 

mysql_free_result($value);

}
else
{
die('Error Occurred'.mysql_error($con));
}

//close connection
mysql_close($con);

We have use mysql_fetch_array() function here. i have sent only one argument to it in my case. but it can handle two paremeter inputs.  mysql_fetch_array(return_value_of_mysql_query(), MYSQL_ASSOC or MYSQL_NUM).. second argument is optional it can be  MYSQL_ASSOC or MYSQL_NUM.

MYSQL_ASSOC - If you used  MYSQL_ASSOC as second argument function will return row as an associated array.

MYSQL_NUM - If you used MYSQL_NUM as second argument it will return row with numeric index.

There is a another function that you can use for fetch data from mysql. that is mysql_fetch_assoc() it's also return row as an associated array.

You can see that at the end of while loop i have used new function call  mysql_free_result(). this function is use to release cursor memory. it is good practice to release memory at the end.

Try to play with codes and try to do this by your own. Cheers.. 

Read More..