v5:userguide:mysql_tutorial
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
v5:userguide:mysql_tutorial [2016/01/13 01:57] – ↷ Links adapted because of a move operation mnewnham | v5:userguide:mysql_tutorial [2016/02/18 23:14] – Fix headlines levels dregad | ||
---|---|---|---|
Line 4: | Line 4: | ||
====== Tutorial ====== | ====== Tutorial ====== | ||
- | ===== Moving from MySQL to ADODB ===== | + | ===== Moving from MySQL to ADOdb ===== |
- | You say eether and I say eyether, | + | <WRAP indent> |
- | You say neether and I say nyther; | + | // |
- | | + | You say eether and I say eyether, |
- | | + | 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; | + | \\ |
- | | + | You like potato and I like po-tah-to, |
- | | + | You like tomato and I like to-mah-to; |
+ | Potato, po-tah-to, tomato, to-mah-to - \\ | ||
+ | Let's call the whole thing off ! | ||
+ | // | ||
+ | </ | ||
I love this song, especially the version with Louis Armstrong and Ella singing duet. It is all about how hard it is for two people in love to be compatible with each other. It's about compromise and finding a common ground, and that's what this article is all about. | I love this song, especially the version with Louis Armstrong and Ella singing duet. It is all about how hard it is for two people in love to be compatible with each other. It's about compromise and finding a common ground, and that's what this article is all about. | ||
Line 20: | 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, | 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, | ||
- | Unfortunately in PHP every database is accessed slightly differently. To connect to MySQL, you would use mysql_connect(); | + | Unfortunately in PHP every database is accessed slightly differently. To connect to MySQL, you would use '' |
- | Let's NOT call the whole thing off | + | ===== Let's NOT call the whole thing off ===== |
- | A database wrapper library such as ADODB comes in handy when you need to ensure portability. It provides you with a common API to communicate with any supported database so you don't have to call things off. | + | A database wrapper library such as ADOdb comes in handy when you need to ensure portability. It provides you with a common API to communicate with any supported database so you don't have to call things off. |
- | ADODB stands for Active Data Objects DataBase (sorry computer guys are sometimes not very original). | + | ADOdb stands for Active Data Objects DataBase (sorry computer guys are sometimes not very original). |
===== 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 |
<code php> | <code php> | ||
+ | // Section 1 | ||
$db = mysql_connect(" | $db = mysql_connect(" | ||
mysql_select_db(" | mysql_select_db(" | ||
+ | |||
+ | // Section 2 | ||
$result = mysql_query(" | $result = mysql_query(" | ||
if ($result === false) die(" | if ($result === false) die(" | ||
+ | |||
+ | // Section 3 | ||
while ($fields = mysql_fetch_row($result)) { | while ($fields = mysql_fetch_row($result)) { | ||
- | for ($i=0, $max=sizeof($fields); | + | |
- | print $fields[$i].' | + | print $fields[$i].' |
- | } | + | } |
- | print "< | + | print "< |
} | } | ||
</ | </ | ||
- | The above code has been color-coded by section. | + | The above code has been split in 3 sections: |
+ | | ||
+ | - The execution of the SQL, and | ||
+ | - Displaying | ||
- | Here is the equivalent code in ADODB | + | Here is the equivalent code in ADOdb |
<code php> | <code php> | ||
+ | // Section 1 | ||
include(" | include(" | ||
$db = NewADOConnection(' | $db = NewADOConnection(' | ||
$db-> | $db-> | ||
+ | |||
+ | // Section 2 | ||
$result = $db-> | $result = $db-> | ||
if ($result === false) die(" | if ($result === false) die(" | ||
- | while (!$result-> | + | |
- | { | + | // Section 3 |
- | for ($i=0, $max=$result-> | + | while (!$result-> |
- | | + | for ($i=0, $max=$result-> |
+ | print $result-> | ||
+ | } | ||
$result-> | $result-> | ||
print "< | print "< | ||
} | } | ||
- | |||
</ | </ | ||
- | Now porting to Oracle is as simple as changing the second line to NewADOConnection(' | + | Now porting to Oracle is as simple as changing the second line to '' |
- | ===== Connecting to the Database | + | ==== Connecting to the Database ==== |
<code php> | <code php> | ||
Line 76: | Line 91: | ||
</ | </ | ||
- | The connection code is a bit more sophisticated than MySQL' | + | The connection code is a bit more sophisticated than MySQL' |
- | 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 |
- | Then we create a new instance of the connection class by calling NewADOConnection(). Finally we connect to the database using $db-> | + | Then we create a new instance of the connection class by calling |
- | ===== Executing the SQL ===== | + | ==== Executing the SQL ==== |
<code php> | <code php> | ||
$result = $db-> | $result = $db-> | ||
- | if ($result === false) | + | if ($result === false) die(" |
- | | + | |
</ | </ | ||
- | 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. |
- | 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? |
The connection object ($db) is responsible for connecting to the database, formatting your SQL and querying the database server. The recordset object ($result) is responsible for retrieving the results and formatting the reply as text or as an array. | The connection object ($db) is responsible for connecting to the database, formatting your SQL and querying the database server. The recordset object ($result) is responsible for retrieving the results and formatting the reply as text or as an array. | ||
- | 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-> | while (!$result-> | ||
{ | { | ||
- | for ($i=0, $max=$result-> | + | |
- | print $result-> | + | print $result-> |
- | | + | $result-> |
- | | + | print "< |
} | } | ||
</ | </ | ||
Line 111: | 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, | 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, | ||
- | The $result-> | + | The '' |
<code php> | <code php> | ||
Line 122: | Line 136: | ||
</ | </ | ||
- | 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 '' |
===== ADOConnection ===== | ===== ADOConnection ===== | ||
Line 130: | Line 144: | ||
===== Other Useful Functions ===== | ===== Other Useful Functions ===== | ||
- | $recordset-> | + | * '' |
- | $recordset-> | + | * '' |
- | $recordset-> | + | * '' |
- | 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: | + | * '' |
<code php> | <code php> | ||
- | |||
include(' | include(' | ||
include(' | include(' | ||
Line 155: | 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. | ||
- | | + | < |
- | TheDate=mktime(0, | + | ID = 3 |
- | Note= sugar why don't we call it off | + | TheDate=mktime(0, |
+ | Note= sugar why don't we call it off | ||
+ | </ | ||
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. | + | 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. |
- | 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'' | + | The next problem is that the //don't// in the Note needs to be quoted. In MySQL, we use '' |
So how do we use the functions? Like this: | So how do we use the functions? Like this: | ||
Line 176: | Line 191: | ||
</ | </ | ||
- | ADODB also supports [[reference: | + | ADOdb also supports |
- | ===== MetaTypes | + | ==== MetaTypes ==== |
- | You can find out more information about each of the fields (I use the words fields and columns | + | You can find out more information about each of the fields (I use the words fields and columns |
For example: | For example: | ||
Line 188: | Line 203: | ||
</ | </ | ||
- | Then $f0-> | + | Then '' |
- | 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, | + | 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 |
- | | + | ^ 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, | ||
Line 210: | Line 226: | ||
</ | </ | ||
- | ===== Select Limit and Top Support | + | ==== Select Limit and Top Support ==== |
- | ADODB has a function called [[reference: | + | ADOdb has a function called |
- | ===== 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-> | + | * '' |
- | * $connection-> | + | * '' |
- | ===== 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, | + | ADOdb also supports session handlers. You can store your session variables in a database for true scalability using ADOdb. For further information, |
===== Commercial Use Encouraged ===== | ===== Commercial Use Encouraged ===== | ||
Line 231: | 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//. |
- | + | ||
- | | + | |
- | You say eether and I say eyether, | + | |
- | You say neether and I say nyther; | + | |
- | Eether, eyether, neether, nyther - | + | |
- | Let's call the whole thing off ! | + | |
- | + | ||
- | You like potato and I like po-tah-to, | + | |
- | 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. | + | |
- | + | ||
- | So, if you like pajamas and I like pa-jah-mas, | + | |
- | I'll wear pajamas and give up pa-jah-mas. | + | |
- | For we know we | + | |
- | Need each other, so we | + | |
- | Better call the calling off off. | + | |
- | Let's call the whole thing off ! | + | |
- | + | ||
- | Second Refrain | + | |
- | + | ||
- | You say laughter and I say lawfter, | + | |
- | You say after and I say awfter; | + | |
- | Laughter, lawfter, after, awfter - | + | |
- | Let's call the whole thing off ! | + | |
- | + | ||
- | You like vanilla and I like vanella, | + | |
- | You, sa' | + | |
- | Vanilla, vanella, choc' | + | |
- | Let's call the whole thing off ! | + | |
- | | + | <WRAP indent> |
- | And oh, if we ever part, then that might break my heart. | + | // |
+ | Refrain \\ | ||
+ | You say eether and I say eyether, \\ | ||
+ | You say neether and I say nyther; \\ | ||
+ | Eether, eyether, neether, nyther - \\ | ||
+ | Let's call the whole thing off ! \\ | ||
+ | \\ | ||
+ | You like potato and I like po-tah-to, \\ | ||
+ | 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. | ||
+ | \\ | ||
+ | So, if you like pajamas and I like pa-jah-mas, \\ | ||
+ | I'll wear pajamas and give up pa-jah-mas. \\ | ||
+ | For we know we \\ | ||
+ | Need each other, so we \\ | ||
+ | Better call the calling off off. \\ | ||
+ | Let's call the whole thing off ! \\ | ||
+ | \\ | ||
+ | Second Refrain \\ | ||
+ | \\ | ||
+ | You say laughter and I say lawfter, \\ | ||
+ | You say after and I say awfter; \\ | ||
+ | Laughter, lawfter, after, awfter - \\ | ||
+ | Let's call the whole thing off ! \\ | ||
+ | \\ | ||
+ | You like vanilla and I like vanella, \\ | ||
+ | You, sa' | ||
+ | Vanilla, vanella, choc' | ||
+ | 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. \\ | ||
+ | \\ | ||
+ | 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 ! | ||
+ | // | ||
+ | </ | ||
- | 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.txt · Last modified: 2018/06/27 16:09 by dregad