Hi guys - extreme novice here playing with PHP/MySQL trying to teach myself online but can't figure this out for the life of me...
I have been following a PHP tutorial at http://www.keithjbrown.co.uk/vworks/php/php_p5.php
This page is at tasmanianracing.com/horses.php
I'm getting the following mysql error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (horses.horseID = )' at line 1
which is being thrown up from my function update_horse()
My code is below - if someone could help me out I'd be forever grateful!
<html>
<head>
<title>Horses | Horse Database</title>
</head>
<body>
<?php
if (!$_REQUEST['Submit']) {
html_form();
} elseif ($_REQUEST['Submit'] == "View Horse") {
select_horse();
} elseif ($_REQUEST['Submit'] == "Edit") {
get_data();
} elseif ($_REQUEST['Submit'] == "Update") {
update_horse();
}
function my_conn() {
/* sets the variables for MySQL connection */
$server = "***"; // this is the server address and port
$username = "***"; // this is the mysql username
$password = "***"; // this is the mysql password
/* connects to the MySQL server */
$link = @mysql_connect ($server, $username, $password)
or die (mysql_error());
/* defines the active database for the connection */
if (!@mysql_select_db("tashorse_tasform", $link)) {
echo "<p>There has been an error. This is the error message:</p>";
echo "<p><strong>" . mysql_error() . "</strong></p>";
echo "Please contact your systems administrator with the details";
}
return $link;
}
function html_form() {
?>
<p>Please enter the search term for the horse</p>
<form name="horsesearch" method="post" action="<? echo $_SERVER['PHP_SELF']; ?>">
Name of horse: <input type="text" name="horse_name">
<input type="submit" name="Submit" value="View Horse" />
</form>
<?
}
function select_horse() {
?>
<h4>Horse Search</h4>
<?
$conn = my_conn();
/* Sets the SQL Query */
$sql = "SELECT * FROM horses";
$sql .= " WHERE (horses.horse_name = '{$_POST['horse_name']}')";
/* Passes a Query to the Active Database */
$result = mysql_query($sql, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
/* starts the table and creates headings */
?>
<table>
<tr>
<td><strong>Horse Name</strong></td>
<td><strong>Year Foaled</strong></td>
<td><strong>Trainer</strong></td>
<td><strong>Owners</strong></td>
<td><strong>Silks</strong></td>
<td></td>
</tr>
<?
/* retrieves the rows from the query result set and puts them into
a HTML table row */
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo("<tr><td>" . $row["horse_name"] . "</td>");
echo("<td>" . $row["year_foaled"] . "</td>");
echo("<td>" . $row["trainer"] . "</td>");
echo("<td>" . $row["owners"] . "</td>");
echo("<td>" . $row["silks"] . "</td>");
echo("<td><a href=\"" . $_SERVER['PHP_SELF'] . "?horseID=" .$row['horseID'] . "&Submit=Edit\">Edit</a></td></tr>");
}
/* closes the table */
?>
</table>
<?
/* closes connection to the MySQL server */
mysql_close ($conn);
/* Displays HTML Form */
html_form();
}
function get_data() {
/* Calls our connection function */
$conn = my_conn();
/* Defines query */
$sql = "SELECT * FROM horses WHERE (horses.horseID = " . $_REQUEST['horseID'] . ")";
/* Passes query to database */
$result = mysql_query($sql, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
/* creates our row array with an if statement to report errors */
if ($row = @mysql_fetch_array($result, MYSQL_ASSOC)) {
/* prints out the horse name */
print "<h4>$row[horse_name]</h4>";
/* prints out our HTML form '\"' */
print "<form name=\"horseupdate\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">";
/* prints out our HTML table and fields 'escaping' any double quotes '\"' */
print "<table width=\"600\">
<tr>
<td width=\"150\"><strong>Horse Name</strong></td>
<td width=\"350\"><input type=\"hidden\" name=\"horse_name\" value=\"$row[horse_name]\"></td>
<td rowspan=\"5\" valign=\"top\">
<input type=\"submit\" name=\"Submit\" value=\"Update\">
</td>
</tr>
<tr>
<td width=\"150\"><strong>Year Foaled</strong></td>
<td width=\"350\"><input type=\"text\" size =\"4\" name=\"year_foaled\" value=\"$row[year_foaled]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Trainer</strong></td>
<td width=\"350\"><input type=\"text\" size =\"40\" name=\"trainer\" value=\"$row[trainer]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Owners</strong></td>
<td width=\"350\"><input type=\"text\" size =\"40\" name=\"owners\" value=\"$row[owners]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Silks</strong></td>
<td width=\"350\"><input type=\"text\" size =\"40\" name=\"silks\" value=\"$row[silks]\"></td>
</tr>
</table>
</form>";
} else {
echo("There has been an error" . mysql_error());
}
/* closes connection */
mysql_close ($conn);
}
function update_horse() {
/* Calls our connection function */
$conn = my_conn();
/* Defines query */
$sql_update = "UPDATE horses SET ";
$sql_update .= "horses.year_foaled = '" . $_REQUEST['year_foaled'] . "', ";
$sql_update .= "horses.trainer = '" . $_REQUEST['trainer'] . "', ";
$sql_update .= "horses.owners = '" . $_REQUEST['owners'] . "', ";
$sql_update .= "horses.silks = '" . $_REQUEST['silks'] . "', ";
$sql_update .= "WHERE (horses.horseID = " . $_REQUEST['horseID'] . ")";
/* Passes query to database */
$result = mysql_query($sql_update, $conn);
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
/* Prints success message */
print "<p> Successfully Updated</p>";
/* closes connection */
mysql_close ($conn);
/* Calls get_data() function */
getdata();
}
?>
</body>
</html>