DSN connection method
DB currently supports the connection method as follows
The most complete format:
PHPTYPE (dbsyntax): // username: Password @ protocol hostspec / database? Option = Value
There are also some abbreviated formats.
PHPTYPE: // Username: Password @ protocol hostspec: 110 // USR / dB_FILE.DB
PHPTYPE: // UserName: Password @ hostspec / database
phpoType: // username: Password @ hostspec
phpoType: // username @ hostspec
PHPTYPE: // Hostspec / Database
PHPTYPE: // HostSpec
PHPTYPE: /// Database
PHPTYPE: / / / DATABASE? OPTION = Value & AnotherOption = AnotherValue
PHPTYPE (DBSYNTAX)
phpoType, phctype indicates the database type, and the options available are:
DBASE -> DBASE
FBSQL -> FrontBase
Ibase -> Interbase
IFX -> Informix
MSQL -> Mini SQL
MSSQL -> Microsoft SQL Server
MySQL -> mysql (for mysql <= 4.0)
MySQLI -> MySQL (for mysql> = 4.1) (Since DB 1.6.3)
OCI8-> Oracle 7/8/9
ODBC -> ODBC (Open Database Connectivity)
Pgsql -> PostgreSQL
SQLITE -> SQLITESYBASE -> Sybase The following is some common connection examples:
l Connect to a database of non-standard ports
PGSQL: // User: Pass @ TCP (Localhost: 5555) / PEAR
l Connect to the database via Socket
MySQL: // user @ UNIX (/ Path / TO / Socket) / PEAR
l On the Unix machine, connect the SQLite database
SQLITE: FULL / UNIX / PATH / TO / FILE.DB? Mode = 0666
l On the Windows machine, connect the SQLite database
SQLite: /// c: /full/windows/path/to/file.db? mode = 0666
l Use SSL to connect to mysqli
MySQLI: // User: Pass @ localhost / pear? key = client-key.pem & cert = client-cert.pem
l Connect to an Access database via ODBC
ODBC (Access): // username: Password @ / DataSourceName DB Connection Instance 'pgsql', 'username' => 'someuser', 'password' => 'apasswd', 'HostSpec' => 'localhost', 'Database' => 'THEDB ', * / / / $ OPTIONS is an optional $ options = array (' debug '=> 2,' Portability '=> db_portability_all,); $ db = & db :: Connect ($ DSN, $ OPTIONS) ; if (DB :: ISERROR ($ dB)) {DIE ($ db-> getMessage ());} // Setting the default query method of the database $ dB-> setfetchmode (db_fetchmode_assoc); // Perform a normal query statement $ RES = & $ db-> query ('select * from products'); // get the result of a SQL query, column number printf ("a total of% D line,% D column data", $ r-> nuMrows (), $ res-> Numcols ()); // The structure information of the current table ECHO ("
"); Print_R ($ db-> TableInfo ($ R)); ECHO ("") ; // Execute a query with parameters $ sql = 'SELECT *WHOM PRODS WHERE PRODUCTS_ID Query ($ SQL, $ DATA); // Perform more Parameters $ sql = 'SELECT * FROM PRODUCTS WHERE PRODUCTS_VOLT ?'; $ Data = array (1 0, 4000); $ RES = & $ db-> query ($ SQL, $ DATA); // Remember to have an error if you want to check the execution of the error if (DB :: ISERROR ($ RES)) {Die ($ RES) -> getMessage ());} // Press the default mode DB_FETCHMODE_ORDERED, loop display, output, and output, in this way, can only access data fields in a serial number ($ ROW = & $ RES-> FetChrow ()) {Echo $ ROW [0]. "
Fetchrow (DB_FETCHMODE_ASSOC) {Echo $ ROW ['ID']. "/ n";} // Get the specified range of row data $ from = 50; $ RESPAGE = 10; $ to = $ from $ RESPAGE; FOREACH ( Range ($ from, $ to) AS $ ROWNUM) {if (! $ row = & $ r) {botchrow ($ fetchmode, $ rownum) {breaf;} echo $ row [0]. "/ n"; / / Release Resource $ RES-> Free (); // You can get the result set of the entire query $ Result = $ db-> getAll ("Select * from tablename"); // Get update / insert / DELETE and other statements, the number of rows affected $ dB-> query ('delete * from clients'); Echo 'i Have deleted'. $ Db-> affectedRows (). 'Clients'; // Close Database Connection $ DB- > disconnect ();?> Prepare / Execute Use detailed explanation to execute a set of queries, only some parameters are different in INSERT INTO TLE_NAME (Col1, Col2) VALUES ('VAL1', VAL2); Insert Into TLE_NAME (col1, col2 VALUES ('VAL3', VAL4); ... or your statement to match multiple different grammar databases DB1: INTO TBL_NAME (COL1, COL2) VALUES (Expr1, Expr2) DB2: INSERT INTO TBL_NAME SET COL1 = EXPR1 COL2 = EXPR2 can use the Prepare / Execute statement to provide better compatibility and scalable performance using the preted / execute statement, points 2 steps 1. Prepare SQL statements with prepare (): Suppose you have a similar Such SQL statements Select Surname, Name, Agefrom Personwhere Name = 'Name_to_Find' And Ag E
?(recommend)
The standard is used instead of parameters of numbers (or characters) variables, it can automatically eACAPE or based on the current DBMS system needs to quoted data.
!
Stands for a scalar value and will insert Into The Statement "As IS"
&
Request an existing file, the content of this file will be used to replace &, often used to save binary or image content into the database
2.Execute () passes the variable to the prepare's SQL statement, then executes it, and execute () requires 2 parameters, one is the variable specified when PREPARE () is called, and the other is to pass to the variable (can be used) ) A simple example prepare ('INSERT INTO NUMBERS (NUMBER) VALUES (?)'); $ DB-> Execute ($ STH, 1); $ db-> execute ($ STH, 8);> An example of saving an image to the database hypothesized that there is a TEST table in the database, its structure is as follows: Create Table `Test` (` ID` int (11) Not Null Auto_Increment,
`Name` Varchar (100) Default Null,
`image` blob,
`description` text,
`Note` Text,
PRIMARY Key (`ID)
) Type = myisaminsert.php 2, 'Portability' => DB_PORTABILITY_ALL, $ dB = & db :: Connect ($ DSN, $ OPTION); if (DB :: ISERROR ($ dB)) {Die ($ db-> getMessage ());} $ db-> setFetchmode DB_FETCHMODE_ASSOC); $ st = $ db-> prepare ("INSERT TEST (Name, Image) VALUES (?, &)"); $ Db-> execute ($ st, array ('jxyuhua,' D: // Websamples //Pear//database//welcome.jpg ')); if (db :: isrror ($ dB)) {Die ($ db-> getMessage ());} // $ r-> free (); $ DB-> DISCONNECT ();?> After depositing the database (pay attention to the content size limit), you can then take the content to display the displayed example image.php 2, 'Portability' => db_portability_all,); $ db = & db :: Connect ($ DSN, $ OPTIONS) ; if (DB :: ISERROR ($ dB)) {DIE ($ db-> getMessage ()); $ db-> setFetchmode (db_fetchmode_assoc); $ res = $ db-> query ("SELECT Image from test where name =? Order by name ", $ _REQUEST ['Name']); if (DB :: ISERROR ($ RES)) {Die ($ RES-> getMessage ());} while ($ row = $ res-> fetchrow ()) {$ data = $ row ['image'];} Header (" Content-type: image / jpeg "); // If you are other image formats, such as GIF, you have to change image / gifecho ($ data); $ r> free (); $ dB-> disconnect );?> DB allows you to perform a set of database operations at once, for example, you have to insert / update a batch of data at once,
), Array (4, 'four', 'fire'); $ st = $ db-> prepare ('INSERT INTO NUMBERS VALUES (?,?,?) "; Foreach ($ alldata as $ row {$ DB-> Execute ($ STH, $ ROW);}?> The above statement will perform equivalent to the following SQL statement features: INSERT INTO NUMBERS VALUES ('1', 'One', 'En') Insert Into Numbers VALUES '2', 'two', 'to') INSERT INTO NUMBERS VALUES ('3', 'Three', 'Tre') Insert Into Numbers Values ('4', 'Four', 'Fire') If you want more The province, can use ExecuteMultiple () prepare ('INSERT INTO NUMBERS VALUES (?,?,?,?); $ DB-> EXECUTEMULTIPLE ($ STH, $ alldata);?> Is there such a experience, when you add (or delete) some fields in a table, your original SQL statement must be rereaded, this is Not very annoying? For example, you have a USER table, which has 3 fields (id, name, country), your previous INSERT / UPDATE statement: Insert Into User (ID, Name, Country) VALUES (?,?,? ) Update user set id = ?, name = ?, country =? Where ...
When you add a field (assuming is birthday), you must re-have your previous INSERT / UPDATE statement, and this is likely to be gueding the emergence of bugs (assuming you have changed some places). Let us now To see how to use autoprepare () / autoexecute () to solve this problem 1. AutoPrePare () with autoprepare (), you don't have to list the detailed statement of INSERT / UPDATE, and as long as it specifies its basic information Resource AutoPrepare (String $ TABLE , array $ table_fields, integer $ mode = DB_AUTOQUERY_INSERT [, string $ where = FALSE]) a list of field names (array type) table $ $ table_fields table to be manipulated $ mode (DB_AUTOQUERY_INSERT || DB_AUTOQUERY_UPDATE) $ where WHERE statement, with Filter data AutoPrepare ($ TABLE_NAME, $ TABLE_FIELDS, DB_AUTOQUERY_INSERT); if (DB :: ISERROR ($ Sth)) {Die ($ st-> getMessage ());} $ table_values = array (1, 'Fabien', ' France '); $ RES = & $ dB-> Execute ($ str, $ table_values); if (db :: iesrror ($ rES)) {Die ($ RES-> getMessage ());}?> In this example AUTOPREPARE () will automatically translate it into INSERT INTO USER (ID, Name, Country) value (?,?,?) And then automatically call prepare () Note: If you use db_autoquery_upda TE, remember to specify $ where conditions, otherwise all data will be updated .2.autoexecute () Autoexecute () is a mixture of autoprepare () and execute (), which can easily perform INSERT / UPDATEDB_RESULT AUTOEXECUTE (String $ TABLE, ARRAY $ fields_values [, integer $ mode = DB_AUTOQUERY_INSERT [, string $ where = FALSE]]) a list of field names (array type) table $ $ table_fields table to be manipulated $ mode (DB_AUTOQUERY_INSERT || DB_AUTOQUERY_UPDATE) $ where WHERE statement, with Data from filtering data 1, 'name' => 'Fabien', ' Country '=>' France '); $ RES =
$ db-> autoexecute ($ TABLE_NAME, $ FIELDS_VALUES, DB_AUTOQUERY_INSERT); if (DB :: ISERROR ($ RES)) {Die ($ RES-> getMessage ());}?> It performs equivalent to the following statement INSERT Is it very convenient for INTO User (ID, Name, Country) Values (1, 'Fabien', 'France')? Just homage! Note: If you use db_autoquery_update, remember to specify $ Where conditions, otherwise update all data .