We specify mysql as name of driver in this case and followed by database credential required.

Errors

If there is an error in database connection how can we identify so here is try/catch for error handling.

<?php
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

PDO::ERRMODE_EXCEPTION will fire exceptions when occur. Using this procedure we can handle any exception.

Fetch

There is two ways to fetch data query and execute we explain both.

1. Query

<?php
$string = 'PHPGang'; # user submitted data
try {
#connection
    $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $data = $conn->query('SELECT * FROM myTable WHERE name = ' . $conn->quote($string)); // $conn->quote used to protect SQL injection 
foreach($data as $rows) {
        print_r($rows); 
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

In this code we used query and manually escaping data with $conn->quote this method is equivalent to mysql_real_escape_string; these both function escape quote user send in data.

2. Execute

<?php
$id = 10;
try {
#connection
    $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    

    $data = $conn->prepare('SELECT * FROM users WHERE user_id = :user_id');
    $data->execute(array('user_id' => $id));

    while($rows = $data->fetch()) {
        print_r($rows);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

In this example we used prepare statement and execute it after that. In this example SQL injection is almost impossible, because $id never direct get into the query. we used :user_id as a placeholder.

$data->fetch() simply shows array of records and you can make it more stylish with FETCH_OBJ like below.

while($row = $data->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
}

Few fetch styling parameters:

PDO::FETCH_ASSOC: return array with column names.

PDO::FETCH_BOTH: Default fetch style is BOTH it return array with column name and index start with 0.

PDO::FETCH_OBJ: return anonymous object with property name.

PDO::FETCH_NUM: return array with index by column number.

Result validation with count if there is no records found in given query:

$data->execute(array('user_id' => $id));
$result = $data->fetchAll();

#count records
if ( count($result) ) {
    foreach($result as $row) {
        print_r($row);
    }
} else {
    echo "data not found.";
}

Full code of fetching:

<?php
$id = 10;
try {
#connection
  $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
  $data = $conn->prepare('SELECT * FROM users WHERE user_id = :user_id');
  $data->execute(array('user_id' => $id));

  $result = $data->fetchAll();

  if ( count($result) ) { 
    foreach($result as $row) {
      print_r($row);
    }   
  } else {
    echo "data not found.";
  }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

Insert

This example shows you how to insert record in a table and also include multiple records insertion with different data.

<?php
try {
#connection 
  $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $data = $conn->prepare('INSERT INTO users VALUES(:user_name)');
  $data->bindParam(':user_name', $name);

  $name = 'Huzoor Bux';
  $data->execute();

  #if you want to insert multiple records add new code block like this repeat this for multiple records.
  $name = 'PHPGang.com';
  $data->execute();

  $name = 'demo.phpgang.com';
  $data->execute();

#exception handiling
} catch(PDOException $e) {
  echo $e->getMessage();
}
?>

Prepare query once and execute it multiple time as you required in your script.

Insert single record and show affected rows:

$data = $conn->prepare('INSERT INTO users VALUES(:user_name)');
  $data->execute(array(
    ':user_name' => 'Huzoor Bux'
  ));

  # get the number of affected rows.
  echo $data->rowCount(); // 1

Update

<?php
$id = 10;
$name = "Codinghelptech";

try {
  $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $data = $conn->prepare('UPDATE users SET user_name = :user_name WHERE user_id = :user_id');
  $data->execute(array(
    ':user_id'   => $id,
    ':user_name' => $name
  ));

  echo $data->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}
?>

Delete

<?php
$id = 10; // select id to delete from users table

try {
  $conn = new PDO('mysql:host=localhost;dbname=myDB', $db_username, $db_password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $data = $conn->prepare('DELETE FROM users WHERE user_id = :user_id');
  $data->bindParam(':user_id', $id); // we used bindParam method
  $data->execute();

  echo $data->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}
?>

If you are still using MySQL API then you must stop and start development with PDO or MySQLi these both extensions more secure.