Publishing MySQL Data on the Web - Insert Data into the Database

sivaji · Jan 11, 2008, 03:22 PM

Publishing MySQL Data on the Web

Inserting Data into the Database

In this section, we'll see how we can use the tools at our disposal to allow site visitors to add their own jokes to the database. If you enjoy a challenge, you might want to try to figure this out on your own before you read any further. There is little new material in this section, but it's mostly just a sample application that incorporates everything we've learned so far.

If you want to let visitors to your site type in new jokes, you'll obviously need a form. Here's the code for a form that will fit the bill:

File: jokes.php (excerpt)

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<label>Type your joke here:

<textarea name="joketext" rows="10" cols="40">
</textarea></label>

<input type="submit" value="SUBMIT" />
</form>

As we've seen before, when submitted, this form will load the very same page (because we used the $_SERVER['PHP_SELF'] variable for the form's action attribute) with one difference: a variable will be attached to the request. The variable, joketext, will contain the text of the joke as typed into the text area, and will appear in the $_POST and $_REQUEST arrays created by PHP.

To insert the submitted joke into the database, we use mysql_query to run an INSERT query, using the value stored in $_POST['joketext'] to fill in the joketext column in the query:

File: jokes.php (excerpt)

if (isset($_POST['joketext'])) {
$joketext = $_POST['joketext'];
$sql = "INSERT INTO joke SET
joketext='$joketext',
jokedate=CURDATE()";
if (@mysql_query($sql)) {
echo '<p>Your joke has been added.</p>';
} else {
echo '<p>Error adding submitted joke: ' .
mysql_error() . '</p>';
}
}

The one new trick in this example is shown in bold. The MySQL function CURDATE() is used here to assign the current date as the value of the jokedate column. MySQL actually has dozens of these functions, but we'll introduce them only as required. For a complete MySQL function reference, refer to Appendix B.

We now have the code that will allow a user to type a joke and add it to our database. All that remains is to slot it into our existing joke viewing page in a useful fashion. As most users will only want to view jokes, we don't want to mar our page with a big, ugly form unless the user expresses an interest in adding a new joke. For this reason, our application is well suited for implementation as a multipurpose page. Here's the full code:

File: jokes.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>The Internet Joke Database</title>
<meta http-equiv="content-type"
content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php if (isset($_GET['addjoke'])): // User wants to add a joke
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<label>Type your joke here:

<textarea name="joketext" rows="10" cols="40">
</textarea></label>

<input type="submit" value="SUBMIT" />
</form>
<?php else: // Default page display
// Connect to the database server
$dbcnx = @mysql_connect('localhost', 'root', 'mypasswd');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}
// Select the jokes database
if (!@mysql_select_db('ijdb')) {
exit('<p>Unable to locate the joke ' .
'database at this time.</p>');
}
// If a joke has been submitted,
// add it to the database.
if (isset($_POST['joketext'])) {
$joketext = $_POST['joketext'];
$sql = "INSERT INTO joke SET
joketext='$joketext',
jokedate=CURDATE()";
if (@mysql_query($sql)) {
echo '<p>Your joke has been added.</p>';
} else {
echo '<p>Error adding submitted joke: ' .
mysql_error() . '</p>';
}
}
echo '<p>Here are all the jokes in our database:</p>';
// Request the text of all the jokes
$result = @mysql_query('SELECT joketext FROM joke');
if (!$result) {
exit('<p>Error performing query: ' .
mysql_error() . '</p>');
}
// Display the text of each joke in a paragraph
while ($row = mysql_fetch_array($result)) {
echo '<p>' . $row['joketext'] . '</p>';
}
// When clicked, this link will load this page
// with the joke submission form displayed.
echo '<p><a href="' . $_SERVER['PHP_SELF'] .
'?addjoke=1">Add a Joke!</a></p>';
endif;
?>
</body>
</html>

Load this up and add a new joke or two to the database via your browser. There we go! With a single file that contains a little PHP code, we're able to view existing jokes in, and add new jokes to, our MySQL database.

A Challenge

As "homework", see if you can figure out how to place next to each joke on the page a link labelled Delete this joke that, when clicked, will remove that joke from the database and display the updated joke list. Here are a few hints to get you started:

You'll still be able to do it all in a single multipurpose page.
You'll need to use the SQL DELETE command, which we learned about in Chapter 2.
This is the tough one: to delete a particular joke, you'll need to be able to identify it uniquely.

The id column in the joke table was designed to serve this purpose. You're going to have to pass the ID of the joke to be deleted with the request to delete a joke. The query string of the Delete this joke link
is a perfect place to put this value.

If you think you have the answer, or if you'd just like to see the solution, turn the page. Good luck!

Discussion Community

News:

Publishing MySQL Data on the Web - Insert Data into the Database

sivaji