mysql| Data | Database Now we have program code that allows users to enter a joke and add it to our database. Now the rest is to add it to our already done jokes display page. Because most users just want to see jokes, we don't want to make big changes to our page unless the user says they want to add a new joke. For this reason, our application should be a versatile page. Here's the code for the program:
<HTML>
...
<BODY>
<?php
If the user wants to add a joke
if (Isset ($addjoke)):
?>
<form action= "<?php Echo ($PHP _self);?>" method=post>
<p>type your joke here:<br>
<textarea name= "Joketext" rows=10 cols=40 wrap></textarea><br>
<input type=submit name= "Submitjoke" value= "SUBMIT" >
</FORM>
<?php
Else
Connect to the database server
$DBCNX = @mysql_connect ("localhost",
"Root", "mypasswd");
if (! $dbcnx) {
Echo ("<p>unable to connect to".)
"Database server at this time.</p>");
Exit ();
}
Select the Jokes database
if (! @mysql_select_db ("jokes")) {
Echo ("<p>unable to locate the joke").
"Database at this time.</p>");
Exit ();
}
If a joke has been submitted,
Add it to the database.
if ("SUBMIT" = = $submitjoke) {
$sql = "INSERT into Jokes 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 the all jokes
$result = mysql_query (
"Select Joketext from Jokes");
if (! $result) {
Echo ("<p>error performing Query:".)
Mysql_error (). "</P>");
Exit ();
}
Display the text of each joke in a paragraph
while ($row = Mysql_fetch_array ($result)) {
Echo ("<P>" $row ["Joketext"]. "</P>");
}
When clicked, this link would load this page
With the joke submission form displayed.
Echo ("<p><a href= ' $PHP _self?addjoke=1 ' >").
"Add a joke!</a></p>");
endif
?>
</BODY>
</HTML>
Now we have a separate file that contains not too much PHP code, and through this file we can show jokes in our MySQL database and add jokes to our MySQL database.
A challenge
As a homework, you can see if you can solve the problem: after each joke on the page, place a hyperlink called "Delete this joke", and when you click the connection, remove the joke from the database and display a list of jokes that have changed. Here are some tips for you:
You can also do all of the functions on a versatile page.
You need to use the SQL Delete command, which we have studied in chapter two.
This is a key issue. To delete a specified database, you need to be able to uniquely identify it. The ID in the jokes table can do this function. You must pass the ID of the joke that was deleted to the request to delete the joke. It is more appropriate to place this value in the query string of the "Delete this joke" connection.
If you think you already have the answer, or if you just want to know the solution, go to the next page. Good luck!
Conclusion
In this chapter, we learned some new PHP functions to implement the interface with MySQL database service. Using these functions, we built our first database-driven Web site, where we can publish jokes in our database online and allow visitors to add their own jokes to them.
In the fifth chapter, we go back to the MySQL command line to learn how to use the principles of relational databases and other more advanced SQL queries to describe more complex information and give visitors special permission to the jokes they add!
Resolution of the challenge
This is the "homework" solution we put forward above. To add a "Delete this joke" connection after each joke, you must make the following changes:
Before we had the "Add a joke!" at the bottom of our page A $addjoke variable is passed through the connection, which tells us that the script no longer displays the usual list of jokes, but instead displays a form that is typed into a joke. Similarly, we pass a $deletejoke variable in our "Delete this joke" connection to indicate that we want to delete a joke.
We got the value of the ID column at the same time we got the Joketext column of every joke, so we got the ID associated with every joke in the database.
We are going to delete the joke ID value given to the $deletejoke variable. This is done by inserting the ID value obtained from the database into the "Delete this joke" connection for each joke.
Using an If statement, if our $deletejoke is given a value (using the Isset function) when loading this page, we will use the value in a Sqldelete statement (the ID of the joke that will be removed) to delete the specified joke.
Here are all the source code:
<HTML>
...
<BODY>
<?php
If the user wants to add a joke
if (Isset ($addjoke)):
?>
<form action= "<?php Echo ($PHP _self);?>" method=post>
<p>type your joke here:<br>
<textarea name= "Joketext" rows=10 cols=40 wrap></textarea><br>
<input type=submit name= "Submitjoke" value= "SUBMIT" >
</FORM>
<?php
Else
Connect to the database server
$DBCNX = @mysql_connect (
"LocalHost", "root", "mypasswd");
if (! $dbcnx) {
Echo ("<p>unable to connect to".)
"Database server at this time.</p>");
Exit ();
}
Select the Jokes database
if (! @mysql_select_db ("jokes")) {
Echo ("<p>unable to locate the joke").
"Database at this time.</p>");
Exit ();
}
If a joke has been submitted,
Add it to the database.
if ("SUBMIT" = = $submitjoke) {
$sql = "INSERT into Jokes 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>");
}
}
If a joke has been deleted,
Remove it from the database.
if (Isset ($deletejoke)) {
$sql = "DELETE from Jokes".
"WHERE id= $deletejoke";
if (mysql_query ($sql)) {
Echo ("<p>the joke has been deleted.</p>");
} else {
Echo ("<p>error deleting joke:".)
Mysql_error (). "</P>");
}
}
Echo ("<P> here are all the jokes".
"In our Database: </P>");
Request the ID and text of the all jokes
$result = mysql_query (
"Select ID, joketext from jokes");
if (! $result) {
Echo ("<p>error performing Query:".)
Mysql_error (). "</P>");
Exit ();
}
Display the text of each joke in a paragraph
With a ' Delete this joke ' link next to each.
while ($row = Mysql_fetch_array ($result)) {
$jokeid = $row ["ID"];
$joketext = $row ["Joketext"];
Echo ("<P> $joketext".
"<a href= ' $PHP _self?deletejoke= $jokeid ' > '.
"Delete this joke</a></p>");
}
When clicked, this link would load this page
With the joke submission form displayed.
Echo ("<p><a href= ' $PHP _self?addjoke=1 ' >").
"Add a joke!</a></p>");
endif
?>
</BODY>
</HTML>
