Introduction to database programming for the web using MySQL & PHP

Before you get started:

The first part of this exercise was to review the SQL topic at w3schools.com & get your hands on MySQL at the command line and make some queries using the EBUS202 database so you can _see_ how SQL works and the results it delivers. 

When using MySQL 'at the command line' the results are delivered into a table with column headers at top and a row for each record, or summary, returned by the query.  If you haven't done this yet, please back up and become familiar with SQL at the command line. In 'Database Programmeing' MySQL delivers a similar datastructure, a _result_, that can be read by PHP.

Also, review the PHP section of w3schools.com to get the basic syntax and rules for PHP scripting.  Use the documentation at w3schools, or at us2.php.net, to look up PHP statements as you review the PHP script in DonationReports.php.  These resources supply more detail than can be provided here...

 

Getting Started:

Check out the web pages for this project at http://ebus202.us/SeSPoP/.  Choose the 2nd link, 'SeSPoP Donation Reports: GET data demo' to see it work. 

The files for these pages are available for you to copy to your /home/yourid/web/ directory.  They are in /home/SeSPoP/Web, named LinkToReports.html and DonationReports.php. 

You can copy the files to your web directory using WinSCP or Unix commands.

To copy them using WinSCP, navigate to /home/SeSPoP/Web, then drag & drop these two files to the directory on WinSCP's left side.  Then, navigate back to your web directory and drag & drop the files from the left side to the right.

To copy them using the command line, make sure you're in your web directory.  Then use these commands to make the copy:

  cp /home/SeSPoP/Web/LinkToReports.html ./
  cp /home/SeSPoP/Web/DonationReports.php ./

The 'dot slash' means 'this directory'.

Use your editor to examine the contents of the files. 

LinkToReports.html is 'plain html' with links to DonationReports.php that supply 'GET data' (after the ? in the url) telling DonationReports.php WhichReport to provide.

Make a link in your home page, index.html, to LinkToReports.html, labeled as 'SeSPoP Reports', so I can find it easily.  Then add the  DOCTYPE, xmlns, and link to your CSS to LinkToReports.html and DonationReports.php (the html is at the bottom of the script) so that the pages fit in with your site's appearance. 

After you've got the pages in place, you can set to the rest of the assignment, which is to make your DonationReports page handle two new queries...

 

About DonationReports.php

DonationReports.php is an example of a PHP script.  Apache is a 'modular' web server that is able to run PHP scripts very efficiently, and it continues to gain popularity as a database programming language in both open source and Windows environments.  It was built to 'sit between'  web browsers and databases and it has functions and data structures built for this purpose that make it relatively easy to program in this very complex environment.

JavaScript, used to introduce structured and object-oriented programming, is a 'browser side' programming language that runs on the web browser's machine.

PHP is a 'server side' language that runs on the server and usually sends its output over the internet to the web browser.  They resemble one-another in syntax and share some language elements.  Some of their structured control statements are the same (if/then, if/then/else, if/then/elseif, while, &c).  They both use curly brackets to indicate multi-line control structures, and they both require those pesky semi-colons at the end of each line of script. 

They are both 'loosely typed' languages.  Where JavaScript requires that all program variables are 'declared' in a var statement before they are used, PHP requires a dollar sign ($) be the first character of variable names.  PHP also includes 'predefined variables' that are loaded automatically so they are available for the programmer: $_GET is an array holding any GET data sent to the script; $_POST holds POST data from HTML forms; $_SERVER holds other data such as the browser type & the referring page; $_SESSION holds 'session data' kept on the web server about a user's 'session'.

When a file ending in .php is requested from ebus202.us Apache (the httpd) 'parses' it expecting to find PHP code along with HTML or XHTML.  When the parser encounters a <? it executes the PHP statements that follow until it encounters a ?>.  Any output by the PHP statements, as in 'echo', 'print', or 'die', is output to the browser making the request.  Lines outside of these marks are treated as ordinary HTML or XHTML and are output directly to the browser. 

This allows a web programmer to mix PHP statements in with HTML as needed to provide 'dynamic content' to the web browser.  Some portions of the HTML delivered to the web browser may be 'hard coded' and other portions may be output by the PHP script.

The technique used in DonationReports.php is to place most of the PHP script at the head of the file where it loads two variables, $Links and $Report, that are printed in the body of the <html> at the end of the file.  Look all the way at the end of the file and find

 

Getting the web browser's GET data into the script:

'Database programming' often means that a programmer writes a script that constructs an SQL statement (query, insert, or update) based on the data that arrives with the web browser's request, sends the SQL statement to a DBMS to be executed, and formats the results returned by the DBMS to the script into html. 

Find and look at LinkToReports.html and DonationReports.php so you can see the features discussed next...

One of the ways a web browser sends data to a web server is by including 'GET data' in the URL when the 'GET request' is made by a user clicking a link.  GET data is passed in the form 'Variable=Value'.  GET data follows a question mark (?) in the URL, as in the example LinkToReports.html where the PHP script DonationReports.php is called on the link to the report of donations by region:

  <a href=DonationReports.php?WhichReport=ByRegion>

Depending on the link clicked, DonationReports.php will deliver a report of donations by region, or the top ten donations, or a report of how long the server has been up.

PHP makes the GET data available to scripts in a pre-defined array called $_GET.  The name of the variable is used as the key, or subscript, to the array.  So, $_GET['WhichReport'] will contain the name of the report on the link clicked by the user. 

Line 4 of DonationReports.php checks to see if the URL contains the expected GET variable 'WhichReport'.  The explanation point ('bang', !) means NOT.  It's good programming practice for the script to always make sure it has the variables & values expected so that a meaningful response can be given the user and it doesn't just 'blow up' and produce unclear results... 

If $_GET['WhichReport'] is not set, the 'true' part of the if/then statement puts an error message "No report was selected..." in $Report, else the script continues on to open a database connection and use an if/then/elseif structure to examine the contents of $WhichReport to make the report requested by the web browser. 

Look for the beginning and end of the then & else portions of this structure -- they are clearly indented to show where they begin and end. 

The last alternative in the if/then/elseif, following the final 'else', puts an error message in $Report if none of the expected values is in $WhichReport were encountered. 

You can try this by typing in the url and leaving out the GET data, or by putting a bogus value after ?WhichReport= and sending it to the script.

 

Getting data from MySQL:

When using a DBMS like MySQL in a script, the SQL Statement is put into a variable and then the variable is put into MySQL, which returns a 'result set' just like the table that results from a query at the command line.  The script is able to 'loop thru' the result set, one result at a time, and format the data into a report.

In DonationReports.php a simple query is constructed for each of the reports using the DBMS.  The PHP variable $SQLStmt is assigned a valid SQL statement like "select sum(Amount) as RegionAmount...". 

PHP passes the SQL statement to MySQL using the mysql_query function, similar to how you type in a query at the command line and hit Enter:

  $Result = mysql_query($SQLStmt) or die ("Unable to make query at this time");

The result of the query is returned to the script in a 'result set' named like $RegionResults.  You can think of the result set as being the same as the columns and rows that are returned to your terminal window when you make a query using MySQL from the command line.  If the script is unable to execute the MySQL query the statement after 'or die' is output to the browser as the script stops executing.

Rows in a result set are read one at a time using a statement like

   $ARow = mysql_fetch_assoc($RegionResults)

to put the row into an 'associative array' using the column name as the key to the array. PHP provides a 'shortcut', extract, that 'extracts' the data in each row into PHP variables named the same as the columns using a statement like

     extract($ARow).

PHP assigns a value of 'true' if a row can be fetched using a mysql_fetch functiton, and 'false' when the end of the result set is reached.  In this way, mysql_fetch_assoc can be used to control a while loop that reads the result set and puts the data into html to be returned to the web browser:

   $SQLStmt = "select sum(Amount) as RegionAmount, RegionId, RegionName
     from Donations, Regions
     where Regions.Id=RegionId 
     group by RegionName order by RegionAmount desc";
   $RegionResults = mysql_query($SQLStmt) or die
      ("Unable to access database at this time...");
   $Report = "Donations by Region:<br><br>\r\n";
   while ($ARow = mysql_fetch_assoc($RegionResults)) {
     extract($ARow);
     $Report .= "$RegionName - $RegionAmount<br>"
   }
 

Getting the output from the script into <html>:

In this example, the HTML to be delivered to the browser is found at the end of the file, following the ?> at the end of the PHP script:

The required elements of the web page, html, head, and body, are coded as usual. 

In the place where the report is included on the page, the html 'escapes into PHP' using <? to print the report and the links to the web browser, then goes back to html using ?>:

<html>
  <head>
   <title>South Eastern Society for Preservation of Preservation -- Reports</title>
  </head>
  <body>
   <? 
    print "$Report";
    print "<br><br>$Links";
   ?>
  </body>
</html>

 

Your job for the project:

Review the sample script and be able to identify its parts and functionality.

Use MySQL at the command line to make two reports from the EBUS202 database that are not already in the demo pages. 

Then, copy the elseif block that makes 'TopTenInTable' and paste it twice into the if/then/elseif structure and modify the pasted copies to produce each of your new reports.  (The assignment requires 'tabulated' reports for full credit.)

Add links with appropriate values for ?WhichReport= in LinkToReports.html so that you can call DonationReports.php to show your two new reports.  Optionally, you may discard LinkToReports.html and place links to the new reports directly in your index.html.

Any questions?  Please ask in email or in class...

 

 

Hit Counter