Ive got the following code almost working, but when i get to the get_data() function, it errors out on me with the following message:
Error performing query. You have an error in your sql syntax. check the manual that corresponds to your mysql server version for the right syntax to user near 'xxxx' at line 1.
Basically this code pulls contacts details out of a mysql d/b and displays the names in the drop down box. when you select the name and click the view button the full details of that user is selected and displayed in a html table. you then click the edit button which should allow you to update the user. however this is where the error occurs. Any help would be much appreciated.
<html>
<body>
<?
if (!$REQUEST['Submit']) {
html_form();
} elseif ($REQUEST['Submit'] == "View") {
select();
} elseif ($REQUEST['Submit'] == "Edit") {
get_data();
} elseif ($REQUEST['Submit'] == "Update") {
update();
}
function connect() {
$server="xxx";
$user="xxx";
$pass="xxx";
$db="xxx";
$link = @mysql_connect ($server, $user, $pass) or die (mysql_error());
if (!@mysql_select_db("xxx", $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() {
$conn = connect();
$sql = "SELECT fname FROM contacts";
$result = mysql_query($sql, $conn);
if (!$result) {
echo ("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
?>
<p>Please select the user details to edit</p>
<form name="update" method="post" action="<? $_SERVER['PHP_SELF']; ?>">
Name: <select name="name">
<?
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo("<option value=\"" . $row["fname"] . "\">" . $row["fname"] . "</option>\n");
}
?>
</select>
<input type="submit" name="Submit" value="View" />
</form>
<?
mysql_close ($conn);
}
function select() {
$conn = connect();
$sql = "SELECT * FROM contacts WHERE (contacts.fname = '{$_POST['name']}')";
$result = mysql_query($sql, $conn);
if (!$result) {
echo ("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
?>
<table>
<tr>
<td><strong>Name</strong></td>
<td><strong>Department</strong></td>
<td><strong>Extension</strong></td>
<td><strong>Mobile</strong></td>
<td><strong>Email</strong></td>
<td></td>
</tr>
<?
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo("<tr>\n<td>" . $row["fname"] . "</td>");
echo("<td>" . $row["department"] . "</td>");
echo("<td>" . $row["extention"] . "</td>");
echo("<td>" . $row["mobile"] . "</td>");
echo("<td>" . $row["email"] . "</td>");
echo("<td><a href=\"" . $_SERVER['PHP_SELF'] . "?name=" .$row['fname'] . "&Submit=Edit\">Edit</a></td></tr>\n\n");
}
?>
</table>
<?
mysql_close ($conn);
html_form();
}
function get_data() {
$conn = connect();
$sql = "SELECT * FROM contacts WHERE fname = " . $_REQUEST['name'] . ";";
$result = mysql_query($sql, $conn);
if (!$result) {
echo ("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
if ($row = @mysql_fetch_array($result, MYSQL_ASSOC)) {
print "<h4>$row[fname]</h4>";
print "<form name=\"user\" method=\"post\" action=\"$_SERVER[PHP_SELF]\">";
print "<table width=\"600\">
<tr>
<td width=\"150\"><strong>Name</strong></td>
<td width=\"350\"><input type=\"hidden\" name=\"fname\" value=\"$row[fname]\"></td>
<td rowspan=\"5\" valign=\"top\"><input type=\"submit\" name=\"Submit\" value=\"Update\">
</td>
</tr>
<td width=\"150\"><strong>Department</strong></td>
<td width=\"350\"><input type=\"text\" name=\"dept\" value=\"$row[department]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Extension</strong></td>
<td width=\"350\"><input type=\"text\" name=\"ext\" value=\"$row[extention]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Mobile</strong></td>
<td width=\"350\"><input type=\"text\" name=\"mobile\" value=\"$row[mobile]\"></td>
</tr>
<tr>
<td width=\"150\"><strong>Email</strong></td>
<td width=\"350\"><input type=\"text\" name=\"email\" value=\"$row[email]\"></td>
</tr>
</table>
</form>";
}
mysql_close($conn);
}
function update() {
$conn = connect();
$sql_update = "UPDATE contacts SET ";
$sql_update .= "contacts.department = '" . $REQUEST['dept'] . "' ";
$sql_update .= "contacts.extention = '" . $REQUEST['ext'] . "' ";
$sql_update .= "contacts.mobile = '" . $REQUEST['mobile'] . "' ";
$sql_update .= "contacts.email = '" . $REQUEST['email'] . "' ";
$sql_update .= " WHERE (contacts.fname = " . $_REQUEST['name'] . ")";
$result = mysql_query($sql_update, $conn);
if (!$result) {
echo ("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
print "<p> Successfully Updated</p>";
mysql_close($conn);
get_data();
}
?>
</body>
</html>
