2 // $Id: database.mysqli.inc 144 2007-03-28 07:52:20Z thierry $
6 * Database interface code for MySQL database servers using the mysqli client libraries. mysqli is included in PHP 5 by default and allows developers to use the advanced features of MySQL 4.1.x, 5.0.x and beyond.
9 /* Maintainers of this file should consult
10 * http://www.php.net/manual/en/ref.mysqli.php
19 * Initialise a database connection.
21 * Note that mysqli does not support persistent connections.
23 function db_connect($url) {
24 // Check if MySQLi support is present in PHP
25 if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
26 drupal_maintenance_theme();
27 drupal_set_title('PHP MySQLi support not enabled');
28 print theme('maintenance_page', '<p>We were unable to use the MySQLi database because the MySQLi extension for PHP is not installed. Check your <code>PHP.ini</code> to see how you can enable it.</p>
29 <p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
33 $url = parse_url($url);
35 // Decode url-encoded information in the db connection string
36 $url['user'] = urldecode($url['user']);
37 $url['pass'] = urldecode($url['pass']);
38 $url['host'] = urldecode($url['host']);
39 $url['path'] = urldecode($url['path']);
41 $connection = mysqli_init();
42 @mysqli_real_connect($connection, $url['host'], $url['user'], $url['pass'], substr($url['path'], 1), $url['port'], NULL, MYSQLI_CLIENT_FOUND_ROWS);
44 // Find all database connection errors and error 1045 for access denied for user account
45 if (mysqli_connect_errno() >= 2000 || mysqli_connect_errno() == 1045) {
46 drupal_maintenance_theme();
47 drupal_set_title('Unable to connect to database server');
48 print theme('maintenance_page', '<p>This either means that the username and password information in your <code>settings.php</code> file is incorrect or we can\'t contact the MySQL database server through the mysqli libraries. This could also mean your hosting provider\'s database server is down.</p>
49 <p>The MySQL error was: '. theme('placeholder', mysqli_error($connection)) .'.</p>
50 <p>Currently, the username is '. theme('placeholder', $url['user']) .' and the database server is '. theme('placeholder', $url['host']) .'.</p>
52 <li>Are you sure you have the correct username and password?</li>
53 <li>Are you sure that you have typed the correct hostname?</li>
54 <li>Are you sure that the database server is running?</li>
55 <li>Are you sure that the mysqli libraries are compiled in your PHP installation? Try using the mysql library instead by editing your <code>settings.php</code> configuration file in Drupal.</li>
57 <p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
60 else if (mysqli_connect_errno() > 0) {
61 drupal_maintenance_theme();
62 drupal_set_title('Unable to select database');
63 print theme('maintenance_page', '<p>We were able to connect to the MySQL database server (which means your username and password are okay) but not able to select the database.</p>
64 <p>The MySQL error was: '. theme('placeholder', mysqli_error($connection)) .'.</p>
65 <p>Currently, the database is '. theme('placeholder', substr($url['path'], 1)) .'. The username is '. theme('placeholder', $url['user']) .' and the database server is '. theme('placeholder', $url['host']) .'.</p>
67 <li>Are you sure you have the correct database name?</li>
68 <li>Are you sure the database exists?</li>
69 <li>Are you sure the username has permission to access the database?</li>
71 <p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
76 mysqli_query($connection, 'SET NAMES "utf8"');
79 * from: http://bugs.php.net/bug.php?id=33772
80 * Write and Close handlers are called after destructing objects since PHP
81 * 5.0.5. Thus destructors can use sessions but session handler can't use
82 * objects. In prior versions, they were called in the opposite order. It
83 * is possible to call session_write_close() from the destructor to solve
84 * this chicken and egg problem.
86 register_shutdown_function('session_write_close');
92 * Helper function for db_query().
94 function _db_query($query, $debug = 0) {
95 global $active_db, $queries;
97 if (variable_get('dev_query', 0)) {
98 list($usec, $sec) = explode(' ', microtime());
99 $timer = (float)$usec + (float)$sec;
102 $result = mysqli_query($active_db, $query);
104 if (variable_get('dev_query', 0)) {
105 $bt = debug_backtrace();
106 $query = $bt[2]['function'] . "\n" . $query;
107 list($usec, $sec) = explode(' ', microtime());
108 $stop = (float)$usec + (float)$sec;
109 $diff = $stop - $timer;
110 $queries[] = array($query, $diff);
114 print '<p>query: '. $query .'<br />error:'. mysqli_error($active_db) .'</p>';
117 if (!mysqli_errno($active_db)) {
121 trigger_error(check_plain(mysqli_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
127 * Fetch one result row from the previous query as an object.
130 * A database query result resource, as returned from db_query().
132 * An object representing the next row of the result. The attributes of this
133 * object are the table fields selected by the query.
135 function db_fetch_object($result) {
137 return mysqli_fetch_object($result);
142 * Fetch one result row from the previous query as an array.
145 * A database query result resource, as returned from db_query().
147 * An associative array representing the next row of the result. The keys of
148 * this object are the names of the table fields selected by the query, and
149 * the values are the field values for this result row.
151 function db_fetch_array($result) {
153 return mysqli_fetch_array($result, MYSQLI_ASSOC);
158 * Determine how many result rows were found by the preceding query.
161 * A database query result resource, as returned from db_query().
163 * The number of result rows.
165 function db_num_rows($result) {
167 return mysqli_num_rows($result);
172 * Return an individual result field from the previous query.
174 * Only use this function if exactly one field is being selected; otherwise,
175 * use db_fetch_object() or db_fetch_array().
178 * A database query result resource, as returned from db_query().
180 * The index of the row whose result is needed.
182 * The resulting field.
184 function db_result($result, $row = 0) {
185 if ($result && mysqli_num_rows($result) > $row) {
186 $array = mysqli_fetch_array($result, MYSQLI_NUM);
192 * Determine whether the previous query caused an error.
194 function db_error() {
196 return mysqli_errno($active_db);
200 * Return a new unique ID in the given sequence.
202 * For compatibility reasons, Drupal does not use auto-numbered fields in its
203 * database tables. Instead, this function is used to return a new unique ID
204 * of the type requested. If necessary, a new sequence with the given name
207 function db_next_id($name) {
208 $name = db_prefix_tables($name);
209 db_query('LOCK TABLES {sequences} WRITE');
210 $id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) + 1;
211 db_query("REPLACE INTO {sequences} VALUES ('%s', %d)", $name, $id);
212 db_query('UNLOCK TABLES');
218 * Determine the number of rows changed by the preceding query.
220 function db_affected_rows() {
221 global $active_db; /* mysqli connection resource */
222 return mysqli_affected_rows($active_db);
226 * Runs a limited-range query in the active database.
228 * Use this as a substitute for db_query() when a subset of the query is to be
230 * User-supplied arguments to the query should be passed in as separate parameters
231 * so that they can be properly escaped to avoid SQL injection attacks.
233 * Note that if you need to know how many results were returned, you should do
234 * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
235 * db_affected_rows() do not give consistent result across different database
236 * types in this case.
239 * A string containing an SQL query.
241 * A variable number of arguments which are substituted into the query
242 * using printf() syntax. The query arguments can be enclosed in one
244 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
247 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
248 * and TRUE values to decimal 1.
251 * The first result row to return.
253 * The maximum number of result rows to return.
255 * A database query result resource, or FALSE if the query was not executed
258 function db_query_range($query) {
259 $args = func_get_args();
260 $count = array_pop($args);
261 $from = array_pop($args);
264 $query = db_prefix_tables($query);
265 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
268 _db_query_callback($args, TRUE);
269 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
270 $query .= ' LIMIT '. (int)$from .', '. (int)$count;
271 return _db_query($query);
275 * Runs a SELECT query and stores its results in a temporary table.
277 * Use this as a substitute for db_query() when the results need to stored
278 * in a temporary table. Temporary tables exist for the duration of the page
280 * User-supplied arguments to the query should be passed in as separate parameters
281 * so that they can be properly escaped to avoid SQL injection attacks.
283 * Note that if you need to know how many results were returned, you should do
284 * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
285 * db_affected_rows() do not give consistent result across different database
286 * types in this case.
289 * A string containing a normal SELECT SQL query.
291 * A variable number of arguments which are substituted into the query
292 * using printf() syntax. The query arguments can be enclosed in one
294 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
297 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
298 * and TRUE values to decimal 1.
301 * The name of the temporary table to select into. This name will not be
302 * prefixed as there is no risk of collision.
304 * A database query result resource, or FALSE if the query was not executed
307 function db_query_temporary($query) {
308 $args = func_get_args();
309 $tablename = array_pop($args);
312 $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' SELECT', db_prefix_tables($query));
313 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
316 _db_query_callback($args, TRUE);
317 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
318 return _db_query($query);
322 * Returns a properly formatted Binary Large Object value.
329 function db_encode_blob($data) {
331 return "'" . mysqli_real_escape_string($active_db, $data) . "'";
335 * Returns text from a Binary Large OBject value.
342 function db_decode_blob($data) {
347 * Prepare user input for use in a database query, preventing SQL injection attacks.
349 function db_escape_string($text) {
351 return mysqli_real_escape_string($active_db, $text);
357 function db_lock_table($table) {
358 db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');
362 * Unlock all locked tables.
364 function db_unlock_tables() {
365 db_query('UNLOCK TABLES');
369 * @} End of "ingroup database".