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.