Php MySQL gig listing script tutorial part 1

After much fannying around and general breaking of things, I finally have produced a fully operational gig listing script for which I offer this humble tutorial. Hope you like it.

You may want to glance over the guestbook tutorial as much of the code for the secure login is reused for this script. I’m going to list the code again at the end of this tutorial, but if you want an explanation of how it works, you should head over to the guestbook tutorial.

First off a couple of things must be considered. This is not an all bells and whistles script. For example, this script will not detect if you enter a gig listing which is in the past, it anticipates a modicum of common sense – a feature all too lacking in modern web programming in my opinion! Also, it presupposes that you will not be booking gigs any longer in advance than max 2 years, min 1 year. If you’ve ever worked as a musician, you’ll realise that this is more than adequete. And for all those seasoned musos turned web programmers out there, yes there is a gig cancellation feature!

The first thing we need to do before we can sound check this baby is to prepare the database for the data. Assuming you have already created a database for the guestbook (where all your fans can tell you how fantastic/ awful your last performance was) you will already have the basics in place. We’re going to re-use the connect.php file a lot, so you might want to grab it now.

We need to create a table cleverly called gigs to store all the gig listings – here’s a bit of nifty code to do just that – run this only once:

<?php
//connect to sql & db
require (‘connect.php’);//create new table
mysql_query(“CREATE TABLE gigs(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
date DATE,
description TEXT,
email VARCHAR(65),
www VARCHAR(65),
cancelled BOOLEAN)”)
or die(mysql_error());echo “Table created!”;
?>

In a nutshell: create the new table and give confirmation – no conformation means a fail.

As you can see, we’re only going to be dealing with 5 bits of information, the date being the most important, and only value that cannot be edited once the listing is created. The logic behind this is that if a date is available, a gig may be assigned to it. If a gig moves from one date to another, you will need to cancel the listing and create a new one, or simply amend the existing one to show details of a new gig, or even mark it as cancelled.

So, your database is ready? And you’re champing at the bit? I thought so! This may seem a little arse about face, but there’s a good reason here; I’m going to chuck you in at the deep end and get cracking with the add/ edit gig listings page. The reason being, if there are no gigs in the database, you can’t see if your damn script is working. ‘Tis a motivational epiphany!

I am also going to assume that you’re going to be using the secure login part of the guestbook script to keep filthy invaders out of your admin suite. If you prefer not to hop over to grab the code, it’s listed at the end of this tutorial along with all the bits you’ll need to get it going from scratch without the guestbook, but we won’t be dealing with the workings of it here.

Ok, so the first thing we should do is make a form to capture the details of the gigs we’re going to list. This page will be accessed from within the secure login, so the first bit of code has to be the bit which checks whether the user is logged in, after which the database connection wants opening. You’ll notice I’ve used require in this script as opposed to include in the previous one, simply because the script is useless without a database connection.

<?php
//check if session is not registered then redirect back to main page
session_start();
if(!session_is_registered(username)){
header(“location:main_login.php”);
}

//connect to database
require (‘connect.php’);
?>

Next step in the process will be to establish the current year, so that we can use it as the starting point for the year selection in our form. The form looks like this:

<a href=”logout.php”>logout</a>
<h1>Add or edit a Gig listing</h1>
<?php
//get todays date (year)
$yeartoday=date(“y”,time());
?>
<form method=”post” action=”<?php echo $_SERVER['PHP_SELF']; ?>”>

<label for=”email”>Day<select name=”Day”>
<?php $i=1;while ($i<32) {
echo ‘<option value=’.$i.’>’.$i.’</option>’;
$i++;
}?>
</select></label>
<label for=”email”>Month<select name=”Month”>
<?php $i=1;
while ($i<13) {
echo ‘<option value=’.$i.’>’.$i.’</option>’;
$i++;
}?>
</select></label>
<label for=”email”>Year<select name=”Year”>
<?php $i=1;
echo ‘<option value=’.$yeartoday.’ selected=”yes”>’.$yeartoday.’</option>’;
while ($i<2) {
echo ‘<option value=’.($yeartoday+$i).’>’.($yeartoday+$i).’</option>’;
$i++;
}?>
</select></label>
<br/>
<label for=”description”>Description<textarea name=”Description” rows=”6″></textarea></label>
<br/>
<label for=”email”>email<input type=”text” name=”email”></label>
<br/>
<label for=”website”>website<input type=”text” name=”Web” value=”http://”></label>
<br/>
<label for=”addgig”><input type=”submit” value=”Add Gig”></label>
</form>

Simple eh? Just to clarify, that’s three drop down menus, one for the day, one for the month and one for the year. I’ve not included a function to stop you from entering a date that doesn’t exist (e.g. 30th Feb), as I’m relying the user’s familiarity with the Gregorian calendar, and tempering of their wantonness. If you don’t understand the calendar, you shouldn’t keep a diary…

The next piece of code will assign the form data (when and only when submitted) to variables which we’ll use to write them to the database. We’ll also process the information to make sure the http:// I snuck into the ‘website’ field gets removed if no full URL is present. The most crucial bit of coding here is the date formatting which I’ll deal with next.

<?php
//if form has been submitted, assign form data to vars
if ($_POST) {

$day=$_POST['Day'];
$month=$_POST['Month'];
$year=$_POST['Year'];
$description=$_POST['Description'];
$email=$_POST['email'];
$website=$_POST['Web'];
$cancelled=$_POST['Cancelled'];

//if web address has not been entered, remove http:// from the database
if ($website==”http://”) {
$website=”";
}

//make sure day and month contain double figures
if ($day<10) {
$day=(‘0′.$day);
}if ($month<10) {
$month=(‘0′.$month);
}

//format the date
$combodate=$year.$month.$day;
$getdate=strtotime($combodate);

//create new db entry
mysql_query(“INSERT INTO gigs
(date,description,email,www,cancelled) VALUES (‘$combodate’,'$description’,'$email’,'$website’,'$cancelled’)”)
or die(mysql_error());
}

That’s quite a hefty section of code there, but it does the bulk of what’s needed for this script. Much of it is self explanatory, assigning data to vars and squirting (that’s a technical term) them into the database. In order to format the date to make it useful for this project, I’ve taken two steps. The first is making sure that the day and month contain double figures even when values are less than 10, e.g. 05 (May). My friend Elanman will probably dispute my methods here, and I’m sure he knows a better way to do this, but this is good enough for me. It’s a simple statement: if ($month<10){ $month=(‘0′.$month);} – in other words add a zero to the start of the month or day if it’s a number less than 10. Then, we combine the dates into a single value, and use the strtotime function to turn the date into a mysql formatted value to be recorded in the database. We’ll convert it back later to display on the page.

Now that’s done, we should display all the upcoming gigs from the database. Gigs which have already passed are ignored.

//get gigs from database
$query=”SELECT * FROM gigs ORDER BY date”;
$result=mysql_query($query)or die(mysql_error());

//list gigs
while($row=mysql_fetch_array($result))
{

//only display future gigs
$whatstoday=time();
if(strtotime($row['date'])>$whatstoday)
{

//format the date
$formatdate=date(‘d M’, strtotime($row['date']));

//grab the ID of the message for use in the link to edit messages from db
$id=$row['id'];
$cancel=$row['cancelled'];

//display the gig details
//is the gig cancelled?
if ($cancel==1) {
echo “<div class=’cancelled’><h4>Cancelled</h4>”;
}
else {
echo “<div class=’gig’>”;
}
echo “<p class=’date’>”.$formatdate.”</p>”;
echo “</p>”.$row['description'].”</p>”;

//only show email and web links if they are present
if($row['email']){
echo “<p>Email: <a href=’mailto:”.$row['email'].”‘>”.$row['email'].”</a></p>”;
}
if($row['www']) {
echo “<p>Web: <a href=’”.$row['www'].”‘>”.$row['www'].”</a></p>”;
}

Again, this starts off quite simply, but soon wanders into uncharted territory. Once the gigs have been extracted from the database, the first thing that should be done is to separate the upcoming gigs from those already passed. We are using the time() function to query the time right now, assign it to a var $whatstoday, and compare it to the date from the database, which we formatted before saving to the database. If the date is greater than (in other words, in the future compared to) the date today, then the script continues to process that row, or gig.

Here’s another very important bit – formatting the date. I’ve used the date() function to format the date as a simple day and month. The two arguments in this statement give the output required ‘d M’ from the input provided $row['date'].

The next bit separates out two important bits of information into vars – the id of the gig and the cancellation status. Obviously, if the gig has been cancelled, this needs to be indicated, so it must be queried early on to allow us to adjust the HTML. I’ve used an if…else loop to echo one of two classes to show whether or not the gig has been cancelled.

Ok, so we’re really getting somewhere now, all that’s left is to code in the links to edit the listings as required. I’ve used variations on the same code to create three buttons – one to edit the gig, one to cancel it, and one to delete. They all pass the row number of the gig in question to the file processing each form. It’s simple stuff.

//create a button to edit the gig
echo “<form class=’edit’ method=’post’ action=’edit_gig.php’><input type=’hidden’ name=’name’ value=’”.$id.”‘/><input type=’submit’ value=’Edit Gig’/></form>”;

//create a button to cancel the gig
echo “<form class=’edit’ method=’post’ action=’cancel_gig.php’><input type=’hidden’ name=’name’ value=’”.$id.”‘/><input type=’submit’ value=’Cancel Gig’/></form>”;

//create a button to delete the gig
echo “<form class=’edit’ method=’post’ action=’delete_gig.php’><input type=’hidden’ name=’name’ value=’”.$id.”‘/><input type=’submit’ value=’Delete Gig’/></form>”;

echo “</div><hr/>”;
}
}
?>

And that’s it! The main work is done, and what remains is much less painful than what you’ve just had to digest. I’ll walk you through cancelling, editing and deleting in part 2.

Here’s the script so far – we’ll call this file add_gig.php

<?php
//check if session is not registered then redirect back to main page
session_start();
if(!session_is_registered(username)){
header(“location:main_login.php”);
}//connect to database
require (‘connect.php’);
?><h1>Add or edit a Gig listing</h1>
<?php
//get todays date (year)
$yeartoday=date(“y”,time());
?>
<form method=”post” action=”<?php echo $_SERVER['PHP_SELF']; ?>”><label for=”email”>Day<select name=”Day”>
<?php $i=1;while ($i<32) {
echo ‘<option value=’.$i.’>’.$i.’</option>’;
$i++;
}?>
</select></label>
<label for=”email”>Month<select name=”Month”>
<?php $i=1;
while ($i<13) {
echo ‘<option value=’.$i.’>’.$i.’</option>’;
$i++;
}?>
</select></label>
<label for=”email”>Year<select name=”Year”>
<?php $i=1;
echo ‘<option value=’.$yeartoday.’ selected=”yes”>’.$yeartoday.’</option>’;
while ($i<2) {
echo ‘<option value=’.($yeartoday+$i).’>’.($yeartoday+$i).’</option>’;
$i++;
}?>
</select></label>
<br/>
<label for=”description”>Description<textarea name=”Description” rows=”6″></textarea></label>
<br/>
<label for=”email”>email<input type=”text” name=”email”></label>
<br/>
<label for=”website”>website<input type=”text” name=”Web” value=”http://”></label>
<br/>
<label for=”addgig”><input type=”submit” value=”Add Gig”></label>
</form>
<hr/><?php
//if form has been submitted, assign form data to vars
if ($_POST) {$day=$_POST['Day'];
$month=$_POST['Month'];
$year=$_POST['Year'];
$description=$_POST['Description'];
$email=$_POST['email'];
$website=$_POST['Web'];
$cancelled=$_POST['Cancelled'];//if web address has not been entered, remove http:// from the database
if ($website==”http://”) {
$website=”";
}//make sure day and month contain double figures
if ($day<10) {
$day=(‘0′.$day);
}if ($month<10) {
$month=(‘0′.$month);
}

//format the date
$combodate=$year.$month.$day;
$getdate=strtotime($combodate);

//create new db entry
mysql_query(“INSERT INTO gigs
(date,description,email,www,cancelled) VALUES (‘$combodate’,'$description’,'$email’,'$website’,'$cancelled’)”)
or die(mysql_error());
}

//get gigs from database
$query=”SELECT * FROM gigs ORDER BY date”;
$result=mysql_query($query)or die(mysql_error());//list gigs
while($row=mysql_fetch_array($result))
{//only display future gigs
$whatstoday=time();
if(strtotime($row['date'])>$whatstoday)
{

//format the date
$formatdate=date(‘d M’, strtotime($row['date']));

//grab the ID of the message for use in the link to edit messages from db

$id=$row['id'];
$cancel=$row['cancelled'];

//display the gig details
//is the gig cancelled?
if ($cancel==1) {
echo “<div class=’cancelled’><h4>Cancelled</h4>”;
}
else {
echo “<div class=’gig’>”;
}

echo “<p class=’date’>”.$formatdate.”</p>”;
echo “</p>”.$row['description'].”</p>”;

//only show email and web links if they are present
if($row['email']){
echo “<p>Email: <a href=’mailto:”.$row['email'].”‘>”.$row['email'].”</a></p>”;
}
if($row['www']) {
echo “<p>Web: <a href=’”.$row['www'].”‘>”.$row['www'].”</a></p>”;
}

//create a button to edit the gig

echo “<form class=’edit’ method=’post’ action=’edit_gig.php’><input type=’hidden’ name=’name’ value=’”.$id.”‘/><input type=’submit’ value=’Edit Gig’/></form>”;

//create a button to cancel the gig

echo “<form class=’edit’ method=’post’ action=’cancel_gig.php’><input type=’hidden’ name=’name’ value=’”.$id.”‘/><input type=’submit’ value=’Cancel Gig’/></form>”;

//create a button to delete the gig

echo “<form class=’edit’ method=’post’ action=’delete_gig.php’><input type=’hidden’ name=’name’ value=’”.$id.”‘/><input type=’submit’ value=’Delete Gig’/></form>”;

echo “</div><hr/>”;
}
}
?>

Filed under: php , diary, gig listing, mysql