|
|
| Line 3: |
Line 3: |
| | = PHP and Databases – Making the Connection = | | = PHP and Databases – Making the Connection = |
| | | | |
| − | To execute queries on a database is made up of 2 parts:
| + | == There are 3 ways to connect to a database in PHP: |
| − | # Send the Query to the Database
| |
| − | # Handle the response
| |
| | | | |
| − | == Connecting to the MySQL Database Server ==
| + | # (DEPRECATED) The [http://www.php.net/manual/en/ref.mysql.php mysql functions] are procedural and use manual escaping. |
| − | Firstly, to connect to a database we use the following:
| + | # MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements. |
| − | $dbServer=mysql_connect ("localhost","cp1079_student","");
| + | # PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned. |
| − | * The parameters passed to the '''mysql_connect''' statement are:
| |
| − | ** The MySQL Database Server Address - this is usually running on the same machine as PHP, so "localhost" can be used
| |
| − | ** A Username
| |
| − | ** A Password (in this instance, there is no password for the user "demo")
| |
| − | * $dbServer is a variable we use to hold the connection to the database server. It’s use will become clearer as we progress.
| |
| | | | |
| − | == Checking the Connection ==
| |
| − | To check to make sure the connection is OK, you can use the following:
| |
| − | if (!isset($dbServer))
| |
| − | {
| |
| − | print ("failed to connect to server");
| |
| − | exit;
| |
| − | }
| |
| − | * if $dbServer is not set - i.e. is empty - no connection exists to the database server
| |
| | | | |
| − | == Choosing a database on the MySQL Database Server ==
| |
| − | Next, we choose the database we want to use with the following:
| |
| − | mysql_select_db("cp1079",$dbServer);
| |
| − | * The parameters passed to the '''mysql_select_db''' statement are:
| |
| − | ** the database name we want, in this case '''cp1079''' is the name of the database
| |
| − | ** the database server connection that we have already opened - $dbServer
| |
| − |
| |
| − | == Executing a Query ==
| |
| − | To execute a query, first we have to prepare the query. This is best accomplished using a string variable.
| |
| − | $sql = "SELECT name, species, age FROM pet";
| |
| − | When we execute the query, we need to handle the response from the Database server, so we need to use a special variable called a ''resource''
| |
| − | $queryResult = mysql_query($sql);
| |
| − | * $queryResult is the ''resource'' that holds the response from the Database server
| |
| − | * $sql is passed to the Database server using the command '''mysql_query'''
| |
| − |
| |
| − | == Did it work? ==
| |
| − | We can test to see if the query executed correctly at the Database server by using the following:
| |
| − | <nowiki>if (mysql_error())
| |
| − | {
| |
| − | print ("There has been an error<BR>".mysql_error());
| |
| − | exit;
| |
| − | }</nowiki>
| |
| − |
| |
| − | * '''mysql_error()''' is a function that will return any error messages generated by the database server based on the last instruction
| |
| − | ** i.e. the mysql_query was the last instruction, if this generated errors, mysql_error() will retrieve the error message
| |
| − | ** so '''if(mysql_error())''' means, if the last instruction caused errors, do the following...
| |
| − | ** in our example, we output the error message and exit the program if an error was caused
| |
| − |
| |
| − | == Assuming it worked... ==
| |
| − | We can now check to see how many rows have been returned using the following:
| |
| − | <nowiki>print mysql_num_rows($queryResult)." records returned<BR>";</nowiki>
| |
| − | * this will output the number of records returned in the $queryResult
| |
| − | 8 '''NOTE:''' - this will only work for SELECT queries, as only SELECT queries return records
| |
| − |
| |
| − | == Manipulating the Records returned ==
| |
| − | The most common way to manipulate the returned records, is to extract the first record returned and convert it into an array. We do this with the following:
| |
| − | $dbRecord=mysql_fetch_array($queryResult);
| |
| − | * mysql_fetch_array extracts the first record from $queryResult into an array called $dbRecord
| |
| − | * $dbRecord will be an array containing '''ONLY''' the first record returned, not all of the records, just the first
| |
| − |
| |
| − | == Printing out records returned ==
| |
| − | As we have SELECTed three fields (name, species, age) from the table, there will be three elements created in our array. To print out the first element (name) we can use:
| |
| − | print $dbRecord[0];
| |
| − | * THIS DOES NOT PRINT OUT THE FIRST RECORD - this ONLY prints out the first element of the array (or first field of the record) of the first record returned from the database
| |
| − |
| |
| − | To print out the second field of the first record:
| |
| − | print $dbRecord[1];
| |
| − |
| |
| − | and so on…..
| |
| − |
| |
| − | == Easier access to the fields in the records ==
| |
| − | The mysql_fetch_array statement also names the elements in the array as well.
| |
| − | $dbRecord[0] is the same as $dbRecord["name"]
| |
| − | $dbRecord[1] is the same as $dbRecord["species"]
| |
| − | $dbRecord[2] is the same as $dbRecord["age"]
| |
| − |
| |
| − | = Putting it all together =
| |
| − | <nowiki><?
| |
| − | $dbServer=mysql_connect ("localhost","cp1079_student","");
| |
| − | if (!isset($dbServer))
| |
| − | {
| |
| − | print ("failed to connect to server");
| |
| − | exit;
| |
| − | }
| |
| − | mysql_select_db("cp1079",$dbServer);
| |
| − | $sql = "SELECT name, species, age FROM pet";
| |
| − | $queryResult = mysql_query($sql);
| |
| − | if (mysql_error())
| |
| − | {
| |
| − | print ("There has been an error<BR>".mysql_error());
| |
| − | exit;
| |
| − | }
| |
| − | print mysql_num_rows($queryResult)." records returned<BR>";
| |
| − | $dbRecord=mysql_fetch_array($queryResult);
| |
| − | print "Name is ".$dbRecord["name"]." and species is ".$dbRecord["species"]." and age is " .$dbRecord["age"];
| |
| − | ?></nowiki>
| |
| − |
| |
| − | = BUT... =
| |
| − | This only prints out the first record - our sql query asked for all the records from the pet table.
| |
| − |
| |
| − | We need a loop to loop through all the records returned. Try replacing the last two lines of the code above with an elegant loop to iterate through the records regardless of how many records are returned, like this:
| |
| − | <nowiki>while ($dbRecord=mysql_fetch_array($queryResult))
| |
| − | {
| |
| − | print "Name is ".$dbRecord["name"]." and species is ".$dbRecord["species"]." and age is ".$dbRecord["age"]."<BR>";
| |
| − | }</nowiki>
| |
| | | | |
| | = Ready to try some more PHP/MySQL? = | | = Ready to try some more PHP/MySQL? = |
| | A simple example of using a HTML form to capture search requirements and execute a custom query can be found at [[PHP112|PHP112 - Simple HTML/PHP/MySQL example]] | | A simple example of using a HTML form to capture search requirements and execute a custom query can be found at [[PHP112|PHP112 - Simple HTML/PHP/MySQL example]] |
