Title
And the CrudController function to create the insert statement is,
public function add($formArray) < $title = $_POST['title']; $description = $_POST['description']; $url = $_POST['url']; $category = $_POST['category']; $dao = new Dao(); $conn = $dao->openConnection(); $sql = "INSERT INTO `tb_links`(`title`, `description`, `url`, `category`) VALUES ('" . $title . "','" . $description . "','" . $url . "','" . $category . "')"; $conn->query($sql); $dao->closeConnection(); > CURD: Update Delete jQuery AJAX function This jQuery script shows the event handling functions for raising the request to perform the database update and delete actions. It calls PHP code to invoke the update or delete action as per the request.
This request will be sent with the reference of the unique record id based on which the database update delete actions will be executed.
And the CrudController functions to perform the update and the delete actions are,
openConnection(); $sql = "UPDATE tb_links SET title = '" . $title . "' , description='" . $description . "', url='" . $url . "', category='" . $category . "' WHERE DELETE FROM `tb_links` where "; $conn->query($sql); $dao->closeConnection(); > ?> Below screenshots shows the add form and the edit modal that is used in this object-oriented crud example.
Источник
CRUD operation with PDO Database Connection
Databases are everywhere and there is no practical PHP application that could exist without a database. From the very beginning, PHP offers several ways of interfacing with all popular DBMS. For instance, two popular ways of interfacing with MySQL based databases are mysql and mysqli.
Over years, databases have come a long way and now several different vendors offer popular DBMS that power modern PHP apps. To standardize and streamline development practices, PHP introduced PHP Data Objects (PDO) in PHP 5.1. These objects are used to setup PDO database connections.
PDO is a database access layer which provides a fast and consistent interface for accessing and managing databases in PHP applications. Every DBMS has specific PDO driver(s) that must be installed when you are using PDO in PHP applications.
Supported Databases Driver nameSupported Database
PDO_CUBRID Cubrid PDO_DBLIB FreeTDS / Microsoft SQL Server / Sybase PDO_FIREBIRD Firebird PDO_IBM IBM DB2 PDO_INFORMIX IBM Informix Dynamic Server PDO_MYSQL MySQL 3.x/4.x/5.x PDO_OCI Oracle Call Interface PDO_ODBC ODBC v3 (IBM DB2, unixODBC and win32 ODBC) PDO_PGSQL PostgreSQL PDO_SQLITE SQLite 3 and SQLite 2 PDO_SQLSRV Microsoft SQL Server / SQL Azure PDO_4D 4D
By default, PHP has PDO_SQLite driver installed. However, if you wish to work with other databases, you must first install the relevant driver.
in order to check what drivers are installed on your system, create a new PHP file and add the following code snippet to it:
Working With PDO 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 that separates database related operations from the rest of the code.
You might also like: Simple CRUD in PHP and MySQL
Connectivity One of the most important benefits of PDO is the simple and very straightforward database connectivity. Consider the following code snippet that is used to set up connections with the database. Note that when the underlying DBMS changes, the only change that you need to make is the database type.
PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,); protected $con; public function openConnection() < try < $this->con = new PDO($this->server, $this->user,$this->pass,$this->options); return $this->con; > catch (PDOException $e) < echo "There is some problem in connection: " . $e->getMessage(); > > public function closeConnection() < $this->con = null; > > ?> 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.
Note: PDO can handle exceptions. Therefore, always wrap its operation in a try and catch block.
Stop Wasting Time on Servers Cloudways handle server management for you so you can focus on creating great apps and keeping your clients happy.
Creating a Table With PDO In order to create a table, first declare a query string and then execute it with exec function as no data will be returned.
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 In order to insert data into a table using PDO, first prepare the query using prepare statement. Next, this query is executed with the execute function. Note that this practice prevents SQL injection attacks.
openConnection(); // inserting data into create table using prepare statement to prevent from sql injections $stm = $db->prepare("INSERT INTO student (ID,name,last_name,email) VALUES ( :id, :name, :lastname, :email)") ; // inserting a record $stm->execute(array(':id' => 0 , ':name' => 'Saquib' , ':lastname' => 'Rizwan' , ':email' => '[email protected]')); echo "New record created successfully"; > catch (PDOException $e) < echo "There is some problem in connection: " . $e->getMessage(); > ?> Select Data With PDO In order to select data, first create a query string and then execute it in a for each loop to fetch records from the table.
openConnection(); $sql = "SELECT * FROM student " ; foreach ($db->query($sql) as $row) < echo " ID: ".$row['ID'] . " 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.
openConnection(); $sql = "UPDATE `student` SET `name`= 'yourname' , `last_name` = 'your lastname' , `email` = 'your email' WHERE `id` = 8" ; $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.
openConnection(); $sql = "DELETE FROM student WHERE `id` = 8" ; $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. If you want speed up your PDO queries with iterators, check out this article by Michelangelo van Dam.
Host PHP Websites with Ease [Starts at $10 Credit]
In this article, I introduced PDO and highlighted how you could perform CRUD actions using PDO in PHP. I also demonstrated setting up of PDO database connections. If you have questions or would like to add to the discussion, do leave a comment below.
Share This Article Customer Review at
“Cloudways hosting has one of the best customer service and hosting speed” Sanjit C [Website Developer]
Saquib Rizwan Saquib is a PHP Community Expert at Cloudways — A Managed PHP Hosting Cloud Platform. He is well versed in PHP and regularly contributes to open source projects. For fun, he enjoys gaming, movies and hanging out with friends.
Источник
PHP PDO CRUD In this tutorial we are creating an example to do database operations Create, Read, Update and Delete (CRUD) in PHP using PDO connection. In a previous tutorial, we have already learned about how to do database CRUD using MySQLi
In this example, we have taken a posts table to do CRUD via PDO. The SQL script for this table is given with the source code download. We create PDO connection in a common file and include this file wherever the database connection is required.
Creating PDO Connection The following code shows how to create PDO connection in PHP.
Create New Row in Database Table The code shows the HTML for the add form and the PHP script to be executed to get PDO connection to perform Create operation. On submitting this form, the fields are added in a new row.
prepare( $sql ); $result = $pdo_statement->execute( array( ':post_title'=>$_POST['post_title'], ':description'=>$_POST['description'], ':post_at'=>$_POST['post_at'] ) ); if (!empty($result) ) < header('location:index.php'); >> ?> Back to List
Add New Record Title:
PHP PDO Read After creating a new row in the database table, we are going to fetch all records via PDO connection and list them in a list page. The code is,
body .tbl-qa .tbl-qa th.table-header .tbl-qa .table-row td .button_link prepare("SELECT * FROM posts ORDER BY id DESC"); $pdo_statement->execute(); $result = $pdo_statement->fetchAll(); ?> Create
Title Description Date Actions '> > ?>
Update Database Row In update form, we get values from the database for a particular record and populate those values in the form fields. The code shows update query to be executed using PDO connection to do database update.
prepare("update posts set post_title='" . $_POST[ 'post_title' ] . "', description='" . $_POST[ 'description' ]. "', post_at='" . $_POST[ 'post_at' ]. "' where id"]); $result = $pdo_statement->execute(); if($result) < header('location:index.php'); >> $pdo_statement = $pdo_conn->prepare("SELECT * FROM posts where id"]); $pdo_statement->execute(); $result = $pdo_statement->fetchAll(); ?> Deleting Row using PDO The following PHP code shows how to fire delete query using PDO connection.
Источник
