PHP PDO - exec (INSERT, UPDATE, DELETE) MySQL

Php-mysql Course

• INSERT
• UPDATE
• DELETE

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.
• 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.

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:

$objPDO->exec("CREATE TABLE `table_name` (`column1` TYPE, `column2` TYPE, ...)");

- 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".

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Create the table
  $sql = "CREATE TABLE `sites` (
  `id` int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` varchar(70) NOT NULL DEFAULT '',
  `category` varchar(25),
  `link` varchar(100)
  ) CHARACTER SET utf8 COLLATE utf8_general_ci";
  if($conn->exec($sql) !== false) echo 'The sites table is created';       // If the result is not false, display confirmation

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}
?>

- If the table is created, the code above will display: - 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');.

INSERT

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.
Sintax:

Example:

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define an insert query
  $sql = "INSERT INTO `sites` (`name`, `category`, `link`)
    VALUES
      ('Courses - Tutorials', 'education', 'coursesweb.net'),
      ('PHP-MySQL Course', 'programming', 'coursesweb.net/php-mysql'),
      ('English Courses', 'foreign languages', 'marplo.net/engleza')";
  $count = $conn->exec($sql);

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

// If data added ($count not false) displays the number of rows added
if($count !== false) echo 'Number of rows added: '. $count;
?>

- This code adds 3 rows in the "sites" table. The $count variable stores the number of affected rows (added).
This script will display:
The "sites" table will contain these data:

| id | | name               | | category          | | link                         |
------------------------------------------------------------------------------------
| 1 | | Courses - Tutorials | | education         | | https://coursesweb.net           |
| 2 | | PHP-MySQL Course    | | programming       | | https://coursesweb.net/php-mysql |
| 3 | | English Courses     | | foreign languages | | marplo.net/engleza       |

• To get the last auto-inserted "id" (in a AUTO_INCREMENT PRIMARY KEY column), use the lastInsertId() method.

$conn->lastInsertId();

        - 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.

UPDATE

The data in the rows of a MySQL table can be modified with the SQL command INSERT.
Syntax:

The next example changes data in the columns "name" and "link", where "id" is 3; in the "sites" table (created with the code above).

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // changes data in "name" si "link" colummns, where id=3
  $sql = "UPDATE `sites` SET `name`='Spanish Course', `link`='marplo.net/spaniola' WHERE `id`=3";
  $count = $conn->exec($sql);

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

// If the query is succesfully performed ($count not false)
if($count !== false) echo 'Affected rows : '. $count;       // Shows the number of affected rows
?>

- Result:

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).
- Not:   if(!$count)

DELETE

The DELETE instruction deletes rows in a table.
Syntax:

$objPDO->exec("DELETE FROM `table_name` WHERE condition");

The next example deletes all the rows in the "sites" table where the value in "category" column is "education" or "programming".

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'tests';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Delete rows in "sites", according to the value of "category" column
  $sql = "DELETE FROM `sites` WHERE `category` IN('education', 'programming')";
  $count = $conn->exec($sql);

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

// If the query is succesfully performed ($count not false)
if($count !== false) echo 'Affected rows: '. $count;       // Shows the number of aAffected rows
?>

- Result:
- In the next lesson you can learn how to select and get data stored in a MySQL table, using the PDO query() method.

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

.some_class {
  display: list-item;
}

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net