Sample Chapter: PHP Pocket Reference - Web Database Integration
(Page 9 of 10 )
To illustrate a complete database-driven application, we are going to build a little web application that lets people make suggestions and vote on what you should name your new baby. The example uses MySQL, but it can be changed to run on any of the databases that PHP supports.
The schema for our baby-name database looks like this:
CREATE TABLE baby_names (
name varchar(30) NOT NULL,
votes int(4),
PRIMARY KEY (name)
); This is in MySQL's query format and can be used directly to create the actual table. It simply defines a text field and an integer field. The text field is for the suggested baby name and the integer field is for the vote count associated with that name. We are making the name field a primary key, which means uniqueness is enforced, so that the same name cannot appear twice in the database.
We want this application to do a number of things. First, it should have a minimal check that prevents someone from voting many times in a row. We do this using a session cookie. Second, we want to show a fancy little barchart that depicts the relative share of the votes that each name has received. The barchart is created using a one pixel by one pixel blue dot GIF image and scaling the image using the height and width settings of the HTML <IMG> tag. We could also use PHP's built-in image functions to create a fancier looking bar.
Everything else is relatively straightforward form and database work. We use a couple of shortcuts as well. For example, instead of reading all the entries from the database and adding up all the votes in order to get a sum (which we need to calculate the percentages), we ask MySQL to do it for us with its built-in SUM function. The part of the code that displays all the names and their votes, along with the percentage bar, gets a little ugly, but you should be able to follow it. We are simply sending the correct HTML table tags before and after the various data we have fetched from the database.
Here's the full example:
<?
if($vote && !$already_voted)
SetCookie("already_voted","1");
?>
<HTML><HEAD><TITLE>Name the Baby</TITLE>
</HEAD><H3>Name the Baby</H3>
<FORM ACTION="baby.php3" METHOD="POST">
Suggestion: <INPUT TYPE=text NAME=new_name><P>
<INPUT TYPE=submit
VALUE="Submit idea and/or vote">
<?
mysql_pconnect("localhost","","");
$db = "test";
$table = "baby_names";
if($new_name) {
if(!mysql_db_query($db,
"insert into $table values
('$new_name',0)")) {
echo mysql_errno().": ";
echo mysql_error()."<BR>";
}
}
if($vote && $already_voted) {
echo "<FONT COLOR=#ff0000>Hey, you voted ";
echo "already! Vote ignored.</FONT><P>\n";
}
else if($vote) {
if(!mysql_db_query($db,
"update $table set votes=votes+1
where name='$vote'")) {
echo mysql_errno().": ";
echo mysql_error()."<BR>";
}
}
$result=mysql_db_query($db,
"select sum(votes) as sum from $table");
if($result) {
$sum = (int) mysql_result($result,0,"sum");
mysql_free_result($result);
}
$result=mysql_db_query($db,
"select * from $table order by votes DESC");
echo "<TABLE BORDER=0><TR><TH>Vote</TH>";
echo "<TH>Idea</TH><TH COLSPAN=2>Votes</TH>";
echo "</TR>\n";
while($row=mysql_fetch_row($result)) {
echo "<TR><TD ALIGN=center>";
echo "<INPUT TYPE=radio NAME=vote ";
echo "VALUE='$row[0]'></TD><TD>";
echo $row[0]."</TD><TD ALIGN=right>";
echo $row[1]."</TD><TD>";
if($sum && (int)$row[1]) {
$per = (int)(100 * $row[1]/$sum);
echo "<IMG SRC=bline.gif HEIGHT=12 ";
echo "WIDTH=$per> $per %</TD>";
}
echo "</TR>\n";
}
echo "</TABLE>\n";
mysql_free_result($result);
?>
<INPUT TYPE=submit
VALUE="Submit idea and/or vote">
<INPUT TYPE=reset>
</FORM>
</BODY></HTML>Next: Conclusion >>
More PHP Articles
More By Tim Pabst
