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 23:09] – escape -> to avoid entities conversion 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 38: Line 38:
 <code php> <code php>
 // Section 1 // Section 1
-$db = mysql_connect("localhost", "root", "password"); +$db = mysqli_connect("localhost", "root", "password"); 
-mysql_select_db("mydb",$db);+mysqli_select_db("mydb",$db);
  
 // Section 2 // Section 2
-$result = mysql_query("SELECT * FROM employees",$db);+$result = mysqli_query("SELECT * FROM employees",$db);
 if ($result === false) die("failed"); if ($result === false) die("failed");
  
 // Section 3 // Section 3
-while ($fields = mysql_fetch_row($result)) {+while ($fields = mysqli_fetch_row($result)) {
     for ($i=0, $max=sizeof($fields); $i < $max; $i++) {     for ($i=0, $max=sizeof($fields); $i < $max; $i++) {
         print $fields[$i].' ';         print $fields[$i].' ';
Line 64: Line 64:
 // Section 1 // 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");
  
 // Section 2 // Section 2
-$result = $db->Execute("SELECT * FROM employees");+$result = $db->execute("SELECT * FROM employees");
 if ($result === false) die("failed"); if ($result === false) die("failed");
  
 // Section 3 // Section 3
 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>
    
-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 ====
Line 87: Line 87:
 <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>
  
Line 100: Line 100:
  
 <code php> <code php>
-$result = $db->Execute("SELECT * FROM employees");+$result = $db->execute("SELECT * FROM employees");
 if ($result === false) die("failed"); if ($result === false) die("failed");
 </code> </code>
Line 116: Line 116:
 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";
 } }
Line 129: Line 129:
 <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')
Line 144: 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 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:
Line 155: Line 155:
 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 176: Line 176:
 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 187: 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 interchangeably) 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//.
Line 199: Line 199:
 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>
  
Line 220: Line 220:
  
 <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]].
v5/userguide/mysql_tutorial.1455833395.txt.gz · Last modified: 2017/04/21 11:39 (external edit)