Collection of common php adodb usage methods

CopyCode The Code is as follows: <? PHP

// Define database variables
$ Db_type = "MySQL ";
$ Db_host = "localhost ";
$ Db_user = "root ";
$ Db_pass = "";
$ Db_database = "Ai-part ";
Require_once ("../ADODB. Inc. php ");
$ Db = newadoconnection ("$ db_type"); // create a database object
$ Db-> DEBUG = true; // debug test of the database. The default value is false.
$ Adodb_fetch_mode = adodb_fetch_assoc; // return record set form, Association form
/***
Returned record set form
Define ('ADODB _ fetch_default ', 0 );
Define ('ADODB _ fetch_num ', 1 );
Define ('ADODB _ fetch_assoc ', 2 );
Define ('ADODB _ fetch_both ', 3 );
The above constant is defined in ADODB. Inc. php, that is, the "$ adodb_fetch_mode = 2" method is available.
The index in the record set returned by adodb_fetch_num is in the numerical format, that is, the sort order value of the database fields.
The index in the record set returned by adodb_fetch_assoc is the field name of the original database.
Adodb_fetch_both and adodb_fetch_default both return the preceding two types. Some databases do not support
An example:
$ Adodb_fetch_mode = adodb_fetch_num;
$ RS1 = $ db-> execute ('select * From table ');
$ Adodb_fetch_mode = adodb_fetch_assoc;
$ Rs2 = $ db-> execute ('select * From table ');
Print_r ($ RS1-> fields); # The returned array is array ([0] => 'v0', [1] => 'v1 ')
Print_r ($ rs2-> fields); # The returned array is: array (['col1'] => 'v0', ['col2'] => 'v1 ')
***/

// Connect to the database. The methods include connect, pconnect, and nconnect. Connect is generally used.
If (! @ $ Db-> connect ("$ db_host", "$ db_user", "$ db_pass", "$ db_database ")){
Exit ('<a href = "/"> the server is busy. Please visit again later </a> ');
}

/*
$ Db-> $ RS-> usage of this class
Execute ($ SQL), execute the $ SQL statement in the Parameter
Selectlimit ($ SQL, $ numrows =-1, $ offset =-1) $ numrows: returns several records, $ offset, starting from the number of records, which are generally used for paging, or use
*/
// Example: retrieve multiple records
$ SQL = "select * from Table order by ID DESC ";
If (! $ Rs = $ db-> execute ($ SQL) {// execute the SQL statement and return the result to the $ Rs variable.
Echo $ db-> errormsg (); // the error message is printed.
$ Db-> close (); // close the database
Exit ();
}
While (! $ RS-> EOF) {// traverses the record set
Echo $ RS-> fields ['username']. '<br> ';
// Try print_r ($ RS-> fields). $ RS-> fields ['field name'] returns the value in this field.
$ RS-> movenext (); // point the pointer to the next record. Otherwise, an endless loop occurs!
}
$ RS-> close (); // close to release memory

// Insert a new record
$ SQL = "Insert table (user_type, username) values (3, 'lieng eng ')";
$ Db-> execute ($ SQL );

// Update records
$ SQL = "Update table set user_type = 3 where id = 2 ";
$ Db-> execute ($ SQL );

// Delete a record
$ SQL = "delete from table where id = 2 ";
$ Db-> execute ($ SQL );

// Retrieve a single record
// $ Db-> getrow ($ SQL), obtain the first record, and return an array. If an error occurs, false is returned.
$ SQL = "select username, password, user_type from table where id = 3 ";
$ Data_ary = $ db-> getrow ($ SQL );
If ($ data_ary = false ){
Echo 'this record is not found ';
Exit ();
} Else {
Echo $ data_ary ['username']. ''. $ data_ary ['Password'].''. $ data_ary ['user _ type']. '<br> ';
}

// another method
$ SQL = "select username, password, user_type from table where id = 3";
If (! $ Rs = $ db-> execute ($ SQL) {
echo $ db-> errormsg ();
$ db-> close ();
exit ();
}< br> If (! $ Result = $ RS-> fetchrow () {
echo 'This record is not found';
exit ();
} else {
echo $ result ['username']. ''. $ result ['Password']. ''. $ result ['user _ type']. '
';
}

// Retrieve a single Field
// $ Db-> getone ($ SQL) retrieves the value of the first field of the first record. If an error occurs, false is returned.
$ SQL = "select count (ID) from table ";
$ Record_nums = $ db-> getone ($ SQL );
Echo $ record_nums;
$ SQL = "select username, password, user_type from table where user_id = 1 ";
$ Result = $ db-> getone ($ SQL );
Echo $ result; // print the username value
/*
When adding, modifying, and deleting records,
To process string fields, use $ db-> qstr () to process user input characters,
Data judgment is required for Numeric Fields
Update record. Note: This is the case where magic_quotes is set to off in PHP. ini. If you are not sure, you can use
$ Db-> qstr ($ content, get_magic_quotes_gpc ())
Note: content = the right side of the equal sign does not have single quotation marks
*/
$ SQL = "Update table set content =". $ db-> qstr ($ content). "Where id = 2 ";
$ Db-> execute ($ SQL );

/* $ Db-> insert_id (), no parameter. The id value of the record just inserted is returned. Only some databases and databases with the auto-increment function are supported, such as PostgreSQL, mySQL and MS SQL
*/
// Example:
$ SQL = "Insert table (user_type, username) values (3, 'lieng eng ')";
$ Db-> execute ($ SQL );
$ Data_id = $ db-> insert_id ();
Echo $ data_id;

/* $ Db-> genid ($ seqname = 'adodbseq ', $ startid = 1) to generate an id value. $ seqname: name of the database table used to generate this ID. $ startid: start value. Generally, you do not need to set it. It automatically adds the value in $ seqname to 1. some databases are supported. Some databases are not supported.

Insert_id, genid. Generally, I use genid, which is used only when the ID of a record is to be obtained immediately after it is inserted.
*/
/* Example:
Create a table named user_id_seq with only one field, ID, INT (10), not null, and insert a record with a value of 0.
*/
$ User_id = $ db-> genid ('user _ id_seq ');
$ SQL = "Insert table (ID, user_type, username) values (". $ user_id. ", 3, 'lieng eng ')";
$ Db-> execute ($ SQL );

/*
$ RS-> recordcount (), retrieves the total number of record sets, no Parameter
It seems that the number of retrieved records is obtained using the count () array method.
If a large amount of data is retrieved, the efficiency is slow. We recommend that you use the count (*) method in SQL.
$ SQL = "select count (*) from table". When using this method, do not add order by to SQL, which will reduce the execution speed.

Example:
*/
$ SQL = "select * from Table order by ID DESC ";
If (! $ Rs = $ db-> execute ($ SQL )){
Echo $ db-> errormsg ();
$ Db-> close ();
Exit ();
}
$ Record_nums = $ RS-> recordcount ();

/*
If you want to perform the same loop processing for a result set twice, you can use the following method:
The following is an example. It only describes how to use $ RS-> movefirst ().
*/
$ SQL = "select * from Table order by ID DESC ";
If (! $ Rs = $ db-> execute ($ SQL )){
Echo $ db-> errormsg ();
$ Db-> close ();
Exit ();
}
$ Username_ary = array ();
While (! $ RS-> EOF ){
$ Username_ary [] = $ RS-> fields ['username']
Echo $ RS-> fields ['username']. '<br>'; // print_r ($ RS-> fields). Try $ RS-> fields ['field name'] and return the value in this field.
$ RS-> movenext (); // point the pointer to the next record. If you don't need it, an endless loop will occur!
}
$ Username_ary = array_unique ($ username_ary );

$ RS-> movefirst (); // returns the pointer to the first record.
While (! $ RS-> EOF ){
Echo $ RS-> fields ['Password']. '<br>'; // print_r ($ RS-> fields). Try $ RS-> fields ['field name'] and return the value in this field.
$ RS-> movenext (); // pointer to the next record
}
$ RS-> close ();

// When this page is displayedProgramAfter the database operation is completed, $ db-> close ();
$ Db-> close ();

/* a good method */
If (isset ($ dB) {
$ db-> close ();
}< BR >?>