-
Notifications
You must be signed in to change notification settings - Fork 1
Class: db File: e107_handlers/mysql_class.php
The db class provides public functions that allow reading, inserting, updating and deleting data to/from database tables.
Generally, you should create a new instance of this class in your code:
e107 creates the global variables $sql and $sql2 which may often be used. However, be aware that other code may use these variables too, which will corrupt your copy. This can happen if your code calls other core or 3rd party code. If in doubt, it is always safer instantiating your own instance of the class (preferably not called $sql or $sql2!).
Once you have an instance of the class you can call the public functions on it in the usual way:
To allow sharing of a single database, all the tables in an E107 system are assigned a prefix, which is set during installation.
This prefix is available to the coder as the 'MPREFIX' constant. Many of the database access routines automatically prepend this constant to each table name. In other cases (for example when specifying database amendments in code elsewhere), it is up to the coder to add this prefix.
In some database functions (specifically noted) a '#' in front of the table name is replaced by the prefix and any language-related prefix during execution. However note that the '#' must be preceded by a space, and the word immediately following '#' must be followed by a space (not 'end of string') - this condition will tend to be met automatically in many circumstances. As from 0.7.9, it is also preferred that the table name is enclosed in backticks, thus: `#table_name` - this reduces the probability of an erroneous substitution of the prefix. Usage must be consistent within a query - either all table names must be in backticks, or none.
boolean/string $debug - Defaults to FALSE.
When this value is set to true the generated SQL is displayed at the end of the page.
When this value is set to 'now' the generated SQL is displayed on the page at the point it is executed.
When this value is set to anything else (apart from FALSE) the generated SQL is displayed at the end of the page.
Note: at the time of writing (e107 0.7.2) true and 'now' both give the same output - generated SQl is displayed in-line and at end of the page.
From a coding point of view, it is useful to always supply this parameter for all your db class calls as a variable (e.g. $mydebug). This allows you to turn all debugging on simply by setting the variable to TRUE then turn it off when your code is released but still leave the option to turn it back on for bug investigation.
string $log_type - Defaults to ‘‘.
If set to anything other than an empty string then a record will be written to e107's dblog table detailing the SQL query that was executed.
You should set this to something that is fairly unique to allow easy recognition of dblog entries for your code, it can be up to 60 characters long.
When set, you should add extra information, as appropriate, using the $log_remark parameter.
string $log_remark - Defaults to ‘‘.
Used when records are written to e107's dblog table. See the $log_type parameter.
db constructor gets language options from the cookie or session.
Closes the mySQL server connection. Only required if you open a second connection. Native e107 connection is closed in the footer.php file
### db_Connect()
Connects to mySQL server and selects database.
Parameters:
string $mySQLserver - IP Or hostname of the MySQL server
string $mySQLuser - MySQL username
string $mySQLpassword - MySQL Password
string $mySQLdefaultdb - The database schema to connect to
string $newLink [added for 2.0] - if TRUE, forces creation of a new DB link. Default FALSE
string $mySQLPrefix [added for 2.0] - allows selection of a non-standard DB prefix. Defaults to MPREFIX
Returns:
null if successful (0.7.x and 1.0.x)
TRUE if successful (2.0 on)
string error code if failed
For E107 version 0.7.8 and earlier, it is only possible to connect to one database at a time using this class. E107 sets up the connection during initialisation, and all subsequent db objects share this connection. Any use of db_Connect() to connect to a different database will affect all current and subsequent db objects.
For E107 version 0.7.9 onwards, it is possible to connect to several different databases simultaneously.
If you create a db object and do not execute db_Connect() for it, it will normally use the default E107 database.
To force a different database, execute db_Connect() with the appropriate credentials.
### db_Count()
Count the number of rows in a select.
Parameters:
string $table - the database table to be accessed
string $fields - Defaults to '(*)'.
string $arg - additional SQL arguments, usually a WHERE clause (SQL keywords should be included). Defaults to ‘‘.
boolean $debug - set to TRUE to turn on debug information. Defaults to FALSE.
string $log_type - Defaults to ‘‘.
string $log_remark - Defaults to ‘‘.
Returns:
number of affected rows as an integer or false on error
### db_Delete()
Delete rows from a database table.
Parameters:
string $table - the database table to be accessed
string $arg - additional SQL arguments, usually a WHERE clause without the WHERE keyword. Defaults to ‘‘.
boolean $debug - set to TRUE to turn on debug information. Defaults to FALSE.
string $log_type - Defaults to ‘‘.
string $log_remark - Defaults to ‘‘.
Returns:
number of affected rows as an integer, or false on error
### db_Fetch()
Fetch the first/next row from a database table.
The SQL that determines what will be returned should have already been set up via a call to db_Select().
This call is often called in a loop to fetch and process multiple rows that satisfy the SQL criteria.
Parameters:
MYSQL_BOTH (Default for 0.7.x, 1.0.x), returns a combination of associative and numeric arrays
or
MYSQL_NUM, returns a numeric based array
or
MYSQL_ASSOC (default for 2.0), returns an associative array
Returns:
array of row values or FALSE when no more rows.
### db_getList()
Returns fields as a structured array.
This API should be called after a db_Select() call. Basically, it does the job of calling db_Fetch() repeatedly, storing the results in a structured array that is returned to the caller.
Parameters:
string $fields - an array of field names (AKA database column names) to retrieve. Defaults to 'ALL'.
boolean $amount - the exact number of records to return in the array, ignored if FALSE. Defaults to FALSE.
integer $maximum - the maximum number of records to return in the array. Defaults to 200.
boolean $ordermode - Determines how the returned array is structured. When FALSE the array is indexed numerically, starting at 1, when set to a column number or column name the array is indexed by the value of that column returned for each row (Note: when you use this option you must be sure that the column values are unique otherwise some records will be lost). Defaults to FALSE.
Returns:
array of results, empty if no records retrieved
Notes:
- Using this function will produce memory-inefficient code, particularly compared with code that uses db_Fetch() to get results - because it returns an entire result set, rather than one row at a time - this is especially true with large results.
- This command will produce an associative array only. (MYSQL_ASSOC, see db_Fetch)
### db_Insert()
Insert one or more rows into a database table.
Parameters:
string $table - the database table to be accessed
string $arg - table column values, formatted as they would be for an SQL command. That means strings must be quoted, special characters escaped, etc.
boolean $debug - set to TRUE to turn on debug information. Defaults to FALSE.
string $log_type - Defaults to ‘‘.
string $log_remark - Defaults to ‘‘.
Returns:
Last insert ID as an integer or false on error
**Added in 2.0**
In 2.0, you can now pass $arg as an array of arrays - these define the data, the field types and (for updates) the 'WHERE' clause.
The first level array keys are:
'data' - an array of data (key is the field name, value is the data)
'_FIELD_TYPES' - an array of field types
'WHERE' - the records to update (only rquired on the db_Update() method; ignored on db_Insert() ).
This defines the type of each field, which determines the detail of how the field is saved, and also optionally instructs the function to sanitize the data before attempting to add it to the database. There are currently 5 supported field types:
'int' - Value of the data will be cast as int (integer)
'cmd' - Field assumed to be a mysql command - left unchanged
'string' - Field will only have single quotes added surrounding the value.
'escape' - Field value will be passed through mysql_real_escape_string(), and be surrounded by single quotes.
'todb' - Field value will be passed through the e107 toDB() function, and be surrounded by single quotes.
- The default type can be set using the special key of _DEFAULT; if unset then toDB() will be used as default type for all fields not having their types specified.
As an exception, if the value of the field is set to 'NULL', NULL is always used in the query, regardless of any field type which is set.
Multi-language Query Function.
Parameters:
string $query -
boolean $debug -
### db_QueryCount()
Returns the total number of database queries made so far.
This is a count of all database queries so far for the current page, not just the queries for the instance you are querying.
Returns:
Returns the total number of database queries made so far as an integer.
### db_Select()
Performs a select on the database table using the supplied criteria.
To actually retrieve the individual rows you need to use db_Fetch() or db_getList().
Parameters:
string $table - the database table to be accessed (without the prefix)
string $fields - A comma delimited list of fieldnames (Defaults to '*'.)
string $arg - additional SQL arguments, usually a WHERE clause without the WHERE keyword (see $mode parameter). Defaults to ‘‘.
string $mode - $arg string includes the WHERE keyword (anything other than 'default, 'no-where' is considered good for self commenting code) or not ('default'). Defaults to 'default' which will adds the WHERE keyword for you.
boolean $debug - set to TRUE to turn on debug information. Defaults to FALSE.
string $log_type - Defaults to ‘‘.
string $log_remark - Defaults to ‘‘.
Returns:
The number of rows selected as an integer or false on error
### db_Update()
Update fields in a database table corresponding to the supplied arguments.
Parameters:
string $table - the database table to be accessed
string $arg - the SQL detailing the column names and new values and a WHERE clause indicating which row(s) to update.
boolean $debug - set to TRUE to turn on debug information. Defaults to FALSE.
string $log_type - Defaults to ‘‘.
string $log_remark - Defaults to ‘‘.
Returns:
The number of affected rows as an integer or false on error
**Note:** because mysql returns zero, if no row was updated ( example: nothing changed editing a form ), db_Update will also return zero.
If you do a simple if($sql->db_Update()) in this case it will be interpreted as if(0), which in turn is false, although the update went well. So if you want to know it the update was successful you will have to test with :
**Added in 2.0**
In 2.0, you can now pass $arg as an array of arrays - these define the data, the field types and (for updates) the 'WHERE' clause.
The first level array keys are:
'data' - an array of data (key is the field name, value is the data)
'_FIELD_TYPES' - an array of field types
'WHERE' - the records to update (only rquired on the db_Update() method; ignored on db_Insert() ).
This defines the type of each field, which determines the detail of how the field is saved, and also optionally instructs the function to sanitize the data before attempting to add it to the database. There are currently 5 supported field types:
'int' - Value of the data will be cast as int (integer)
'cmd' - Field assumed to be a mysql command - left unchanged
'string' - Field will only have single quotes added surrounding the value.
'escape' - Field value will be passed through mysql_real_escape_string(), and be surrounded by single quotes.
'todb' - Field value will be passed through the e107 toDB() function, and be surrounded by single quotes.
- The default type can be set using the special key of _DEFAULT; if unset then toDB() will be used as default type for all fields not having their types specified.
As an exception, if the value of the field is set to 'NULL', NULL is always used in the query, regardless of any field type which is set. This is almost identical to passing an array with the db_Insert() command, except you will need to set the special 'WHERE' key in the array.
Making a brief appearance in 2.0 - replaced by latest db_Update();
Update fields in a database table corresponding to the supplied arguments.
Parameters:
string $table - the database table to be accessed
string $vars - a list of values to update. This may either be an array of fieldname=>value pairs, or various string alternatives acceptable to mySQL.
string $arg - the WHERE clause indicating which row(s) to update. Defaults to empty string.
boolean $debug - set to TRUE to turn on debug information. Defaults to FALSE.
string $log_type - Defaults to ‘‘.
string $log_remark - Defaults to ‘‘.
Returns:
The number of affected rows as an integer or false on error
**Note:** because MySQL returns zero, if no row was updated ( example: nothing changed editing a form ), db_UpdateArray will also return zero.
If you do a simple if($sql->db_UpdateArray()) in this case it will be interpreted as if(0), which in turn is false, although the update went well. So if you want to know it the update was successful you will have to test with :
### db_Select_gen()
Parameters:
string $query - any generic SQL statement, which can (but does not exclusively have to) return row data.
boolean $debug - set to TRUE to turn on debug information. Defaults to FALSE.
With this function you can execute any sql commands; the syntax being broadly as accepted by the mySQL engine.
The '#' before a table name is converted to specify the database prefix, and any language-related prefix. (If MPREFIX alone is used, rather than '#', multi-language sites may not work as expected). As from 0.7.9, it is also preferred that the table name is enclosed in backticks, thus: `#table_name` - this reduces the probability of an erroneous substitution of the prefix. Usage must be consistent within a query - either all table names must be in backticks, or none.
If using db_Select_gen(), any reference to database fields in the query should use an alias (rather than multiple occurrences of '#table_name') - this is probably a little faster and clearer, and also ensures the '#' is correctly handled.
The following is an example (for accessing the download table):
Note the use of 'AS' to specify an alias for each table.
In 2.0 onwards, SQL_CALC_ROWS can be added to a query - it returns total number of rows which would match the search criteria in the absence of a LIMIT phrase. If used, the value is automatically retrieved and placed into $sql->total_results, so it can be read by the caller. (This often saves a query, since otherwise it is not uncommon to have to do a db_Count() as well as the query proper).
Internal, or private, functions should not be called by 3rd party code.
Category:API Category:PluginWriting Category:Development Category:Database Category:Tables