Display Errors: On
Error Level: Not E_ALL
Register Globals: Off
I'm trying to update a MySQL db with PHP on local server. The returned error is:
"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 id = 2' at line 5."
Is there something wrong with this syntax? Help is much appreciated, thanks. I have included all the code on the page and the includes for reference.
Here is the update code itself:
======================================
Code: Select all
$id = mysql_prep($_GET['trnr']);
$name = mysql_prep($_POST['name']);
$bio = mysql_prep($_POST['bio']);
$photo_name = mysql_prep($_POST['photo_name']);
$query = "UPDATE vet_trainers SET
name = '{$name}',
bio = '{$bio}',
photo_name = '{$photo_name}',
WHERE id = {$id}";
$result = mysql_query($query, $connection);
[syntax="php" ]
Here is the page which is supposed to update the db:
=====================================================================================================
[syntax="php" ]
<?php require_once("includes/connection.php")?>
<?php require_once("includes/functions.php");?>
<?php require_once("includes/form_functions.php");?>
<?php get_selected_trainers();?>
<?php
if (intval($_GET['trnr']) == 0){
redirect_to("staff.php");
}
if (isset($_POST['submit'])){
$errors = array();
//form validation
$required_fields = array('name', 'bio', 'photo_name');
foreach($required_fields as $fieldname){
if (!isset($_POST[$fieldname]) || empty($_POST[$fieldname])){
$errors[] = $fieldname;}
}
if (empty($errors)){
//Perform Update
$id = mysql_prep($_GET['trnr']);
$name = mysql_prep($_POST['name']);
$bio = mysql_prep($_POST['bio']);
$photo_name = mysql_prep($_POST['photo_name']);
$query = "UPDATE vet_trainers SET
name = '{$name}',
bio = '{$bio}',
photo_name = '{$photo_name}',
WHERE id = {$id}";
$result = mysql_query($query, $connection);
if(mysql_affected_rows() == 1){
//Success
$message = "The trainer was successfully updated.";
} else {
//Errors Occured
$message = "The trainer update failed.";
$message .= "<br/>" . mysql_error();
}
} else {
//Errors Occured
$message = "There were " . count($errors) . " errors in the form.";
}
}//End of isset($_POST['submit']
?>
<?php get_selected_trainers();?>
<?php include("includes/header.php");?>
<div id="container" >
<h2>Edit VSTC Trainer: <?php echo $sel_trainer['name'];?></h2>
<?php if (!empty($message)) {echo "<p class=\"message\">" . $message . "</p>";} ?>
<form action="edit_trainer.php?trnr=<?php echo urlencode($sel_trainer['id']);?>" method="post"/>
<label for="basic"> Name</label>
<input type="text" name="name" id="name" data-mini="true" value="<?php echo $sel_trainer['name'];?>"/>
<br/>
<label for="textarea"> Bio Blurb</label>
<textarea name="bio" id="bio"><?php echo $sel_trainer['bio'];?></textarea>
<br/>
<label for="basic"> Photo Name (eg. photoname.jpg)</label>
<input type="text" name="photo_name" id="photo_name" data-mini="true" value="<?php echo $sel_trainer['photo_name'];?>"/>
<br/>
<input type="submit" name="submit" data-theme="b" data-icon="check" data-iconpos="left" value="Edit Trainer" />
</form>
<br/>
<a data-role="button" data-icon="minus" href="staff.php">cancel</a>
</div>
<?php include("includes/footer.php");?>
</div>
</body>
</html>
[ /syntax]
Here is my connections "includes" page:
===============================================================================================
[syntax="php"]
<?php
require("constants.php");
//1. create database connection
$connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS);
if (!$connection) {
die("Database connection failed: " . mysql_error());
}
//2. select a database to use
$db_select = mysql_select_db("meigkerc_hosted_vet",$connection);
if (!$db_select) {
die("Database selection failed: " . mysql_error());
}?>
[ /syntax]
Here is my functions "includes" page:
===============================================================================================
[syntax="php"]
<?php
//this file is the place to store all basic functions
function confirm_query($result_set)
{
if (!$result_set)
{
die("Database query failed: " . mysql_error());
}
}
function get_all_subjects()
{
global $connection;
$query = "SELECT *
FROM subjects
ORDER BY position ASC";
$subject_set = mysql_query($query, $connection);
confirm_query ($subject_set);
return $subject_set;
}
function get_pages_for_subject($subject_id)
{
global $connection;
$query = "SELECT *
FROM pages
WHERE subject_id = {$subject_id}
ORDER BY position ASC";
$page_set = mysql_query($query, $connection);
confirm_query ($page_set);
return $page_set;
}
function get_event_by_id($event_id)
{
global $connection;
$query = "SELECT * ";
$query .= "FROM vet_events ";
$query .= "WHERE id=" . $event_id;
$query .= " LIMIT 1";
$result_set = mysql_query($query, $connection);
confirm_query($result_set);
//if no rows are returned, fetch arrachy will return false
if ($event = mysql_fetch_array($result_set))
{
return $event;
} else {
return NULL;
}
}
function get_trainer_by_id($trainer_id)
{
global $connection;
$query = "SELECT * ";
$query .= "FROM vet_trainers ";
$query .= "WHERE id=" . $trainer_id;
$query .= " LIMIT 1";
$result_set = mysql_query($query, $connection);
confirm_query($result_set);
//if no rows are returned, fetch arrachy will return false
if ($trainer = mysql_fetch_array($result_set)){
return $trainer;
} else {
return NULL;
}
}
function get_selected_events()
{
global $sel_event;
if (isset($_GET['evnt']))
{
$sel_event = get_event_by_id($_GET['evnt']);
}
}
function get_selected_trainers()
{
global $sel_trainer;
if (isset($_GET['trnr'])){
$sel_trainer = get_trainer_by_id($_GET['trnr']);
}
}
date_default_timezone_set('America/Los_Angeles');
$current_date = date("Y-m-d");
?>[ /syntax]
And lastly, here is my form_functions "includes" page:
===============================================================================================
[syntax="php"]
<?php
function mysql_prep($value){
$magic_quotes_active = get_magic_quotes_gpc();
$new_enough_php = function_exists("mysql_real_escape_string");
if($new_enough_php)
{//php v4.3.0 or higher
//undo any magic quote effects so mysql_real_escape_string can do the work
if ($magic_quotes_active){
$value = stripslashes($value);
}
$value = mysql_real_escape_string($value);
} else {//before php v4.3.0
//if magic quotes arent already on then add them manually
if(!$magic_quotes_active) { $value = addslashes($value);}
//if magic quotes are active, then the slashes already exist
}
return $value;
}
function redirect_to($location = NULL){
if ($location != NULL){
header("Location: {$location}");
exit;
}
}
?>