- PDO, connection to the database
- PDO, reading and outputting data from the database
- PDO, create a table
- PDO, inserting new records into a table (DB)
- PDO, update existing records in the table
- PDO, delete records from the table
PDO, connection to the database
Connecting to a database using PDO looks like this:
<?php
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
I think from the name of the variables it is clear what and where to substitute.
To detect errors, we will use methods try/catch:
<?php
try {
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
PDO::ERRMODE_EXCEPTION - Output exceptions when they occur.
PDO, reading and outputting data from the database
In the PDO, data can be obtained in two ways (methods):
- query
- execute
Let's consider both.
Reading and printing data using query
Immediately for example:
<?php
$q = 'devreadwrite.com';
try {
//Connection to the database
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//Get the data
$data = $dbcon->query('SELECT * FROM tableName WHERE fieldName = ' . $dbcon->quote($string));
//Output the result
foreach($data as $rows) {
print_r($rows);
}
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
$dbcon->quote - a method similar to mysql_real_escape_string.
One more example:
<?php
$q = 'devreadwrite.com';
try {
//Connection to the database
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//Get the data
$data = $dbcon->query('SELECT * FROM tableName');
//Output the result
foreach ($dbcon->query($sql) as $row) {
echo " ID: ".$row['ID'] . "<br />";
echo " Name: ".$row['name'] . "<br />";
echo " Other field: ".$row['otherField'] . "<br />";
}
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Read and print data with execute
Example:
<?php
$q = 'devreadwrite.com';
try {
//Connection to the database
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$data = $dbcon->prepare('SELECT * FROM tableName WHERE fieldName = :q');
$data->execute(array('q' => $q));
$result = $data->fetchAll();
if (count($result)) {
//Output the result
foreach($result as $row) {
print_r($row);
}
} else {
echo "No entries for output";
}
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
Here we work with the method prepare. This minimizes the possibility of SQL injection. Since $q does not fall directly into the query, and in the request we use the placeholder :q.
You can also output data in the following way:
while($row = $data->fetch(PDO::FETCH_OBJ)) {
print_r($row);
}
PDO::FETCH_OBJ - creates an anonymous object with properties that match the names of the query columns.
PDO::FETCH_ASSOC - will return an array indexed by the names of the query columns.
PDO::FETCH_BOTH - returns an array indexed by the names of the columns of the query, as well as their numbers (starting with 0). It is used by default.
PDO::FETCH_NUM - returns an array indexed by column numbers (starting with 0).
PDO, create a table
<?php
try {
//Connection to the database
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
//Table creation request
$sql = "CREATE TABLE `tableName` ( `ID` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(40) NOT NULL, `otherField` VARCHAR(40) NOT NULL, PRIMARY KEY (`ID`)) ";
$dbcon->exec($sql);
echo "Table tableName is ready for use.";
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
PDO, inserting new records into a table (DB)
We add a record to the table:
<?php
try {
//Connection to the database
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$data = $dbcon->prepare('INSERT INTO tableName VALUES(:fieldName)');
$data->bindParam(':fieldName', $fieldData);
$fieldData= 'your data';
$data->execute();
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
We add several records in a row to the table:
<?php
try {
//Connection to the database
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$data = $dbcon->prepare('INSERT INTO tableName VALUES(:fieldName)');
$data->bindParam(':fieldName', $fieldData);
$fieldData= 'your data';
$data->execute();
$fieldData= 'your data 2';
$data->execute();
$fieldData= 'your data 3';
$data->execute();
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
PDO, update existing records in the table
Example of updating records:
<?php
//Insertion data
$id = 1;
$name = "new name";
try {
$dbcon = new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$data = $dbcon->prepare('UPDATE tableName SET name = :name WHERE id = :id');
$data->execute(array(
':id' => $id,
':name' => $name
));
echo $data->rowCount(); //выведет: 1
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
PDO, delete records from the table
The last example, deleting records from the table:
<?php
$id = 1;
try {
$dbcon= new PDO('mysql:host=localhost;dbname=dbName', $dbUsername, $dbPassword);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$data = $dbcon->prepare('DELETE FROM tableName WHERE id = :id');
$data->bindParam(':id', $id);
$data->execute();
echo $data->rowCount(); //выведет: 1
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
}
I described the basic ways of working with a database through PDO, for a more detailed study, you can read the documentation: http://php.net/manual/en/book.pdo.php.