ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:userguide:mysql_tutorial

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
v5:userguide:mysql_tutorial [2016/02/18 22:32] – ADODB -> ADOdb dregadv5:userguide:mysql_tutorial [2018/06/27 16:09] (current) – [Moving from MySQL to ADOdb] increase title level dregad
Line 3: Line 3:
 </WRAP> </WRAP>
  
-====== Tutorial ====== + 
-===== Moving from MySQL to ADOdb =====+====== Moving from MySQL to ADOdb ======
  
 <WRAP indent> <WRAP indent>
Line 24: Line 24:
 PHP is all about creating dynamic web-sites with the least fuss and the most fun. To create these websites we need to use databases to retrieve login information, to splash dynamic news onto the web page and store forum postings. So let's say we were using the popular MySQL database for this. Your company has done such a fantastic job that the Web site is more popular than your wildest dreams. You find that MySQL cannot scale to handle the workload; time to switch databases. PHP is all about creating dynamic web-sites with the least fuss and the most fun. To create these websites we need to use databases to retrieve login information, to splash dynamic news onto the web page and store forum postings. So let's say we were using the popular MySQL database for this. Your company has done such a fantastic job that the Web site is more popular than your wildest dreams. You find that MySQL cannot scale to handle the workload; time to switch databases.
  
-Unfortunately in PHP every database is accessed slightly differently. To connect to MySQL, you would use mysql_connect(); when you decide to upgrade to Oracle or Microsoft SQL Server, you would use ocilogon() or mssql_connect() respectively. What is worse is that the parameters you use for the different connect functions are different also.. One database says po-tato, the other database says pota-to. Oh-oh.+Unfortunately in PHP every database is accessed slightly differently. To connect to MySQL, you would use ''mysqli_connect();'' when you decide to upgrade to Oracle or Microsoft SQL Server, you would use ''ocilogon()'' or ''mssql_connect()'' respectively. What is worse is that the parameters you use for the different connect functions are different also.. One database says po-tato, the other database says pota-to. Oh-oh.
  
 ===== Let's NOT call the whole thing off ===== ===== Let's NOT call the whole thing off =====
Line 34: Line 34:
 ===== MySQL Example ===== ===== MySQL Example =====
  
-The most common database used with PHP is MySQL, so I guess you should be familiar with the following code. It connects to a MySQL server at localhost, database mydb, and executes an SQL select statement. The results are printed, one line per row.+The most common database used with PHP is MySQL, so I guess you should be familiar with the following code. It connects to a MySQL server at //localhost//, database //mydb//, and executes an SQL select statement. The results are printed, one line per row.
  
 <code php> <code php>
-$db = mysql_connect("localhost", "root", "password"); +// Section 1 
-mysql_select_db("mydb",$db); +$db = mysqli_connect("localhost", "root", "password"); 
-$result = mysql_query("SELECT * FROM employees",$db);+mysqli_select_db("mydb",$db); 
 + 
 +// Section 2 
 +$result = mysqli_query("SELECT * FROM employees",$db);
 if ($result === false) die("failed"); if ($result === false) die("failed");
-while ($fields = mysql_fetch_row($result)) { + 
- for ($i=0, $max=sizeof($fields); $i < $max; $i++) { +// Section 3 
- print $fields[$i].' '; +while ($fields = mysqli_fetch_row($result)) { 
- } +    for ($i=0, $max=sizeof($fields); $i < $max; $i++) { 
- print "<br>\n";+        print $fields[$i].' '; 
 +    } 
 +    print "<br>\n";
 } }
 </code> </code>
  
-The above code has been color-coded by section. The first section is the connection phase. The second is the execution of the SQL, and the last section is displaying the fields. The while loop scans the rows of the result, while the for loop scans the fields in one row.+The above code has been split in 3 sections: 
 +  - The connection phase.  
 +  - The execution of the SQL, and  
 +  - Displaying the fields. The while loop scans the rows of the result, while the for loop scans the fields in one row.
  
 Here is the equivalent code in ADOdb Here is the equivalent code in ADOdb
  
 <code php> <code php>
 +// Section 1
 include("adodb.inc.php"); include("adodb.inc.php");
-$db = NewADOConnection('mysql'); +$db = newADOConnection('mysqli'); 
-$db->Connect("localhost", "root", "password", "mydb"); +$db->connect("localhost", "root", "password", "mydb"); 
-$result = $db->Execute("SELECT * FROM employees");+ 
 +// Section 2 
 +$result = $db->execute("SELECT * FROM employees");
 if ($result === false) die("failed"); if ($result === false) die("failed");
-while (!$result->EOF)  + 
-{ +// Section 3 
-    for ($i=0, $max=$result->FieldCount(); $i < $max; $i++) +while (!$result->EOF) { 
-       print $result->fields[$i].' '; +    for ($i=0, $max=$result->fieldCount(); $i < $max; $i++) { 
-    $result->MoveNext();+        print $result->fields[$i].' '; 
 +    } 
 +    $result->moveNext();
     print "<br>\n";     print "<br>\n";
 } }
- 
 </code> </code>
    
-Now porting to Oracle is as simple as changing the second line to NewADOConnection('oracle'). Let's walk through the code...+Now porting to Oracle is as simple as changing the second line to ''newADOConnection('oracle')''. Let's walk through the code...
  
-===== Connecting to the Database =====+==== Connecting to the Database ====
  
 <code php> <code php>
 include("adodb.inc.php"); include("adodb.inc.php");
-$db = NewADOConnection('mysql'); +$db = newADOConnection('mysql'); 
-$db->Connect("localhost", "root", "password", "mydb");+$db->connect("localhost", "root", "password", "mydb");
 </code> </code>
  
-The connection code is a bit more sophisticated than MySQL's because our needs are more sophisticated. In ADOdb, we use an object-oriented approach to managing the complexity of handling multiple databases. We have different classes to handle different databases. If you aren't familiar with object-oriented programing, don't worry -- the complexity is all hidden away in the NewADOConnection() function.+The connection code is a bit more sophisticated than MySQL's because our needs are more sophisticated. In ADOdb, we use an object-oriented approach to managing the complexity of handling multiple databases. We have different classes to handle different databases. If you aren't familiar with object-oriented programing, don't worry -- the complexity is all hidden away in the ''NewADOConnection()'' function.
  
-To conserve memory, we only load the PHP code specific to the database you are connecting to. We do this by calling NewADOConnection(databasedriver). Legal database drivers include mysql, mssql, oracle, oci8, postgres, sybase, vfp, access, ibase and many others.+To conserve memory, we only load the PHP code specific to the database you are connecting to. We do this by calling ''NewADOConnection(databasedriver)''. Legal database drivers include mysql, mssql, oracle, oci8, postgres, sybase, vfp, access, ibase and many others.
  
-Then we create a new instance of the connection class by calling NewADOConnection(). Finally we connect to the database using $db->Connect().+Then we create a new instance of the connection class by calling ''NewADOConnection()''. Finally we connect to the database using ''$db%%->%%Connect()''.
  
-===== Executing the SQL =====+==== Executing the SQL ====
  
 <code php> <code php>
-$result = $db->Execute("SELECT * FROM employees"); +$result = $db->execute("SELECT * FROM employees"); 
-if ($result === false)  +if ($result === false) die("failed");
-    die("failed");+
 </code> </code>
    
-Sending the SQL statement to the server is straight forward. Execute() will return a recordset object on successful execution. You should check $result as we do above.+Sending the SQL statement to the server is straight forward. ''Execute()'' will return a recordset object on successful execution. You should check $result as we do above.
  
 An issue that confuses beginners is the fact that we have two types of objects in ADOdb, the connection object and the recordset object. When do we use each? An issue that confuses beginners is the fact that we have two types of objects in ADOdb, the connection object and the recordset object. When do we use each?
Line 102: Line 112:
 The only thing I need to add is that ADOdb provides several helper functions for making INSERT and UPDATE statements easier, which we will cover in the Advanced section. The only thing I need to add is that ADOdb provides several helper functions for making INSERT and UPDATE statements easier, which we will cover in the Advanced section.
  
-===== Retrieving the Data =====+==== Retrieving the Data ====
 <code php> <code php>
 while (!$result->EOF) while (!$result->EOF)
 { {
-   for ($i=0, $max=$result->FieldCount(); $i < $max; $i++) +    for ($i=0, $max=$result->fieldCount(); $i < $max; $i++) 
-    print $result->fields[$i].' '; +        print $result->fields[$i].' '; 
-   $result->MoveNext(); +    $result->moveNext(); 
-   print "<br>\n";+    print "<br>\n";
 } }
 </code> </code>
Line 115: Line 125:
 The paradigm for getting the data is that it's like reading a file. For every line, we check first whether we have reached the end-of-file (EOF). While not end-of-file, loop through each field in the row. Then move to the next line (MoveNext) and repeat. The paradigm for getting the data is that it's like reading a file. For every line, we check first whether we have reached the end-of-file (EOF). While not end-of-file, loop through each field in the row. Then move to the next line (MoveNext) and repeat.
  
-The $result->fields[] array is generated by the PHP database extension. Some database extensions do not index the array by field name. To force indexing by name - that is associative arrays - use the $ADODB_FETCH_MODE global variable.+The ''$result%%->%%fields[]'' array is generated by the PHP database extension. Some database extensions do not index the array by field name. To force indexing by name - that is associative arrays - use the ''$ADODB_FETCH_MODE'' global variable.
  
 <code php> <code php>
 $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
-$rs1 = $db->Execute('select * from table');+$rs1 = $db->execute('select * from table');
 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
-$rs2 = $db->Execute('select * from table');+$rs2 = $db->execute('select * from table');
 print_r($rs1->fields); // shows array([0]=>'v0',[1] =>'v1') print_r($rs1->fields); // shows array([0]=>'v0',[1] =>'v1')
 print_r($rs2->fields); // shows array(['col1']=>'v0',['col2'] =>'v1') print_r($rs2->fields); // shows array(['col1']=>'v0',['col2'] =>'v1')
 </code> </code>
  
-As you can see in the above example, both recordsets store and use different fetch modes based on the $ADODB_FETCH_MODE setting when the recordset was created by Execute().+As you can see in the above example, both recordsets store and use different fetch modes based on the ''$ADODB_FETCH_MODE'' setting when the recordset was created by ''Execute()''.
  
 ===== ADOConnection ===== ===== ADOConnection =====
Line 134: Line 144:
 ===== Other Useful Functions ===== ===== Other Useful Functions =====
  
-$recordset->Move($pos) scrolls to that particular row. ADOdb supports forward scrolling for all databases. Some databases will not support backwards scrolling. This is normally not a problem as you can always cache records to simulate backwards scrolling.+  * ''$recordset%%->%%move($pos)'' scrolls to that particular row. ADOdb supports forward scrolling for all databases. Some databases will not support backwards scrolling. This is normally not a problem as you can always cache records to simulate backwards scrolling.
  
-$recordset->RecordCount() returns the number of records accessed by the SQL statement. Some databases will return -1 because it is not supported.+  * ''$recordset%%->%%recordCount()'' returns the number of records accessed by the SQL statement. Some databases will return -1 because it is not supported.
  
-$recordset->GetArray() returns the result as an array.+  * ''$recordset%%->%%getArray()'' returns the result as an array.
  
-rs2html($recordset) is a function that is generates a HTML table based on the $recordset passed to it. An example with the relevant lines in bold:+  * ''rs2html($recordset)'' is a function that generates a HTML table based on the $recordset passed to it. An example with the relevant lines in bold:
  
 <code php> <code php>
- 
 include('adodb.inc.php'); include('adodb.inc.php');
 include('tohtml.inc.php'); /* includes the rs2html function */ include('tohtml.inc.php'); /* includes the rs2html function */
-$conn = ADONewConnection('mysql'); +$conn = newADOConnection('mysqli'); 
-$conn->PConnect('localhost','userid','password','database'); +$conn->pConnect('localhost','userid','password','database'); 
-$rs = $conn->Execute('select * from table');+$rs = $conn->execute('select * from table');
 rs2html($rs); /* recordset to html table */  rs2html($rs); /* recordset to html table */ 
 </code> </code>
Line 159: Line 168:
 Let's say you want to insert the following data into a database. Let's say you want to insert the following data into a database.
  
-  ID = 3 +<code> 
-  TheDate=mktime(0,0,0,8,31,2001) /* 31st August 2001 */ +ID = 3 
-  Note= sugar why don't we call it off+TheDate=mktime(0,0,0,8,31,2001) /* 31st August 2001 */ 
 +Note= sugar why don't we call it off 
 +</code>
  
 When you move to another database, your insert might no longer work. When you move to another database, your insert might no longer work.
  
-The first problem is that each database has a different default date format. MySQL expects YYYY-MM-DD format, while other databases have different defaults. ADOdb has a function called [[v5:reference:dbdate]] that addresses this issue by converting converting the date to the correct format.+The first problem is that each database has a different default date format. MySQL expects YYYY-MM-DD format, while other databases have different defaults. ADOdb has a function called [[v5:reference:connection:dbdate|dbDate()]] that addresses this issue by converting converting the date to the correct format.
  
-The next problem is that the don't in the Note needs to be quoted. In MySQL, we use don\'t but in some other databases (Sybase, Access, Microsoft SQL Server) we use don''t. The [[v5:reference:qstr]] function addresses this issue.+The next problem is that the //don't// in the Note needs to be quoted. In MySQL, we use ''don\'t'' but in some other databases (Sybase, Access, Microsoft SQL Server) we use ''don%%''%%t''. The [[v5:reference:connection:qStr()]] function addresses this issue.
  
 So how do we use the functions? Like this: So how do we use the functions? Like this:
Line 176: Line 187:
    . $db->dbDate($TheDate) .','    . $db->dbDate($TheDate) .','
    . $db->qStr($Note).")";    . $db->qStr($Note).")";
-$db->Execute($sql);+$db->execute($sql);
  
 </code> </code>
  
-ADOdb also supports [[v5:reference:affected_rows|$connection->affected_rows()]] (returns the number of rows affected by last update or delete) and [[v5:reference:insert_id|$recordset->insert_id() ]] (returns last autoincrement number generated by an insert statement). Be forewarned that not all databases support the two functions.+ADOdb also supports ''[[v5:reference:connection:affected_rows|$connection->affected_rows()]]'' (returns the number of rows affected by last update or delete) and ''[[v5:reference:connection:insert_id|$recordset->insert_id()]]'' (returns last autoincrement number generated by an insert statement). Be forewarned that **not all databases support the two functions**.
  
-===== MetaTypes =====+==== MetaTypes ====
  
-You can find out more information about each of the fields (I use the words fields and columns interchangebly) you are selecting by calling the recordset method FetchField($fieldoffset). This will return an object with 3 properties: name, type and max_length.+You can find out more information about each of the fields (I use the words fields and columns interchangeably) you are selecting by calling the recordset method ''FetchField($fieldoffset)''. This will return an object with 3 properties: //name//, //type// and //max_length//.
  
 For example: For example:
 <code php> <code php>
-$recordset = $conn->Execute("select adate from table"); +$recordset = $conn->execute("select adate from table"); 
-$f0 = $recordset->FetchField(0);+$f0 = $recordset->fetchField(0);
 </code> </code>
  
-Then $f0->name will hold 'adata', $f0->type will be set to 'date'. If the max_length is unknown, it will be set to -1.+Then ''$f0%%->%%name'' will hold 'adata', ''$f0%%->%%type'' will be set to 'date'. If the //max_length// is unknown, it will be set to -1.
  
-One problem with handling different databases is that each database often calls the same type by a different name. For example a timestamp type is called datetime in one database and time in another. So ADOdb has a special MetaType($type, $max_length) function that standardises the types to the following:+One problem with handling different databases is that each database often calls the same type by a different name. For example a timestamp type is called //datetime// in one database and //time// in another. So ADOdb has a special ''MetaType($type, $max_length)'' function that standardises the types to the following:
  
-  C: character and varchar types +^ MetaType ^ Description ^ 
-  X: text or long character (eg. more than 255 bytes wide). +| C | character and varchar types | 
-  B: blob or binary image +| X | text or long character (eg. more than 255 bytes wide). | 
-  D: date +| B | blob or binary image | 
-  T: timestamp +| D | date | 
-  L: logical (boolean) +| T | timestamp | 
-  I: integer +| L | logical (boolean) | 
-  N: numeric (float, double, money)+| I | integer | 
 +| N | numeric (float, double, money) |
  
 In the above date example, In the above date example,
  
 <code php> <code php>
-$recordset = $conn->Execute("select adate from table"); +$recordset = $conn->execute("select adate from table"); 
-$f0 = $recordset->FetchField(0); +$f0 = $recordset->fetchField(0); 
-$type = $recordset->MetaType($f0->type, $f0->max_length);+$type = $recordset->metaType($f0->type, $f0->max_length);
 print $type; /* should print 'D' */ print $type; /* should print 'D' */
 </code> </code>
  
-===== Select Limit and Top Support =====+==== Select Limit and Top Support ====
  
-ADOdb has a function called [[v5:reference:selectlimit|$connection->SelectLimit($sql,$nrows,$offset)]] that allows you to retrieve a subset of the recordset. This will take advantage of native SELECT TOP on Microsoft products and SELECT ... LIMIT with PostgreSQL and MySQL, and emulated if the database does not support it.+ADOdb has a function called ''[[v5:reference:connection:selectlimit|$connection->SelectLimit($sql,$nrows,$offset)]]'' that allows you to retrieve a subset of the recordset. This will take advantage of native ''SELECT TOP'' on Microsoft products and ''SELECT ... LIMIT'' with PostgreSQL and MySQL, and emulated if the database does not support it.
  
-===== Caching Support =====+==== Caching Support ====
  
 ADOdb allows you to cache recordsets in your file system, and only requery the database server after a certain timeout period with  ADOdb allows you to cache recordsets in your file system, and only requery the database server after a certain timeout period with 
  
-  * $connection->CacheExecute($secs2cache,$sql) +  * ''$connection->cacheExecute($secs2cache,$sql)'' 
-  * $connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset).+  * ''$connection->cacheSelectLimit($secs2cache,$sql,$nrows,$offset)''.
  
-===== Session Handler Support =====+==== Session Handler Support ====
  
 ADOdb also supports session handlers. You can store your session variables in a database for true scalability using ADOdb. For further information, see the section on [[v5:session:session_index|Sessions]]. ADOdb also supports session handlers. You can store your session variables in a database for true scalability using ADOdb. For further information, see the section on [[v5:session:session_index|Sessions]].
Line 235: Line 247:
 ===== Conclusion ===== ===== Conclusion =====
  
-As a thank you for finishing this article, here are the complete lyrics for let's call the whole thing off.+As a thank you for finishing this article, here are the complete lyrics for //let's call the whole thing off//.
  
-   Refrain +<WRAP indent> 
-  You say eether and I say eyether, +// 
-  You say neether and I say nyther; +Refrain \\ 
-  Eether, eyether, neether, nyther - +You say eether and I say eyether, \\ 
-  Let's call the whole thing off ! +You say neether and I say nyther; \\ 
- +Eether, eyether, neether, nyther - \\ 
-  You like potato and I like po-tah-to, +Let's call the whole thing off ! \\ 
-  You like tomato and I like to-mah-to; +\\ 
-  Potato, po-tah-to, tomato, to-mah-to - +You like potato and I like po-tah-to, \\ 
-  Let's call the whole thing off ! +You like tomato and I like to-mah-to; \\ 
- +Potato, po-tah-to, tomato, to-mah-to - \\ 
- +Let's call the whole thing off ! \\ 
-  But oh, if we call the whole thing off, then we must part. +\\ 
-  And oh, if we ever part, then that might break my heart. +But oh, if we call the whole thing off, then we must part. \\ 
- +And oh, if we ever part, then that might break my heart. \\ 
-  So, if you like pajamas and I like pa-jah-mas, +\\ 
-  I'll wear pajamas and give up pa-jah-mas. +So, if you like pajamas and I like pa-jah-mas, \\ 
-  For we know we +I'll wear pajamas and give up pa-jah-mas. \\ 
-  Need each other, so we +For we know we \\ 
-  Better call the calling off off. +Need each other, so we \\ 
-  Let's call the whole thing off ! +Better call the calling off off. \\ 
- +Let's call the whole thing off ! \\ 
-  Second Refrain +\\ 
- +Second Refrain \\ 
-  You say laughter and I say lawfter, +\\ 
-  You say after and I say awfter; +You say laughter and I say lawfter, \\ 
-  Laughter, lawfter, after, awfter - +You say after and I say awfter; \\ 
-  Let's call the whole thing off ! +Laughter, lawfter, after, awfter - \\ 
- +Let's call the whole thing off ! \\ 
-  You like vanilla and I like vanella, +\\ 
-  You, sa's'parilla and I sa's'parella; +You like vanilla and I like vanella, \\ 
-  Vanilla, vanella, choc'late, strawb'ry - +You, sa's'parilla and I sa's'parella; \\ 
-  Let's call the whole thing off ! +Vanilla, vanella, choc'late, strawb'ry - \\ 
- +Let's call the whole thing off ! \\ 
-  But oh, if we call the whole thing off, then we must part. +\\ 
-  And oh, if we ever part, then that might break my heart.+But oh, if we call the whole thing off, then we must part. \\ 
 +And oh, if we ever part, then that might break my heart. \\ 
 +\\ 
 +So, if you go for oysters and I go for ersters, \\ 
 +I'll order oysters and cancel the ersters. \\ 
 +For we know we \\ 
 +Need each other, so we \\ 
 +Better call the calling off off. \\ 
 +Let's call the whole thing off ! 
 +// 
 +</WRAP>
  
-  So, if you go for oysters and I go for ersters, 
-  I'll order oysters and cancel the ersters. 
-  For we know we 
-  Need each other, so we 
-  Better call the calling off off. 
-  Let's call the whole thing off ! 
-   
 Song and lyrics by George and Ira Gershwin, introduced by Fred Astaire and Ginger Rogers in the film "Shall We Dance?" Song and lyrics by George and Ira Gershwin, introduced by Fred Astaire and Ginger Rogers in the film "Shall We Dance?"
  
 (c) 2001-2014 John Lim. (c) 2001-2014 John Lim.
v5/userguide/mysql_tutorial.1455831127.txt.gz · Last modified: 2017/04/21 11:39 (external edit)