Intro to databases using PHP
    12/6/17
  1. Like all server-side languages, PHP can interface with databases. Here is the introductory lecture for ColdFusion (CFML) that discusses database structure and shows how to access (query) a database. Note that the actual statements ColdFusion uses are SQL (Structured Query Language), which are also used by PHP. So learning one server-side language gets you started on another. The database files can be of varies types, but most PHP scripts use MySQL, which is open-source, as is PHP, with the database having an .sql file extension.
  2. Most PHP users adminster the MySQL databases through a module called phpMyAdmin. If you want to build a server emulation on your local machine for development and testing, there are packages to install such as WAMPP, XAMP and MAMP. The "A" is Apache, which is the server emulator, "M" is MySQL, "P" is PHP and the extra "P" in WAMPP is Perl. XAMP is cross-platform, WAMP is Windows, MAMP is Mac.
  3. We have phpMyAdmin set up on the the DArt server (csc-srv1.lasalle.edu). The instructions to FTP to that server are here. Databases (think of them as containers for spreadsheets/tables) could be accessed here. Note the URL ends with a port number http://csc-srv1.lasalle.edu:8080/
  4. Take a look at the phpMyAdmin—I will demo my database that has two related tables, one for company employees and one for company departments, (handout) connected by a shared field called fldDep_id. This is the "foreign key" that you saw in the CFML lecture.
  5. PHP.net has a sample (and "simple") database page that shows the basic functions needed : mysql_connect() mysql_select_db() mysql_query() mysql_fetch_* which has various options. For this server and its PHP version (installations can have different settings) mysql_fetch_array works best. Note that it is saying loop through the database and build an array of what is being brought back ("fetched").
  6. So here is what I built, with the code below using this SQL database that you could download (Right- or Ctrl-click; Click to see SQL) then Import into your database folder. I first selected and output each table, then did a join, using the WHERE clause and output that. Note how it differs from the sample page above. Note also how it builds the output into an HTML table (standard practice, since the database contains tables), with the PHP cycling through the rows of the tables.
  7. Here is the code as plain text. The PHP page also contains an embedded stylesheet. Commented code includes one fatal error that's easy to make.

Back to home page