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_ERRMODE, PDO::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_MODE, PDO::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;}?>

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