PHP PDO – exec (INSERT, UPDATE, DELETE) MySQL
After the connection to database is successfully created and the PDO object instance is set, the object can be used to perform SQL queries.
The SQL queries with PDO can be made in two ways:
– directly using ” exec() “, and ” query() ” methods,
– or with the prepare(). execute() statement.
The first variant is more simple, in this lesson it’s presented the exec method.
The queries that modify rows in the table, but not return a result set with rows and columns (INSERT, UPDATE, and DELETE), are send with exec(). this method returns the number of affected rows, or FALSE on error.
$count = $conn- exec(“SQL Query”);
Queries that select rows (SELECT) and return a result set with rows and columns are sent with the query() method. In case of error, returns FALSE.
$res = $conn- query(“SQL Query”);
To work with databases in PHP, you must know the specific SQL queries as: CREATE TABLE, INSERT, SELECT, UPDATE, etc.
These queries are send as a string to the MySQL server.
Create MySQL table
To create a table in a MySQL database, use the ” CREATE TABLE `table_name` ” query, and the exec() method:
– All these instructions are added after the PDO object is created, containing the connection to MySQL database.
In the next example it is created in a database named “tests” a table named “sites”, with 4 colummns: “id”, “name”, “category”, and “link”. – If the table is created, the code above will display:
The sites table is created
– The instruction: ” exec(“SET CHARACTER SET utf8″) ” sets the transfer of data between PHP and MySQL to be made with UTF-8 encoding. It is advisable to add this instruction especialy when working with data containing diacritics or special characters, but the PHP script should also contain this header: header(‘Content-type: text/html; charset=utf-8’); .
Once the MySQL table is created, you can add rows with data. To add data into a table, use an INSERT command, in the exec() method.
$objPDO- exec(“INSERT INTO `table_name` (`column1`, `column2`. ) VALUES (‘value1’, ‘value2’. )”);
Example: – This code adds 3 rows in the “sites” table. The $count variable stores the number of affected rows (added).
This script will display:
Number of rows added: 3
The “sites” table will contain these data:
To get the last auto-inserted “id” (in a AUTO_INCREMENT PRIMARY KEY column), use the lastInsertId() method.
– When you add multiple rows in the same INSERT query, this method will return the ID of the first added row.
UPDATE. and DELETE are SQL instructions that changes data in a table, but not return a result set with rows and columns. They can be executed in the same way as INSERT, with the exec() method.
The data in the rows of a MySQL table can be modified with the SQL command INSERT .
$objPDO- exec(“UPDATE `table_name` SET `column1`=’value1′, `column2`=’value2′ WHERE condition “);
The next example changes data in the columns “name” and “link”, where “id” is 3; in the “sites” table (created with the code above). – Result:
Affected rows. 1
Sometimes an UPDATE query not affect any row (if the condition not matches), and will return 0. So, it is indicated to use this statement to check the result: if($count !== false) .
The DELETE instruction deletes rows in a table.
The next example deletes all the rows in the “sites” table where the value in “category” column is ” education ” or ” programming “. – Result:
AAffected rows: 2
– In the next lesson you can learn how to select and get data stored in a MySQL table, using the PDO query() method.