2 // $Id: database.mysql.inc 144 2007-03-28 07:52:20Z thierry $
6 * Database interface code for MySQL database servers.
15 * Initialize a database connection.
17 * Note that you can change the mysql_connect() call to mysql_pconnect() if you
18 * want to use persistent connections. This is not recommended on shared hosts,
19 * and might require additional database/webserver tuning. It can increase
20 * performance, however, when the overhead to connect to your database is high
21 * (e.g. your database and web server live on different machines).
23 function db_connect($url) {
24 // Check if MySQL support is present in PHP
25 if (!function_exists('mysql_connect')) {
26 drupal_maintenance_theme();
27 drupal_set_title('PHP MySQL support not enabled');
28 print theme('maintenance_page', '<p>We were unable to use the MySQL database because the MySQL 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 = db_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 // Allow for non-standard MySQL port.
42 if (isset($url['port'])) {
43 $url['host'] = $url['host'] .':'. $url['port'];
46 // - TRUE makes mysql_connect() always open a new link, even if
47 // mysql_connect() was called before with the same parameters.
48 // This is important if you are using two databases on the same
50 // - 2 means CLIENT_FOUND_ROWS: return the number of found
51 // (matched) rows, not the number of affected rows.
52 $connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);
54 drupal_maintenance_theme();
55 drupal_set_title('Unable to connect to database server');
56 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. This could mean your hosting provider\'s database server is down.</p>
57 <p>The MySQL error was: '. theme('placeholder', mysql_error()) .'.</p>
58 <p>Currently, the username is '. theme('placeholder', $url['user']) .' and the database server is '. theme('placeholder', $url['host']) .'.</p>
60 <li>Are you sure you have the correct username and password?</li>
61 <li>Are you sure that you have typed the correct hostname?</li>
62 <li>Are you sure that the database server is running?</li>
64 <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>');
68 if (!mysql_select_db(substr($url['path'], 1))) {
69 drupal_maintenance_theme();
70 drupal_set_title('Unable to select database');
71 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>
72 <p>The MySQL error was: '. theme('placeholder', mysql_error($connection)) .'.</p>
73 <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>
75 <li>Are you sure you have the correct database name?</li>
76 <li>Are you sure the database exists?</li>
77 <li>Are you sure the username has permission to access the database?</li>
79 <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>');
83 /* On MySQL 4.1 and later, force UTF-8 */
84 if (version_compare(mysql_get_server_info(), '4.1.0', '>=')) {
85 mysql_query('SET NAMES "utf8"', $connection);
91 * Helper function for db_query().
93 function _db_query($query, $debug = 0) {
94 global $active_db, $queries;
96 if (variable_get('dev_query', 0)) {
97 list($usec, $sec) = explode(' ', microtime());
98 $timer = (float)$usec + (float)$sec;
101 $result = mysql_query($query, $active_db);
103 if (variable_get('dev_query', 0)) {
104 $bt = debug_backtrace();
105 $query = $bt[2]['function'] . "\n" . $query;
106 list($usec, $sec) = explode(' ', microtime());
107 $stop = (float)$usec + (float)$sec;
108 $diff = $stop - $timer;
109 $queries[] = array($query, $diff);
113 print '<p>query: '. $query .'<br />error:'. mysql_error($active_db) .'</p>';
116 if (!mysql_errno($active_db)) {
120 trigger_error(check_plain(mysql_error($active_db) ."\nquery: ". $query), E_USER_WARNING);
126 * Fetch one result row from the previous query as an object.
129 * A database query result resource, as returned from db_query().
131 * An object representing the next row of the result. The attributes of this
132 * object are the table fields selected by the query.
134 function db_fetch_object($result) {
136 return mysql_fetch_object($result);
141 * Fetch one result row from the previous query as an array.
144 * A database query result resource, as returned from db_query().
146 * An associative array representing the next row of the result. The keys of
147 * this object are the names of the table fields selected by the query, and
148 * the values are the field values for this result row.
150 function db_fetch_array($result) {
152 return mysql_fetch_array($result, MYSQL_ASSOC);
157 * Determine how many result rows were found by the preceding query.
160 * A database query result resource, as returned from db_query().
162 * The number of result rows.
164 function db_num_rows($result) {
166 return mysql_num_rows($result);
171 * Return an individual result field from the previous query.
173 * Only use this function if exactly one field is being selected; otherwise,
174 * use db_fetch_object() or db_fetch_array().
177 * A database query result resource, as returned from db_query().
179 * The index of the row whose result is needed.
181 * The resulting field.
183 function db_result($result, $row = 0) {
184 if ($result && mysql_num_rows($result) > $row) {
185 return mysql_result($result, $row);
190 * Determine whether the previous query caused an error.
192 function db_error() {
194 return mysql_errno($active_db);
198 * Return a new unique ID in the given sequence.
200 * For compatibility reasons, Drupal does not use auto-numbered fields in its
201 * database tables. Instead, this function is used to return a new unique ID
202 * of the type requested. If necessary, a new sequence with the given name
205 function db_next_id($name) {
206 $name = db_prefix_tables($name);
207 db_query('LOCK TABLES {sequences} WRITE');
208 $id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) + 1;
209 db_query("REPLACE INTO {sequences} VALUES ('%s', %d)", $name, $id);
210 db_query('UNLOCK TABLES');
216 * Determine the number of rows changed by the preceding query.
218 function db_affected_rows() {
220 return mysql_affected_rows($active_db);
224 * Runs a limited-range query in the active database.
226 * Use this as a substitute for db_query() when a subset of the query is to be
228 * User-supplied arguments to the query should be passed in as separate parameters
229 * so that they can be properly escaped to avoid SQL injection attacks.
231 * Note that if you need to know how many results were returned, you should do
232 * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
233 * db_affected_rows() do not give consistent result across different database
234 * types in this case.
237 * A string containing an SQL query.
239 * A variable number of arguments which are substituted into the query
240 * using printf() syntax. The query arguments can be enclosed in one
242 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
245 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
246 * and TRUE values to decimal 1.
249 * The first result row to return.
251 * The maximum number of result rows to return.
253 * A database query result resource, or FALSE if the query was not executed
256 function db_query_range($query) {
257 $args = func_get_args();
258 $count = array_pop($args);
259 $from = array_pop($args);
262 $query = db_prefix_tables($query);
263 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
266 _db_query_callback($args, TRUE);
267 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
268 $query .= ' LIMIT '. (int)$from .', '. (int)$count;
269 return _db_query($query);
273 * Runs a SELECT query and stores its results in a temporary table.
275 * Use this as a substitute for db_query() when the results need to stored
276 * in a temporary table. Temporary tables exist for the duration of the page
278 * User-supplied arguments to the query should be passed in as separate parameters
279 * so that they can be properly escaped to avoid SQL injection attacks.
281 * Note that if you need to know how many results were returned, you should do
282 * a SELECT COUNT(*) on the temporary table afterwards. db_num_rows() and
283 * db_affected_rows() do not give consistent result across different database
284 * types in this case.
287 * A string containing a normal SELECT SQL query.
289 * A variable number of arguments which are substituted into the query
290 * using printf() syntax. The query arguments can be enclosed in one
292 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
295 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
296 * and TRUE values to decimal 1.
299 * The name of the temporary table to select into. This name will not be
300 * prefixed as there is no risk of collision.
302 * A database query result resource, or FALSE if the query was not executed
305 function db_query_temporary($query) {
306 $args = func_get_args();
307 $tablename = array_pop($args);
310 $query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' SELECT', db_prefix_tables($query));
311 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
314 _db_query_callback($args, TRUE);
315 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
316 return _db_query($query);
320 * Returns a properly formatted Binary Large OBject value.
327 function db_encode_blob($data) {
329 return "'" . mysql_real_escape_string($data, $active_db) . "'";
333 * Returns text from a Binary Large Object value.
340 function db_decode_blob($data) {
345 * Prepare user input for use in a database query, preventing SQL injection attacks.
347 function db_escape_string($text) {
349 return mysql_real_escape_string($text, $active_db);
355 function db_lock_table($table) {
356 db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');
360 * Unlock all locked tables.
362 function db_unlock_tables() {
363 db_query('UNLOCK TABLES');
367 * @} End of "ingroup database".