Php CodeIgniter Study Notes

Before using the database, we 'd better automatically connect the database: config/autoload. php automatically loads $ autoload ['libraries'] = array ('database ');
Some common functions

Select data
$ This-> db-> select ();
Allow youSQLWrite in QuerySELECT.
$ This-> db-> where ();
$ This-> db-> or_where ();
$ This-> db-> where_in ();
Allow youSQLWrite in QueryWHEREAnd otherWhereFor the statements, see the manual.
$ This-> db-> get ();
Run the SELECT query statement and return the result set. You can obtain all the data of a table.
$ This-> db-> like ();
$ This-> db-> or_like ();
$ This-> db-> not_like ();
This function allows you to generateLIKEClause, which is useful for queries. For other syntaxes, see the manual.
$ This-> db-> order_by ();
Help you set upORDERClause.
$ This-> db-> group_by ();
Allow you to writeGROUPPart:
$ This-> db-> distinct ();
Add a query statement"DISTINCT"Keywords:
$ This-> db-> having ();
Allow you to write your query statementsHAVING.
$ This-> db-> limit ();
Limit the number of results returned by the query:
$ This-> db-> select_max ();
Write"Select max (field )".
$ This-> db-> select_min ();
Write"Select min (field )".
$ This-> db-> select_avg ();
Write"Select avg (field )".
$ This-> db-> select_sum ();
Write"Select sum (field )".
$ This-> db-> join ();
Allow you to writeJOIN.
$ This-> db-> count_all_results ();
Allows you to obtain a specificActive RecordThe number of results returned by the query. AvailableActive RecordRestrict functions, suchWhere (), or_where (), like (), or_like ()And so on.

Insert data

$ This-> db-> insert ();
GenerateSQLInsert a string and perform the query. You can pass an array or an object to a function.
$ This-> db-> insert_batch ();
Insert multiple data records at a time to generateSQLInsert a string and perform the query. You can pass an array or an object to a function.
$ This-> db-> set ();
This function allows you to setInserts (Insert)OrUpdates (Update)Value. It can be used instead of directly passing arrays to insert and update functions.

Update Data

$ This-> db-> update ();
Generate and executeUpdate (Update)Statement. You can pass an array or object to this function.
$ This-> db-> update_batch ();
Generates an update string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:

Delete data

$ This-> db-> delete ();
Generate and executeDELETE (Delete)Statement.
$ This-> db-> empty_table ();
Generate and executeDELETE (Delete)Statement.
$ This-> db-> truncate ();
Generate and executeTRUNCATE (Truncation)Statement.

Chain Method

The chained method allows you to simplify your syntax by connecting multiple functions. Consider this example:
$ This-> db-> select ('title')-> from ('mytable')-> where ('id', $ id)-> limit (10, 20 );
$ Query = $ this-> db-> get ();
Description:The chain method can only bePHP 5Run the following command.

Query

$ This-> db-> query ();
To submit a query, use the following functions:
$ This-> db-> query ('your query here ');
Query ()FunctionObject (Object)Returns a database result set. When using"Read"When running the query,You can use"Show your result set"To display query results;When using"Write"When running the query,It will only be returned Based on the execution success or failure.TRUEOrFALSE.

Escape Query

$ This-> db-> escape ()This function determines the data type so that only the string type data can be escaped. In addition, it will automatically enclose the data in single quotes, so you do not need to manually add single quotes. The usage is as follows: $ SQL = "INSERT INTO table (title) VALUES (". $ this-> db-> escape ($ title ).")";

Query auxiliary functions

$ This-> db-> insert_id ()
ThisIDWhen data is insertedID.
$ This-> db-> affected_rows ()
When the write operation (Insert, updateThe number of affected rows is displayed.
$ This-> db-> count_all ();
Calculates the total number of rows in the specified table and returns the result. Write the committed table name in the first parameter.

Generate query record set

Result ()
If this method is successfully executed,ObjectArray. If an error occurs, an empty array is returned.
Result_array ()
When this method is successfully executed, the record set is returned as an associated array. An empty array is returned when a failure occurs.
Row ()
This function uses the data of the first row of the current requestObject.

You can pass Parameters(The parameter is a row index.)To obtain the data of a row. For example5Row data: $ Row = $ query-> row (4 );

Row_array ()
Functions andRow ()Same,The difference is that the function returns an array..

In addition,We can also use the following method to obtain records through a cursor:
$ Row = $ query-> first_row ()
$ Row = $ query-> last_row ()
$ Row = $ query-> next_row ()
$ Row = $ query-> previus_row ()
By default, they will returnObjectYou can also pass Parameters"Array"Easy to useArrayTo obtain data. $ Row = $ query-> first_row ('array ')
$ Row = $ query-> last_row ('array ')
$ Row = $ query-> next_row ('array ')
$ Row = $ query-> previus_row ('array ')

Auxiliary Functions of result set

$ Query-> num_rows ()
This function returns the number of rows in the current request.
$ Query-> num_fields ()
This function returns the number of fields in the current request (number of columns ):
$ Query-> free_result ()
This function will release the memory occupied by the current query and delete its associated resource IDs.

Automatic Connection

"Automatic Connection"The function is automatically instantiated when each page is loaded. To enable"Automatic Connection", You canApplication/config/autoload. phpInLibraryAdd in arrayDatabase:
$Autoload ['libraries'] = array ('database ');

Manual connection

If only a part of the page requires database connection, you can manually add the following code in the function you need or in your class for this class.
$ This-> load-> database ();

Connect to multiple databases

If you need to connect to more than one database at the same time, you can use the following methods:
$ DB1 = $ this-> load-> database ('group _ one', TRUE );
$ DB2 = $ this-> load-> database ('group _ two', TRUE );

Table Data

$ This-> db-> list_tables ();
Returns an array containing the names of all tables in the currently connected database.
$ This-> db-> table_exists ();
Sometimes, before performing an operation on a table, you can use this function to determine whether a specified table exists. Returns a Boolean value..

Database tools

Important: Before initializing the database tool class, your database driver must have been running,The tool class depends on this.
Loading tool class: $ This-> load-> dbutil ()
Once Initialization is complete, you can use$ This-> dbutilObject To access member functions:
$ This-> dbutil-> list_databases ()
$ This-> dbutil-> database_exists ();
$ This-> dbutil-> xml_from_result ($ db_result)
$ This-> dbutil-> backup ()

Database cache

Three steps are required to activate the cache:
1,Create a writable directory on the server to save the cached files.
2,In the fileApplication/config/database. phpMedium$ Db ['xxxx'] ['cachedir']Set its directory.
3,Activate the cache feature.Application/config/database. phpSet Global Options$ Db ['xxxx'] ['cache _ on'] = 'true'You can also use the method below this page to manually set.
Once activated, the cache automatically occurs when a page containing database queries is loaded.

When a database is updated, We need to delete the cached files.
$ This-> db-> cache_delete ()
Delete cached files and specific webpages. If you need to clear the cache, update your database
$ This-> db-> cache_delete ('/blog', 'comments ');
Note: what is written in the manual?$ This-> db-> cache_delete ('blog ', 'comments ');However, according to the actual test, a slash should be added before the Controller name.'/'Can be correctly executed.
$ This-> db-> cache_delete_all ()
Clear all cached files.

Database Maintenance

Note::To initialize the database maintenance class, make sure that your database driver is running because it depends on the database driver.
Use the following method to load the database maintenance class:
$ This-> load-> dbforge ()
Once initialized, you can use$ This-> dbforgeFunctions in the object category class:
$ This-> dbforge-> create_database ('db _ name ')
Allows you to create a database specified by the first parameter.
$ This-> dbforge-> drop_database ('db _ name ')
Allows you to delete a database specified by the first parameter.
$ This-> dbforge-> create_table ('table _ name ');
After declaring fields and keys, you can create a table..