<?php
#########################################################
# class05-sites6.php - Submit Your Favorite Site with Tags
#########################################################
$scriptName = $_SERVER['PHP_SELF'];
$pageTitle = "Class 5 Example - Submit Your Favorite Site with Tags";
# Make sure we display errors to the browser
error_reporting(E_ALL ^ E_NOTICE);
ini_set('display_errors', 1);
# Get our DB info
require "info.php";
#########################################################
# Connect to the database.
#########################################################
$connection = mysql_connect($mySqlHostname, $mySqlUsername, $mySqlPassword);
if (!$connection)
die("Error " . mysql_errno() . " : " . mysql_error());
# Select the DB
$db_selected = mysql_select_db($mySqlDatabase, $connection);
if (!$db_selected)
die("Error " . mysql_errno() . " : " . mysql_error());
#########################################################
# Define our widgets and initial values
#########################################################
### New site suhmission widgets ###
$textTitleName = "title"; # The name of text widget that has new website name
$textTitleValue = $_POST[$textTitleName]; # The value of title widget
$textTagName = "tag"; # The name of text widget that has tags
$textTagValue = $_POST[$textTagName]; # The value of tags widget
$textUrlName = "url"; # The name of text widget that has new website url
$textUrlValue = $_POST[$textUrlName]; # The value of url widget
if (empty($textUrlValue)) $textUrlValue = "http://"; # Initialize if blank
$textDescName = "desc"; # The name of text widget that has new website description
$textDescValue = $_POST[$textDescName]; # The value of desc widget
$submitName = "DataAction";
$submitValue = "Submit New Site"; # This is what it will say on our submit site button
### Status variables ###
$statusMsg = ""; # Gives response back to user (i.e. "Thank you for your ...")
$hasErrors = 0; # Keeps track of whether there are input errors
#########################################################
# Update the votes for selected site if submit button was pressed
#########################################################
if ($_POST[$submitName]==$submitValue)
{ # Someone submitted new site
# Error Checking
$noTitle = 0; # Flag that is set if title widget was blank
$noUrl = 0; # Flag that is set if url widget was blank
$noDesc = 0; # Flag that is set if description widget was blank
$title = trim($textTitleValue);
if (empty($title))
{ # If blank, set general error flag, name error flag, and general error message
$hasErrors = 1;
$noTitle = 1;
$statusMsg = "There were errors in your site submission.";
}
$url = trim($textUrlValue);
if (empty($url) || !strcasecmp($url,"http://"))
{ # If blank, set general error flag, name error flag, and general error message
$hasErrors = 1;
$noUrl = 1;
$statusMsg = "There were errors in your site submission.";
}
$desc = trim($textDescValue);
if (empty($desc))
{ # If blank, set general error flag, name error flag, and general error message
$hasErrors = 1;
$noDesc = 1;
$statusMsg = "There were errors in your site submission.";
}
# If we had no errors, we can put it in the database
if (!$hasErrors)
{ # Replace any single quotes in our text fields
$titleDB = str_replace("'", "''", $title);
$urlDB = str_replace("'", "''", $url);
$descDB = str_replace("'", "''", $desc);
# Create the SQL query
$SqlStatement = "INSERT INTO class05_table2 (title,url,description)
VALUES ('$titleDB','$urlDB','$descDB') ";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
$statusMsg = "Thank you for your site submission!";
# Reset the text widgets to accept input once again for the next submission
$textTitleValue = "";
$textUrlValue = "http://";
$textDescValue = "";
# Now get the ID of this site and use it to populate any tags
if (!empty($textTagValue))
{
# mysql_insert_id returns last ID created
$site_id = mysql_insert_id($connection);
# Get our individual tags
$tag_array = split(",",$textTagValue);
foreach ($tag_array as $tag)
{
$tag = trim($tag);
if (!empty($tag))
{
# Does this tag exist? If so, get it's ID
$tag_id = -1;
$tagDB = str_replace("'", "''", $tag);
$SqlStatement = "SELECT id FROM class05_tag WHERE name='$tagDB'";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
if ($row = mysql_fetch_array($result,MYSQL_NUM))
{ $tag_id = $row[0];
}
else
{ # We need to insert it and get its ID
$SqlStatement = "INSERT INTO class05_tag (name) VALUES ('$tagDB')";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
# mysql_insert_id returns last ID created
$tag_id = mysql_insert_id($connection);
}
if ($site_id>0 && $tag_id>0)
{ $SqlStatement = "INSERT INTO class05_table2_x_tag (site_id,tag_id)
VALUES ($site_id,$tag_id)";
$result = mysql_query($SqlStatement,$connection);
if (!$result) die("Error " . mysql_errno() . " : " . mysql_error());
}
}
}
}
# Clear the tag field since successful submission
$textTagValue = "";
}
}
#########################################################
# Visual Info
#########################################################
$table_header_color = "#99cccc";
$table_row_color = "#e8e8e8";
?>
<html>
<head>
<title><?=$pageTitle?></title>
<link rel="stylesheet" href="css/classnotes.css" type="text/css" media="all" />
</head>
<body>
<b><?=$pageTitle?></b>
<p>
Submit Your Favorite Website:
<p>
<?
# If we put anything in the general status message, then print it
if (!empty($statusMsg))
{ print '<font color="#990000"><b>'.$statusMsg.'</b></font> <p>';
}
# Start the form and write the title widget
echo <<<END
<table border=0 cellpadding=3 cellspacing=1 class="arial13">
<tr bgcolor="$table_row_color">
<td align="left">
<form action="$scriptName" method="POST" enctype="application/x-www-form-urlencoded">
<nobr><b>Title:</b></nobr>
</td>
<td align="left">
<input type="text" name="$textTitleName" value="$textTitleValue" size="32" maxlength="255">
END;
# If we had a problem with the title, show error message here
if ($hasErrors && $noTitle)
{ print '<br><font color="#ff0000"><b>Please provide a website title</b></font>';
}
# Write the url widget
echo <<<END
</td>
</tr>
<tr bgcolor="$table_row_color">
<td align="left">
<nobr><b>URL:</b></nobr>
</td>
<td align="left">
<input type="text" name="$textUrlName" value="$textUrlValue" size="32" maxlength="255">
END;
# If we had a problem with the url, show error message here
if ($hasErrors && $noUrl)
{ print '<br><font color="#ff0000"><b>Please provide a website URL</b></font>';
}
# Write the description widget
echo <<<END
</td>
</tr>
<tr bgcolor="$table_row_color">
<td align="left">
<nobr><b>Description:</b></nobr>
</td>
<td align="left">
<input type="text" name="$textDescName" value="$textDescValue" size="32" maxlength="255">
END;
# If we had a problem with the description, show error message here
if ($hasErrors && $noDesc)
{ print '<br><font color="#ff0000"><b>Please provide a description of this website</b></font>';
}
# Write the tag widget, and put in the submit
echo <<<END
</td>
</tr>
<tr bgcolor="$table_row_color">
<td align="left">
<nobr><b>Tags:</b></nobr>
</td>
<td align="left">
<input type="text" name="$textTagName" value="$textTagValue" size="32" maxlength="255">
</td>
</tr>
<tr><td height="5" colspan="2"></td></tr>
<tr>
<td></td>
<td align="left" valign="top">
<input type="submit" name="$submitName" value="$submitValue"></td></tr>
</table></form>
<p>
END;
#########################################################
# Use SELECT to show favorite sites
#########################################################
echo <<<END
<table cellspacing="1" cellpadding="5" border="0" class="arial13">
<tr bgcolor="$table_header_color">
<td align="left"><b>Title</b></td>
<td align="left"><b>URL</b></td>
<td align="left"><b>Description</b></td>
<td align="left"><b>Tags</b></td>
<td align="left"><b>Votes</b></td>
</tr>
END;
# Retrieve all rows from the table and put in normal array.
$SqlStatement = "SELECT s.id, s.title, s.url, s.description, s.votes, t.id, t.name
FROM class05_table2 s
LEFT OUTER JOIN class05_table2_x_tag x
ON s.id = x.site_id
LEFT OUTER JOIN class05_tag t
ON t.id = x.tag_id
ORDER BY s.votes desc, s.title";
# Run the query on the database through the connection
$result = mysql_query($SqlStatement,$connection);
if (!$result)
die("Error " . mysql_errno() . " : " . mysql_error());
$row_prev = array();
$tag_text = "";
while ($row = mysql_fetch_array($result,MYSQL_NUM))
{
if ($row_prev[0]>0 && $row_prev[0]!=$row[0])
{
# Found new site so write the previous site if we recorded it
echo <<<END
<tr bgcolor="$table_row_color">
<td align="left"><a target="newSite" href="$row_prev[2]">$row_prev[1]</a></td>
<td align="left">$row_prev[2]</td>
<td align="left">$row_prev[3]</td>
<td align="left">$tag_text</td>
<td align="right">$row_prev[4]</td>
</tr>
END;
# Clear the tag text
$tag_text = "";
}
# Now record the tag text for this entry if exists
if ($row[5]>0)
{ if ($tag_text!="") $tag_text .= ", ";
$tag_text .= $row[6];
}
# Record the current row for the next go round
$row_prev = $row;
}
# Write the last site if we have valid site ID
if ($row_prev[0]>0)
{
echo <<<END
<tr bgcolor="$table_row_color">
<td align="left"><a target="newSite" href="$row_prev[2]">$row_prev[1]</a></td>
<td align="left">$row_prev[2]</td>
<td align="left">$row_prev[3]</td>
<td align="left">$tag_text</td>
<td align="right">$row_prev[4]</td>
</tr>
END;
}
echo <<<END
</table><p>
END;
#########################################################
# Disconnect from the database.
#########################################################
mysql_close($connection);
?>
</body>
</html>