-
-
Notifications
You must be signed in to change notification settings - Fork 214
Class: db File: e107_handlers/mysql_class.php
-
- INCOMPLETE - needs SecretR to check interactions with $e107 **************
(But does include some of the new features of the db class itself)
-
- Also needs renaming to reflect new class name ***************
The e_db_mysql class provides public functions that allow reading, inserting, updating and deleting data to/from database tables. The class was formerly called 'db', and while deprecated, it can still be referenced by this name at present.
Generally, you should create a new instance of this class in your code:
e107 creates the shared 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 to create 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.
On multi-language sites, a further language-related prefix is added to multi-language tables (some tables are common to all languages).
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. It is also strongly recommended 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 enclosed 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 present 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.
Note also that the general e107 debug facilities can provide some database-related information.
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 ('rolling log') 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 255 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 connection yourself. 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 - if TRUE, forces creation of a new DB link. Default FALSE
string $mySQLPrefix - allows selection of a non-standard DB prefix. Defaults to MPREFIX
Returns:
TRUE if successful (0.7 returned NULL)
string error code if failed
For E107 version 0.7.9 onwards, it is possible to connect to several different databases simultaneously (since 0.7.9).
If you create a db object and do not execute db_Connect() for it, it will normally use the default E107 database and connection.
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), returns a combination of associative and numeric arrays
or
MYSQL_NUM, returns a numeric based array
or
MYSQL_ASSOC (default after 0.7), 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 no limit.
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
array|string $arg - table column values as an array, an array of arrays, or (not preferred) as a 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:
Last insert ID as an integer or false on error
You can also pass $arg as an array of arrays - these define the data, the field types, any 'NOT NULL' fields and (for updates) the 'WHERE' clause. (Not in 0.7)
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 required on the db_Update() method; ignored on db_Insert() ).
'_NOTNULL' - fields which are declared as 'NOT NULL', but for which no default is set. Only used on db_Insert(). This is an array where the key is the field name, and the data is the value to set. This value is used only if the field value is not set in the 'data' array.
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.
### 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:** the call *may* return zero, if no row was updated ( example: nothing changed editing a form ).
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 :
As with db_Insert(), you can pass $arg as an array of arrays - these define the data, the field types and the 'WHERE' clause.
The relevant 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.
Deprecated routine making a brief appearance - 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). It is also strongly recommended that the table name is enclosed in backticks (since 0.7.9), 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.
SQL_CALC_ROWS can be added to a query passed with db_Select_gen() - 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.
Multi-language Query Function.
Parameters:
string $query -
boolean $debug -
Used primarily during updates, to apply the same changes to all of a set of multi-language tables.
e107 v2.x