This is the code to accompany the PHP Primer on this site.
It provides a fully working PHP/MySQL records management system on one script when given access to the MySQL table 'primer', which can be created with the code embedded in comments at the end of the script.
This very simple script should get you up and running very quickly, and you can build on it.
Note that before putting any web application online, input from site visitors should be filtered to prevent SQL Injection attacks.
<!--?php // ------------------------------------------------------------------------------------ // PHP MySQL PRIMER v3 // ------------------------------------------------------------------------------------ // // This file is intended to give a basic understanding and all the code required to // access a MySQL database with PHP and to add, edit and delete data in the database. // // If you run this .php script on a server with PHP and MySQL installed, and have // created the 'primer' database using the sql script in the comments at the end of // this file, this script is a functioning add/edit/delete/list database interface. // You can cut and paste this entire document into a .php file using a text editor. // // You can copy this file and use it as the basis of any system to manage information // with PHP and MySQL. // // Prerequisites: // - Installing a web server, PHP and MySQL is beyond the scope of this document. // Suffice it to say that these components are available at no cost. Many people use // a WAMP package (http://en.wikipedia.org/wiki/WAMP), which installs all these // components together. One such WAMP package is called XAMPP, and can be downloaded // at http://en.wikipedia.org/wiki/XAMPP. Separate manual install of these components // can get a little involved, so you may be better off with a XAMP package or a LAMP // package on Linux. // // - The sql script embedded in comments at the end of this file will give the SQL // code required to create the databse and table that work with this file. // Save the script in a file called primer.sql and restore the file to your // MySQL server and it will create the database. // // - Create a MySQL user 'test' with the password 'password', and ensure that it // has full rights on the database 'primer'. // // - Once you do that you can start to experiment with this page. // // This demonstration script operates with an extremely simple database, called 'primer'. // It contains only one table, 'members' and that table stores only three // attributes, 'id', 'firstname' and 'lastname'. The 'id' attribute is the primary key // and it is an auto-increment attribute, meaning that when a new record is added // the value of 'id' will be automatically assigned. // // I strongly recommend installing the MySQL Administrator and MySQL Query Browser // utilities, both free from MySQL at http://dev.mysql.com/downloads/gui-tools/5.0.html. // The administrator utility can be used to 'restore' the primer.sql file to create // the sample database on your server. // // I also strongly urge the use of a syntax-colouring text editor, which makes code // much easier to work with. A good option is the free open-source editor Notepad++ // at http://notepad-plus.sourceforge.net. // // // NOTE: In a normal working environment, some of the things in this // file would be separated into include files called with the include() function. // Examples would be the page header HTML code, the HTML code to close the page, // and the database connect code. These include files would be accessed by all pages // on the site, so changes to the entire site could be made in one file. // In this case, I've put it all into one file for clarity. // // Now Go Nuts! // // // // ------------------------------------------------------------------------------------ // Version 2 - 2009-07-30: Some enhancements added which make the code slightly more // complex, but make it a better template for building a webapp component. // - Added TH header // - Added CSS styles // - Replaced ampersands in query strings with entities to pass validation // - Added code for alternating row colours // - Added code to sanitize input to prevent SQL Injection attacks // // Version 3 - 2009-10-15: embedded primer.sql script in comments at end of this file // // ------------------------------------------------------------------------------------ // First we're going to open the HTML page in the normal fashion // this page intro applies to all pages rendered by this file. // You probably already understand this, so feel free to skip ahead to the // DATABASE CONNECT section following. // the DOCTYPE declaration just below is typically on one line, it is split in 2 here to facilitate printing ?--> |
tags contain Cascading Style Sheet (CSS) code, -- */ /* -- which controls the visual appearance of content on the web page. -- */ /* ---------------------------------------------------------------------------- */ .header a {color: white;} html {font-family:sans-serif;} table {border-collapse:collapse;} .listtable table {padding:.25em;border:1px solid silver;} .listtable td {padding:.125em .5em .125em .5em;border:1px solid silver;font-size:80%;text-align:left;} .listtable th { background-color:#B0C4DE; padding:.5em .5em 0em .5em; font-weight: bolder; font-size:80%; text-align:left; border-top:2px solid navy; border-bottom:1px solid navy; line-height:135%; } .listtable tr {border-left:1px solid navy;border-right:1px solid navy;} .listtable a:link {color:blue;} .listtable a:visited {color:blue;} .listtable a:active {color:blue;} .listtable a:hover {color: red;} |
$value) { $_POST[$key] = mysql_real_escape_string($value); } // Prevent SQL Injection attacks in GET vars foreach ($_GET as $key => $value) { $_GET[$key] = mysql_real_escape_string($value); } // connect to database server $dbserver = "localhost"; $username = "test"; $password = "password"; $connect = mysql_connect ("$dbserver", "$username", "$password") or die ('
I cannot connect to the database because: ' . mysql_error()); // select the database mysql_select_db ("primer") or die('
I could not select database: '.mysql_error()); // ==================================== /database connect ======================================= // // OK, so we've opened up our HTML page and connected to the database. // Now it's time to determine what the page will be doing, because this PHP script // file will generate pages to fill several distinct roles // When the page is loaded, it may have a query string with an 'action' parameter. // The switch statement below will look for that parameter and load the // appropriate page content by calling a function that generates the page. // A couple of these functions do not generate a page, they perform an action // on the database instead, then end by calling the listRecords() function to show // the modified database. // The code which follows first picks up the 'action' parameter – if any – from the // Query String (the end of the URL). // Then it uses that value in a switch statement to decide which of the functions below // will be executed. // If there is an action parameter in the Query String, the appropriate block will execute. // If there isn't an action paramenter, or it isn't listed in the case statements below, // the default block at the end of the switch statement will execute. // Once one of the functions is called from the switch statement, the function will // execute, after which control will be returned to the line following the function call. // That's important to understand, so pause here and make sure that's clear. // Each case in the switch ends with a break statement to cause flow to break out of the // switch statement once flow returns from the called function. Following the break // statement the flow will resume on the line immediately following the switch statement. // The real work of the page is done inside these functions // Capture the 'action' parameter from the query string. If there is none, // enter a substitute so that the switch statement doest not generate an error. $action = isset($_REQUEST['action']) ? $_REQUEST['action'] : "none_specified"; // One of the cases below will be executed, depending on the value of $action switch ($action) { case 'add': addRecord(); break; case 'delete'; deleteRecord(); break; case 'edit'; editRecord($_REQUEST['id']); break; case 'commitNewRecord'; commitNewRecord(); break; case 'update'; updateRecord(); break; default; listRecords(); break; } // Once the function called from the switch statement above is complete, the flow // will return here, to the line following the switch statement // Now we will close the page structure. ?>
I cannot connect to the database because: ' . mysql_error()); // select the database mysql_select_db ("primer") or die('
I could not select database: '.mysql_error()); // ==================================== /database connect ======================================= // // OK, so we've opened up our HTML page and connected to the database. // Now it's time to determine what the page will be doing, because this PHP script // file will generate pages to fill several distinct roles // When the page is loaded, it may have a query string with an 'action' parameter. // The switch statement below will look for that parameter and load the // appropriate page content by calling a function that generates the page. // A couple of these functions do not generate a page, they perform an action // on the database instead, then end by calling the listRecords() function to show // the modified database. // The code which follows first picks up the 'action' parameter – if any – from the // Query String (the end of the URL). // Then it uses that value in a switch statement to decide which of the functions below // will be executed. // If there is an action parameter in the Query String, the appropriate block will execute. // If there isn't an action paramenter, or it isn't listed in the case statements below, // the default block at the end of the switch statement will execute. // Once one of the functions is called from the switch statement, the function will // execute, after which control will be returned to the line following the function call. // That's important to understand, so pause here and make sure that's clear. // Each case in the switch ends with a break statement to cause flow to break out of the // switch statement once flow returns from the called function. Following the break // statement the flow will resume on the line immediately following the switch statement. // The real work of the page is done inside these functions // Capture the 'action' parameter from the query string. If there is none, // enter a substitute so that the switch statement doest not generate an error. $action = isset($_REQUEST['action']) ? $_REQUEST['action'] : "none_specified"; // One of the cases below will be executed, depending on the value of $action switch ($action) { case 'add': addRecord(); break; case 'delete'; deleteRecord(); break; case 'edit'; editRecord($_REQUEST['id']); break; case 'commitNewRecord'; commitNewRecord(); break; case 'update'; updateRecord(); break; default; listRecords(); break; } // Once the function called from the switch statement above is complete, the flow // will return here, to the line following the switch statement // Now we will close the page structure. ?>
<!--?php // ensure that the flow stops here, immediately after the page structure is closed. exit(); // ==================================================================================== // // ====== THIS IS THE EXIT POINT OF THE FILE. ===== // // ====== ALL CODE BELOW IS CALLED FROM THE CASES IN THE SWITCH STATEMENT ABOVE ===== // // ==================================================================================== // function listRecords() { #----------------------------------------------------------------------------------------- # FUNCTION: listMembers # PURPOSE: Read the contents of the members table and list them # PARAMETERS: none #----------------------------------------------------------------------------------------- // display a header echo "<h2-->List Members"; // start by opening up a table to display the data echo ""; // read the data from the database table into a dataset $dataset = mysql_query("SELECT * FROM members"); // create the header row echo ""; echo " \n"; // nothing needed in 1st column header, so just put a hard space entity echo " \n"; echo " \n"; echo ""; // now we'll loop through the dataset, displaying rows one by one while ($row = mysql_fetch_assoc($dataset)) { // create a variable for each attribute in this row // the variable will have the same name as the attribute // this is much easier to work with than "$row['attribute']" foreach($row as $var => $value){ $$var = $value; } // create the background colour for this row to make it easier to read. // The background colour will toggle back and forth between each row. $bgcolour = (!isset($bgcolour) || $bgcolour == "#F0F7FF") ? "#FFFFFF" : "#F0F7FF"; // Now create a table row for this record // Note how the variables $id, $firstname and $lastname, created just above, are // inserted into the data. // Remember this is all happening inside a 'while' loop, so these values will change // with each iteration as the rows are generated one by one. echo "\n"; // note the variable that sets the alternating row color echo ""; echo ""; echo ""; } // end while // We're out of the while loop, so we've gone through all the rows in the dataset. // Now close the table echo " |
First Name | Last Name | |
---|---|---|
"; echo " edit · "; echo " delete"; echo " | $firstname | $lastname |
"; } // end listRecords() function addRecord() { #----------------------------------------------------------------------------------------- # FUNCTION: addRecord() # PURPOSE: Present a form for the user to add a new record to the database # PARAMETERS: None # NOTE: Notice that I have used the hard-space entity  in the empty # table cell. This corrects display problems with empty cells # in some browsers. #----------------------------------------------------------------------------------------- // display a header echo " |
Add a New Member
"; // Build the form. // Because this is a fairly lengthy piece of code, we'll break out of PHP mode into HTML // mode temporarily. // We supply the 'action' parameter 'commitNewRecord' in the 'action' parameter in the // form element to tell the script what to do when it receives the data from the form. // NOTE: Notice how we use the server variable $_SERVER['PHP_SELF'] in the |
First name: | |
Last name: | |
Enjoy!