Page 1 of 1

Updating MySQL, syntax error

Posted: Tue Mar 20, 2012 3:52 am
by remsleepwagon
PHP Version: 5.3.8
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;
		}
}
?>

Re: Updating MySQL, not working, but no errors

Posted: Tue Mar 20, 2012 4:30 am
by azycraze
what do you mean by this line of code

==============================
$query = "UPDATE vet_trainers SET
$name = '{$name}',
$bio = '{$bio}',
$photo_name = '{$photo_name}',
WHERE id = {$id}";
=========================

delete the $ sign before the table names (name,bio,photo_name).
===============================

Re: Updating MySQL, not working, but no errors

Posted: Tue Mar 20, 2012 2:27 pm
by remsleepwagon
Thanks for responding. Following your advice, I replaced the above code with this code, but it had no effect:
======================
$query = "UPDATE vet_trainers SET
name = '{$name}',
bio = '{$bio}',
photo_name = '{$photo_name}',
WHERE id = {$id}";
======================

Re: Updating MySQL, syntax error

Posted: Tue Mar 20, 2012 6:43 pm
by remsleepwagon
Problem solved, it was the final comma before the WHERE that caused the error.