A PHP MySQL Error which I just cannot work out_问答_开发者

I'm playing with PHP/MySQL and 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>

Your update form doesn't have an element with name = "horseID", which your update function is trying to use to specify which horse to update. You've got a hidden name field there though!

It looks like the horseID variable hasn't been set in the form that is posting to the update script, you could see this easily if you outputted your SQL query. You really need to look also at sanitising your variables before using them in a query using mysql_real_escape_string and using $_GET or $_POST over $_REQUEST. If this is based on a tutorial - you really should use a different one as there is so much bad practice in your code that its actually very scary

First thing to do when debugging this kind of trouble: find out exactly what SQL statement you are trying to execute. Put an echo $sql_update before you actually execute it and make sure it is doing what you want. Then you can start tracking down where the problem is.

The 'Bobby Tables' comment is someone trying to tell you that you also need to escape user input. Use mysql_real_escape_string to ensure that user input cannot be used to attack your website.

Replace

<input type=\"hidden\" name=\"horse_name\" value=\"$row[horse_name]\">

with

<input type=\"hidden\" name=\"horseID\" value=\"$row[horseID]\">

It doesn't look like you need horse name anywhere in your update.