Maya's Interactive Multimedia Blog: April 2009

Saturday, April 25, 2009

a simple content management system: finis

I've made a few finishing touches to my Contacts application by including some functionality to allow users to add new records to the database table.

In a new file I named insert.php, I've added two main sections. Firstly, I've written some code to take user form inputs and store them into my database table, as shown here:

<?php

// If "Insert Contact" form was submitted.
if ($_POST['action'] == 'saveform') {
 // Load the user inputs into local variables.
 $firstname = $_POST['firstname'];
 $lastname = $_POST['lastname'];
 $phone = $_POST['phone'];
 $email = $_POST['email'];
 // Insert new record into database.
 $sql = "INSERT INTO contacts ( first_name, last_name, phone, email ) VALUES ( '$firstname', '$lastname', '$phone', '$email' );";
 mysql_query($sql) or die(mysql_error());
 // Return to list of contacts page.
 echo "
  <script type=\"text/javascript\">
   window.location=\"index.php\";
  </script>
 ";
}
?>

I also built a form in the <body> section of the HTML for users to submit:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Maya's Contact CMS</title>
</head>
<body>

<h3>Insert Contact</h3>

<form name="InsertContact" method="post">
<input type="hidden" name="action" value="saveform" />
<table border="1" cellpadding="3" cellspacing="0">
<tr><th align="left">First Name:</th><td><input type="text" name="firstname" value="" /></td></tr>
<tr><th align="left">Last Name:</th><td><input type="text" name="lastname" value="" /></td></tr>
<tr><th align="left">Phone:</th><td><input type="text" name="phone" value="" /></td></tr>
<tr><th align="left">E-mail:</th><td><input type="text" name="email" value="" /></td></tr>
<tr><td align="center" colspan="2"><input type="submit" name="submit" value="Save" /></td></tr>
</table>
</form>

</body>
</html>

And that's it! I now have a rudimentary content management system built solely with HTML and PHP.

Please feel free to check it out and test the functionality here.

Friday, April 24, 2009

a simple content management system: part four

The last part of this series involves the creation of a processing page. That is, a page that handles changes to the database. Below is the code I've written to a new php file called update.php. In the last part, there were 2 links: one to edit a contact record, and the other to delete a contact record. Both of these links pointed to this update.php file.

To begin, we need to access the database once again, like so:

<?php
// Connect to the database.
$host="localhost"; // Host name.
$db_user="root"; // MySQL username.
$db_password="root"; // MySQL password.
$database="mestalil"; // Database name.
mysql_connect($host,$db_user,$db_password);
mysql_select_db($database);

The next section of code handles each of the possible scenarios:

// If the URL includes an "action" parameter and an "id" parameter.
if (isset($_GET['action']) && isset($_GET['id'])) {
 if ($_GET['action'] == "edit"){
  // Select the data record with the selected id.
  $sql = "select * from contacts where contact_id = '{$_GET['id']}'";
  $qContacts = mysql_query($sql) or die(mysql_error());
  // Load the retrieved record data into page variables.
  while($row = mysql_fetch_array($qContacts)) {
   $id = $row['contact_id'];
   $firstname = $row['first_name'];
   $lastname = $row['last_name'];
   $phone = $row['phone'];
   $email = $row['email'];
  }
 } elseif ($_GET['action'] == "del") {
  // Delete the data record with the selected id.
  $sql = "delete from contacts where contact_id = '{$_GET['id']}'";
  mysql_query($sql) or die(mysql_error());
  // Return to list of contacts page.
  echo "
   <script type=\"text/javascript\">
    window.location=\"index.php\";
   </script>
  ";
 }
} elseif (isset($_POST['submit']) && isset($_POST['id'])) { // If this page was called from a Submit button variable.
 // Reset posted variables into page variables.
 $id = $_POST['id'];
 $firstname = $_POST['firstname'];
 $lastname = $_POST['lastname'];
 $phone = $_POST['phone'];
 $email = $_POST['email'];
 // Update SQL statement
 $sql = "update contacts set first_name = '$firstname', last_name = '$lastname', phone = '$phone', email = '$email' WHERE contact_id = '$id'";
 mysql_query($sql) or die(mysql_error());
 // Return to list of contacts page.
 echo "
   <script type=\"text/javascript\">
    window.location=\"index.php\";
   </script>
 ";
}
?>

And finally, the PHP/HTML that is actually viewed by the user:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Maya's Contact CMS</title>
</head>
<body>

<form name="UpdateContact" action="update.php" method="post">
<input type="hidden" name="id" value="<?php echo $id; ?>" />
<table border="1" cellpadding="3" cellspacing="0">
<tr><th align="left">First Name:</th><td><input type="text" name="firstname" value="<?php echo $firstname; ?>" /></td></tr>
<tr><th align="left">Last Name:</th><td><input type="text" name="lastname" value="<?php echo $lastname; ?>" /></td></tr>
<tr><th align="left">Phone:</th><td><input type="text" name="phone" value="<?php echo $phone; ?>" /></td></tr>
<tr><th align="left">E-mail:</th><td><input type="text" name="email" value="<?php echo $email; ?>" /></td></tr>
<tr><td align="center" colspan="2"><input type="submit" name="submit" value="Update" /></td></tr>
</table>
</form>

</body>
</html>

Of course, I don't think that it's the greatest code ever written, but it helped me to understand how to bind a MySQL application to my PHP code to create something useful.

If I get the chance, I want to create an "Add New Contact" page, but I think that would be almost identical to the way I've handled editing an existing contact. The major difference would be the SQL statement would look something more like this:

INSERT INTO contacts ( 
 first_name, 
 last_name, 
 phone, email 
) VALUES (
 '$firstname', 
 '$lastname', 
 '$phone', 
 '$email'
);

Sunday, April 19, 2009

a simple content management system: part three

Now for the PHP coding part. Firstly, I needed to build a web page called index.php that displayed the records from my data source. The code below demonstrates connecting to the database and querying my contacts table for this data:

<?php
// Connect database.
$host=""; // Host name.
$db_user=""; // MySQL username.
$db_password=""; // MySQL password.
$database=""; // Database name.
mysql_connect($host,$db_user,$db_password);
mysql_select_db($database);
// Select all data records in table "name_list" and put them into $result.
$sql = "select * from contacts order by last_name asc";
$qContacts = mysql_query($sql);
?>

Then, within the <body> tags of the HTML, I added the following form, which was written in HTML, but includes PHP in order to bind the data from my database table to the web page display:

<form name="contacts_form">
<table border="1" cellpadding="3" cellspacing="0">
<tr>
 <th> </th>
 <th>Last Name</th>
 <th>First Name</th>
 <th>Phone</th>
 <th>E-mail</th>
 <th>Edit</th>
 <th>Delete</th>
</tr>
<?php
 // Create a variable to display row numbers
 $row_counter = 1;
 // Get all the records returned from the $qContacts database query.
 // Loop over each record returned and put all the data into a variable called $row.
 while($row=mysql_fetch_array($qContacts)){
?>
<tr>
 <td><?php echo $row_counter; ?></td>
 <td><?php echo $row['last_name']; ?></td>
 <td><?php echo $row['first_name']; ?></td>
 <td><?php echo $row['phone']; ?></td>
 <td><?php echo $row['email']; ?></td>
 <td><a href="update.php?action=edit&id=<?php echo $row['contact_id']; ?>">Edit</a></td>
 <td><a href="update.php?action=del&id=<?php echo $row['contact_id']; ?>">Delete</a></td>
</tr>
<?php
 // Increment the current value of $row_counter by 1.
 $row_counter++;
 } // End loop.
?>
</table>
</form>

Saturday, April 18, 2009

a simple content management system: part two

Before continuing with the PHP portion of my application, I need some dummy data in the database to work with to help me test my code. As such, I've run the SQL statements below to add 3 records to my "contacts" table:

INSERT contacts (
  first_name,
  last_name,
  phone,
  email
) VALUES (
  'Atticus',
  'Finch',
  '0291234567',
  'atticus@finch.com'
);

INSERT contacts (
  first_name,
  last_name,
  phone,
  email
) VALUES (
  'Jeremy',
  'Finch',
  '0291234568',
  'jem@finch.com'
);

INSERT contacts (
  first_name,
  last_name,
  phone,
  email
) VALUES (
  'Jean-Louise',
  'Finch',
  '0291234569',
  'scout@finch.com'
);

Tuesday, April 14, 2009

a simple content management system: part one

I decided to build a simple CMS to help me learn the relationship between PHP & MySQL. This first part involves creating the database table for a Contacts application that will display a list of contacts on a web page, and allow users to add, delete and edit new or existing contacts.

Log into PHPMyAdmin;
Select my database from the left-hand navigation menu, i.e. 'mestalil';
Open the SQL panel and paste in the following SQL script:

CREATE TABLE contacts (
contact_id int(11) NOT NULL auto_increment,
first_name varchar(50) NOT NULL default '',
last_name varchar(50) NOT NULL default '',
phone varchar(50) NOT NULL default '',
email varchar(50) NOT NULL default '',
PRIMARY KEY (contact_id)
) TYPE=MyISAM AUTO_INCREMENT=1;

Test that the new table was created by selecting it from the left-hand navigation menu.

The steps outlined above demonstrates how to create a new database table with 5 columns:

contact_id - this will remain hidden from the user but will allow us to identify a particular record;
first_name - this will be used to store a contact's first name;
last_name - this will be used to store a contact's last name;
phone - this will be used to store a contact's phone number; and
email - this will be used to store a contact's e-mail address.

Monday, April 6, 2009

my foray into php

according to my project plan, i'm meant to be working on the front-end of my web site, i.e. flex. however, because i successfully installed php on my machine, i've already gotten started on the back-end of things.

in looking for a neat way to structure the content management component of my project, i discovered a way of wrapping up all my php pages within the index template. this way, i don't need to have a whole bunch of duplicated code like headers and footers and css includes, etc.

basically, my index page includes a particular php template in its body section according to a $_GET['value'] in my URL string.

it took me a pretty long time to get this working, so i'm starting to wonder whether or not my project is a tad too ambitious?

Friday, April 3, 2009

the road less taken

okay, so i changed my mind... instead of taking the easy route, i decided to actually learn one of the things i try to avoid at all costs: server installations.

after uninstalling xampp and running installers for apache & php, the only tricky part was configuring apache's config file (httpd.conf) and php's config file (php.ini). but after a little bit of trial and error, i managed to get it working.

i also created a virtual host so that i can make my webroot http://ohfancyclaps/ on my local machine. this is great because, it's closer to the file structure i will eventually create on the remote web server.

NOW i'm ready to start learning PHP.

xampp

installing apache, php and mysql was a breeze with xampp. i just ran the installer and presto!

having read some of the installation documentation for the standalone products, i was lost, so thankfully some blessed person has neatly combined each of these into one easy install package. at least i have more time to spend on actual scripting rather than configuration...

Maya's Interactive Multimedia Blog