CRUD operation in Database using PHP

 PDO replaces all previous database interaction approaches. Using PDO, you could easily perform CRUD and related DBMS operations. In effect, PDO acts as a layer separating database-related operations from the rest of the code.

• Connectivity

One of the most important benefits of PDO is the simple database connectivity. Consider the following code snippet used to set up connections with the database.

<?php

$server = “localhost”;

$user = “root”;

$password = “”;

$db = “phppdo”;

try {

$dbcon = new PDO(“mysql:host=$server;dbname=$db”, $user, $password);

$dbcon->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_OBJ);

} catch (PDOException $e) {

echo “Connection Failed: “ . $e->getMessage();

}

In the above code snippet, notice that the DBMS is MySQL. However, if the DBMS changes to MS SQL Server, the only change will be the replacement of MySQL with mssql.

• Creating a Table With PDO

To create a table, first declare a query string and then execute it with the exec function as no data will be returned.

<?php

include_once ‘index.php’;

try

{

$database = new Connection();

$db = $database->openConnection();

// sql to create table

$sql = “CREATE TABLE `Student` ( `ID` INT NOT NULL AUTO_INCREMENT , `name`VARCHAR(40) NOT NULL , `last_ame` VARCHAR(40) NOT NULL , `email` VARCHAR(40)NOT NULL , PRIMARY KEY (`ID`)) “;

// use exec() because no results are returned

$db->exec($sql);

echo “Table Student created successfully”;

$database->closeConnection();

}

catch (PDOException $e)

{

echo “There is some problem in connection: “ . $e->getMessage();

}

?>

• Inserting Data With PDO

To insert data into a table using PDO, first, prepare the query using a prepared statement. Next, this query is executed with the execute function. Note that this practice prevents SQL injection attacks.

<?php

include_once ‘index.php’;

try

{

$database = new Connection();

$db = $database->openConnection();

// inserting data into create table using prepare statement to prevent from sql injections

$stm = $db->prepare(“INSERT INTO student (ID, name, last_ame, email) VALUES (:id, :name, :lastname, :email)”) ;

// inserting a record

$stm->execute(array(‘:id’ => 0, ‘:name’ => ‘Himanshi’, ‘:lastname’ => ‘Gandhi’, ‘:email’ => ‘hgandhi1810@gmail.com’));

echo “New record created successfully”;

}

catch (PDOException $e)

{

echo “There is some problem in connection: “ . $e->getMessage();

}

?>

• Select Data With PDO

To select data, first create a query string and then execute it in a for each loop to fetch records from the table.

<?php

include_once ‘index.php’;

try

{

$database = new Connection();

$db = $database->openConnection();

$sql = “SELECT * FROM student “ ;

foreach ($db->query($sql) as $row) {

echo “ ID: “.$row[‘ID’] . “<br>”;

echo “ Name: “.$row[‘name’] . “<br>”;

echo “ Last Name: “.$row[‘last_ame’] . “<br>”;

echo “ Email: “.$row[‘email’] . “<br>”;

}

}

catch (PDOException $e)

{

echo “There is some problem in connection: “ . $e->getMessage();

}

?>

• Update Data With PDO

In order to update a record in the table, first declare a query string and then execute it with exec function.

<?php

include_once ‘index.php’;

try

{

$database = new Connection();

$db = $database->openConnection();

$sql = “UPDATE `student` SET `name`= ‘Himu’ , `last_ame` = ‘Shah’ , `email` = ‘himushah57@gmail.com’ WHERE `id` = 1” ;

$affectedrows = $db->exec($sql);

if(isset($affectedrows))

{

echo “Record has been successfully updated”;

}

}

catch (PDOException $e)

{

echo “There is some problem in connection: “ . $e->getMessage();

}

?>

• Delete Data With PDO

In order to delete a record from the table, first declare a query string and then execute it with exec function

<?php

include_once ‘index.php’;

try

{

$database = new Connection();

$db = $database->openConnection();

$sql = “DELETE FROM student WHERE `id` = 1” ;

$affectedrows = $db->exec($sql);

if(isset($affectedrows))

{

echo “Record has been successfully deleted”;

}

}

catch (PDOException $e)

{

echo “There is some problem in connection: “ . $e->getMessage();

}

?>

• Conclusion

PDO is the data-accessing layer that greatly eases the process of connecting and working with databases. Perhaps, the best thing about PDO is the streamlined process of database migration

Thank You.