English / Deutsch | Print version
Twitter
twitter

PHP / MySQL / PDO


The MySQL extension has been removed in PHP7. You can use the MySQLi or PDO extension instead of MySQL. I prefer PDO, because you can easily change the database.

At first, it looks difficult to change to PDO, but its quite easy. I wrote a small library (pdo.php) to simplify the migration process and I give some examples how to change the PHP code.


Function list of the pdo.php library
Examples
Prevent SQL Injection
MySQL LIKE Operator in PDO with prepared statement
Debug functionality in pdo.php
PDO::FETCH_BOTH or PDO::FETCH_ASSOC
Simple example with pdo.php
Download pdo.php


Function list of the pdo.php library


 function SQL_Connect    ($user, $pass, $db, $host = "localhost", $charset = "utf8mb4");
 function SQL_Exec       ($pdo, $query, $values = false);
 function SQL_Fetch      ($pdo, $query, $values = false);
 function SQL_MultiFetch ($pdo, $query, $values = false);
 function SQL_LastID     ($pdo);
 function SQL_Error      ($e, $query);

Examples


Fetch single row

MySQL:

1
2
3
4
5
6
7
 $query = "SELECT * FROM user
WHERE id = $id
LIMIT 1";

$result = mysql_query ($query, $conn);
if (!$result) die (mysql_error());
$read = mysql_fetch_array ($result);

New:

1
2
3
4
5
 $query = "SELECT * FROM user
WHERE id = $id
LIMIT 1";

$read = SQL_Fetch ($pdo, $query);

Fetch multiple rows

MySQL:

1
2
3
4
5
6
7
8
9
10
 $query = "SELECT * FROM user
ORDER BY last_name";

$result = mysql_query ($query, $conn);
if (!$result) die (mysql_error());
while ($read = mysql_fetch_array ($result))
{
// Do something
echo $read["first_name"] . " " . $read["last_name"] . "<br />";
}

New:

1
2
3
4
5
6
7
8
9
 $query = "SELECT * FROM user
ORDER BY last_name";

$arr = SQL_MultiFetch ($pdo, $query);
foreach ($arr as $read)
{
// Do something
echo $read["first_name"] . " " . $read["last_name"] . "<br />";
}

Execute command

MySQL:

1
2
3
4
5
6
 $query = "DELETE FROM user
WHERE id = $id
LIMIT 1";

$result = mysql_query ($query, $conn);
if (!$result) die (mysql_error());

New:

1
2
3
4
5
 $query = "DELETE FROM user             
WHERE id = $id
LIMIT 1";

SQL_Exec ($pdo, $query);


Prevent SQL Injection


In MySQL, you are using 'mysql_real_escape_string()' to prevent SQL Injections. This function does not exist in PDO. But that's no problem. PDO has another mechanism. You are using prepared statements.

For prepared statements in PDO, you place '?' or named parameters in the query, instead of setting the value in the query. The values are sent by an array. You can use the array() functions of PHP, or just use '[' and ']' to create arrays.

MySQL:

1
2
3
4
5
6
 $query = "INSERT INTO user (first_name, last_name)
VALUES ('" . mysql_real_escape_string ($_POST["first_name"]) . "',
'" . mysql_real_escape_string ($_POST["last_name"]) . "')";

$result = mysql_query ($query, $conn);
if (!$result) die (mysql_error());

New:

1
2
3
4
 $query = "INSERT INTO user (first_name, last_name)
VALUES (?, ?)";

SQL_Exec ($pdo, $query, [$_POST["first_name"], $_POST["last_name"]]);

Or with named parameters:

1
2
3
4
5
6
7
8
 $query = "INSERT INTO user (first_name, last_name)
VALUES (:first_name, :last_name)";

$named_params = [ ":first_name" => $_POST["first_name"],
":last_name" => $_POST["last_name"]
];

SQL_Exec ($pdo, $query, $named_params);


MySQL LIKE Operator in PDO with prepared statement


In this example, we want all users, where the last name starts with the value in '$search'. We use 'LIKE' and '%'.

MySQL:

1
2
3
4
5
6
7
8
9
10
 $query = "SELECT * FROM user
WHERE last_name LIKE '$search%'";

$result = mysql_query ($query, $conn);
if (!$result) die (mysql_error());
while ($read = mysql_fetch_array ($result))
{
// Do something
echo $read["first_name"] . " " . $read["last_name"] . "<br />";
}

New: With the prepeared statement, you submit the '%' in the array (see line 4).

1
2
3
4
5
6
7
8
9
 $query = "SELECT * FROM user
WHERE last_name LIKE ?";

$arr = SQL_MultiFetch ($pdo, $query, ["$search%"]);
foreach ($arr as $read)
{
// Do something
echo $read["first_name"] . " " . $read["last_name"] . "<br />";
}


Debug functionality in pdo.php


There are a few options for debugging.


PDO_DEBUG is not defined (default)

On error, you get the message "SQL Error! Please contact the administrator."


PDO_DEBUG is defined

define ("PDO_DEBUG", "");

On error, you get the message "SQL Error: " with the error message.


PDO_DEBUG is defined with value 2

define ("PDO_DEBUG", "2");

On error,

  you get the message "SQL Error: " with the error message.
  you see the problematic SQL Statement.


PDO_DEBUG is defined with value 3

define ("PDO_DEBUG", "3");

On error,

  you get the message "SQL Error: " with the error message.
  you see the problematic SQL Statement.
  you see the traceback with detailed information where the error occurs, script names and lines.



PDO::FETCH_BOTH or PDO::FETCH_ASSOC


PDO::FETCH_BOTH is the default setting in PHP. When you receive a row, then every value has two entries in the array. One entry has the field name as array key, the other entry has an index number based by 0.

1
2
3
4
5
 $query = "SELECT first_name, last_name FROM user
WHERE id = $id
LIMIT 1";

$read = SQL_Fetch ($pdo, $query);

$read[0] and $read["first_name"] have the same value. For example "John".

$read[1] and $read["last_name"] have the same value. For example "Doe".

This could be important, when you use SELECT COUNT(*). With PDO::FETCH_BOTH you get $read[0] and $read["COUNT(*)"].


With PDO::FETCH_ASSOC you get only one entry per field. For example $read["first_name"], $read["last_name"], $read["COUNT(*)"].

Personally, I prefer PDO::FETCH_ASSOC and I activate it in the SQL_Connect function. Just remove the line when you want PDO::FETCH_BOTH.


Simple example with pdo.php


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?php

 
include_once ("pdo.php");


 
// We want every debug information on an error.
 
define ("PDO_DEBUG""3");


 
// Connect to the database.
 
$pdo SQL_Connect ("dbuser""secret""mydb");


 
// Add some data.
 
$query "INSERT INTO user (first_name, last_name)
           VALUES (?, ?)"
;
           
 
SQL_Exec ($pdo$query, [$_POST["first_name"], $_POST["last_name"]]);   
 

 
// Get the ID of the new entry.
 
$id SQL_LastID ($pdo);


 
// Delete the entry.
 
$query "DELETE FROM user
           WHERE  id = 
$id
           LIMIT  1"
;

 
SQL_Exec ($pdo$query);

?>


Download pdo.php


Download pdo.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
<?php
/*

  PDO SQL Routines by Elmar Hanlhofer 03/09/2018 https://www.plop.at
  Free to use. Comes without warranty.

 ------------------------------------------------------------

 function SQL_Connect    ($user, $pass, $db, $host = "localhost", $charset = "utf8mb4");
 function SQL_Exec       ($pdo, $query, $values = false);
 function SQL_Fetch      ($pdo, $query, $values = false);
 function SQL_MultiFetch ($pdo, $query, $values = false);
 function SQL_LastID     ($pdo);
 function SQL_Error      ($e, $query);

 ------------------------------------------------------------

 Define PDO_DEBUG for detailed error messages.

 PDO_DEBUG values:

    1: Print the error message.
    2: Print also the SQL Statement.
    3: Print SQL Statement and traceback with detailed information where the error occurs.

 Example: define ("PDO_DEBUG", "2");

*/

function SQL_Connect ($user$pass$db$host "localhost"$charset "utf8mb4")
{
    try
    {
        
$pdo = new PDO ("mysql:host=$host;dbname=$db;charset=$charset"$user$pass);
    }
    catch (
PDOException $e)
    {
        
SQL_Error ($e);
    }

    
$pdo->setAttribute (PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);

    
// Returned values are only indexed by column name.
    // Set it to PDO::FETCH_BOTH if you also want to have the 0-indexed column number in your result.
    
$pdo->setAttribute (PDO::ATTR_DEFAULT_FETCH_MODEPDO::FETCH_ASSOC);

    return 
$pdo;
}

function 
SQL_Exec ($pdo$query$values false)
{
    try
    {
        if (!
$values)
        {
            
$pdo->exec ($query);
        }
        else
        {
            
$stmt $pdo->prepare ($query);
            
$stmt->execute ($values);
            
$stmt null;
        }
    }
    catch (
PDOException $e)
    {
        
SQL_Error ($e$query);
    }
}

function 
SQL_Fetch ($pdo$query$values false)
{
    try
    {    
        if (!
$values)
        {
            return 
$pdo->query ($query)->fetch();
        }
        else
        {
            
$stmt $pdo->prepare ($query);
            
$stmt->execute ($values);
            
$arr $stmt->fetch ();
            
$stmt null;    
            return 
$arr;
        }
    }    
    catch (
PDOException $e)
    {
        
SQL_Error ($e$query);
    }
}

function 
SQL_MultiFetch ($pdo$query$values false)
{
    try
    {
        
$stmt $pdo->prepare ($query);
        if (!
$values)
        {
            
$stmt->execute ();
        }    
        else
        {    
            
$stmt->execute ($values);
        }
        
$arr $stmt->fetchAll ();
        
$stmt null;    
        return 
$arr;
    }    
    catch (
PDOException $e)
    {
        
SQL_Error ($e$query);
    }
}

function 
SQL_LastID ($pdo)
{
    return 
$pdo->lastInsertId();
}

function 
SQL_Error ($e$query)
{
    echo 
"<pre>";
    
    if (
defined ("PDO_DEBUG"))
    {
        echo 
"SQL Error: " $e->getMessage() . "\n\n";
    
        if (
PDO_DEBUG >= "2")
        {
            echo 
"$query\n\n";
        }
        
        if (
PDO_DEBUG == "3")
        {
            
// Print details like script name and line.
            
print_r ($e);
        }
    }
    else
    {
        echo 
"SQL Error! Please contact the administrator.";
    }

    echo 
"</pre>";

    
// Stop on error.
    
exit;
}
?>

© 2018 by Elmar Hanlhofer
This page was last modified on 03/Sep/2018.