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.
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.
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.
<?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:
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:
<?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).
| 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 |
$conn->lastInsertId();
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.
Syntax:
<?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)
The DELETE instruction deletes rows in a table.
Syntax:
$objPDO->exec("DELETE FROM `table_name` WHERE condition");
<?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:
<input type="checkbox" name="a_name" value="value" checked="checked" />
#id { background:url("path_to_image.png"); background-size:contain; background-repeat:no-repeat; }
var rest8_7 = 8 % 7; alert(rest8_7);
$nr = ceil(3.5); echo $nr; // 4