Part 2: Building a convention planner script using php mySQL

Home: PHP Diary | Script School | PHP Scripts | TD Scripts.com
Guts or Glory Poker PHP - A casino-style card game written entirely in PHP

[back]

01/05/03 "Part 2: How to create a convention planner script php mySQL"

[next]

Diary updates while I'm at Internext

As mentioned in my last diary entry, I'm heading to Vegas for the Internext convention. Actually I'm going to be gone in just a few hours from when I've written this and I've sort of run out of time to do a number of things I had hoped to get done (isn't that the way it goes sometimes?). So I am whipping up this diary entry to finish part 2 of the simple convention planner script I wrote to use while I'm gone. Keep in mind that this script goes inside an admin area. If you need help on creating an admin area, then you'll want to refer to an earlier diary entry: 123199: Password protection in php, .htaccess and browser based admin areas.php3

I am taking my laptop, Nomad II player (to record audio/voice clips) and will hopefully be putting together a diary of my experiences on this business trip. I do not know what my internet connection will be like in the hotel (usually it's a lousy dial-up) and I have a number of meetings planned, but I'll try to make at least one diary entry while I'm gone. Now I'm going to finish this simple convention planner script.

PART 2: Building the admin form to input data into this table

Note: Steps 1-3 are in part 1 of my diary entry which can be accessed by clicking here

STEP 4. I built a simple input form for me to enter the data into the form. Because I was a little low on time, I didn't do dropdown menus for the dates so I could select from them, I just used text boxes. I know enough to enter in mySQL DATETIME fields in the format: YYYY-MM-DD HH:MM:SS. Here's how the quick form I whipped up will look. I used a WYSIWYG editor to create the code the fastest (but then I just edit the code with my text editor to remove the extraneous code the WYSIWYG editor tends to add).

Convention ID:(this is the unique ID # for the event)
Event Start Date/Time:YYYY-MM-DD
Event End Date/Time:YYYY-MM-DD
Event Type:(meeting, etc)
Event Location:(Hotel? Convention room? etc)
Event Details:(who am I meeting, cell phone number, etc)
Event Notes:(notes of my visit)

STEP 5. Now the HTML code for this form input:

Convention ID:<input type="text" name="e_convention_id" size="7">(this is the unique ID # for the event)<br>
<font color="#008000">Start</font> Date/Time:<input type="text" name="e_start" size="20">YYYY-MM-DD<br>
Event <font color="#FF0000">End</font> Date/Time:<input type="text" name="e_end" size="20">YYYY-MM-DD<br>
Event Type:<input type="text" name="e_type" size="20">(meeting, etc)<br>
Event Location:<input type="text" name="e_location" size="20">(Hotel? Convention room? etc)<br>
Event Details:<input type="text" name="e_details" size="20">(who am I meeting, cell phone number, etc)<br>
Event Notes:<input type="text" name="e_notes" size="20">(notes of my visit) <input type="submit" value="Submit New Event">

STEP 6. I just have to wrap the form input around it for my admin area and add a hidden input action field like this:

And then my closing form tag of course: </form>

STEP 7. Now I load up my admin script and add the form to the main page so that I can easily update from there. This is a matter of cut and pasting the form above (with the correct path to my admin script, which isn't shown here). As part of keeping the comment structure down for my coding, I will update the "Last Modified" date in my scripts like is shown below:

/* Last modified 1/5/03 (12/29/02)

- add convention planner script: diary entries 010203, 010503
*/

This is helpful for me to keep track of what I last did to a specific program, and in case I need to verify if the version that I have on my local computer is the most current one.

STEP 8. The next step is creating the branch of code which will take the data from the form above and insert into the mySQL database.

<?
$CONVENTION_ID = cleanIt($_POST['e_convention_id'], 'intonly');
$event_start = cleanIt($_POST['e_start'], '');
$event_end = cleanIt($_POST['e_end'], '');
$event_type = cleanIt($_POST['e_type'], '');
$event_details = cleanIt($_POST['e_details'], '');
$event_notes = cleanIt($_POST['e_notes'], 'htmlok');

$insert = "INSERT INTO convention_planner VALUES (
0, '$CONVENTION_ID', '$event_start', '$event_end',
'$event_type', '$event_location', '$event_details', '$event_notes', 'a'
)";

mysql_query($insert, $mysql_link);
print("<br><font color='green'>Successfully added new event to convention planner</font><br>");
?>

Note: the cleanIt() function is a custom function where you will clean the incoming form data for any malicious input. Even though this is inside an admin area you must still check the input for malicious input. All user-input data must be checked -- very important.

STEP 9. Now it's time to verify that the data indeed did get written to the mySQL database. That can be done by doing a quick query via telnet or ssh, or you can skip to STEP 10 if you don't have telnet/ssh. Here is the command inside mySQL to check for our data.

SELECT * from convention_planner

See your data? Great! Now let's go to STEP 10 and make the routine which puts the data inside the table we created in STEP 1. Note: I adjusted the table width percentages for easier viewing of the data. The checkbox is for possible future editing of the records (so I could add new notes to an existing event, change the information, etc).

STEP 10. Final step is to revisit STEP 1 and output the data from the database into the table. Here is the code to do so:

<table border="0" width="100%">
<tr>
<td width="5%" bgcolor="#FFFF00"> </td>
<td width="10%" bgcolor="#FFFF00">
<p align="center"><font size="2">Date / Time start</font></td>
<td width="10%" bgcolor="#FFFF00">
<p align="center"><font size="2">Date / Time end</font></td>
<td width="18%" bgcolor="#FFFF00">
<p align="center"><font size="2">Event Type</font></td>
<td width="20%" bgcolor="#FFFF00">
<p align="center"><font size="2">Event Location</font></td>
<td width="40%" bgcolor="#FFFF00">
<p align="center"><font size="2">Details (contacts, etc)</font></td>
</tr>

<?
$query = "SELECT * from convention_planner order by event_start";
$result = mysql_query($query, $mysql_link);
while($row=mysql_fetch_row($result)) {
?>

<tr>
<td width="5%" rowspan="2">
<p align="center"><input type="checkbox" name="edit_id" value="<?echo($row[0]);?>"></td>
<td width="10%">
<p align="center"><font size="2"><?echo($row[2]);?></font></td>
<td width="10%">
<p align="center"><font size="2"><?echo($row[3]);?></font></td>
<td width="18%">
<p align="center"><font size="1"><?echo($row[4]);?></font></td>
<td width="20%">
<p align="center"><font size="1"><?echo($row[5]);?></font></td>
<td width="40%">
<p align="center"><font size="2"><?echo($row[6]);?></font></td>
</tr>
<tr>
<td width="100%" colspan="5" bgcolor="#F8F8F8"><font size="2"><b>Notes: </b>
<?echo($row[7]);?></font></td>
</tr>

<?
// end loop to print all results in table and escape php
}
?>
</table>

Notes: The $row array maps to the fields in the mySQL table outlined in STEP 3.

$row[0] = EVENT_ID INT NOT NULL AUTO_INCREMENT,
$row[1] = CONVENTION_ID INT,
$row[2] = event_start DATETIME,
$row[3] = event_end DATETIME,
$row[4] = event_type VARCHAR(100),
$row[5] = event_location VARCHAR(150),
$row[6] = event_details BLOB,
$row[7] = event_notes BLOB,
$row[8] = status CHAR(1),

I could have used mysql_fetch_array() instead but it is my understanding that mysql_fetch_row is faster. I've never done a benchmark test though to check this out. I would like to do this in a future diary entry.

Happy coding to you!

Please vote on the usefulness of this diary entry so other people will know if it is worth their time to read :)

New forum for discussion of diary entries

Have questions about this diary entry? Please discuss this diary entry by clicking here

The homeroom at Script School is available to discuss this and other php-scripts.com diary entries. You must be an enrolled student at Script School to add comments to these diary entries.

[back] 01/05/03 "Part 2: How to create a convention planner script php mySQL" [next]

Home: PHP Diary | Script School | PHP Scripts | TD Scripts.com