Notice: Update Failed: You have an error in your SQL syntax;

[TeddyKiller]

This error only happens when none of the checkboxes are checked.

 

Notice: Update Failed: You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for theright syntax to use near 'WHERE `user_id`='11'' at line 1 in /home/jeanie/public_html/editprofile.php on line 200

 

My form validation is posted below. I'm not too sure why it happens, as far as I'm concerned... it checks if a checkbox is submitted, and if so.. then it goes through the implode() process etc. Can you help? Code is below.

      if(isset($_POST['submit'])){
        $user = check_user($secret_key);
            $profile = check_profile();
      
          // grab the form fields.
        $_POST['proname'] = clean($_POST['proname'],1,0,0);
        $_POST['protag'] = clean($_POST['protag'],1,0,0);
        
        $fields = array();
          $fields[] = (isset($_POST['proname']) && !empty($_POST['proname'])) ? "`pro_name` = '" . $_POST['proname'] . "'" : '';
        $fields[] = (isset($_POST['protag']) && !empty($_POST['protag'])) ? "`pro_tag` = '" . $_POST['protag'] . "'" : '';
        
        if(isset($_POST['checkbox']) && !empty($_POST['checkbox'])) {
            $checkbox = implode(', ', $_POST['checkbox']);
            $fields[] = "`interested_in` = '". $checkbox . "'";
        }

          foreach ($fields as $key => $field) {
            if (empty($field)) {
                unset($fields[$key]);
            }
          }

          $query = mysql_query("UPDATE profile SET " . implode(", ", $fields) . " WHERE `user_id`='$user->id'") or trigger_error("Update Failed: " . mysql_error());
          if($query) {
              echo '<br /><span style="color:#e11919">Profile updated!</span>';
        }          
    }

[Ken2k7]

When you have no checkbox selected, then implode(", ", $fields) will return an empty string; ergo, your SQL will be UPDATE profile SET  WHERE `user_id`='11'.

[TeddyKiller]

No, not quite because $_POST['proname'] and $_POST['protag'] will have values. Though I can check this by doing ..

if(!$fields){ do this }

[TeddyKiller]

I changed it to this

		if(isset($fields)) {
	$query = mysql_query("UPDATE profile SET " . implode(", ", $fields) . " WHERE `user_id`='$user->id'") or trigger_error("Update Failed: " . mysql_error());
  		if($query) {
		  echo '<br /><span style="color:#e11919">Profile updated!</span>';
		}	
	}

It still attempts to execute the query. So there for, it is getting some values in the other 2 fields, other than the checkbox. It's just the checkboxes that are causing the problem.

Any ideas anyone?

[Ken2k7]

Do me a favor.

 

Change

$query = mysql_query("UPDATE profile SET " . implode(", ", $fields) . " WHERE `user_id`='$user->id'") or trigger_error("Update Failed: " . mysql_error());

 

To

$query = "UPDATE profile SET " . implode(", ", $fields) . " WHERE `user_id`='$user->id'";

echo $query;

$query = mysql_query($query) or trigger_error("Update Failed: " . mysql_error());

 

Tell me what the prints out.

[TeddyKiller]

sh*t. Your correct..

UPDATE profile SET WHERE `user_id`='11'

Prints that out.. but I don't get it. If the other two fields have values.. then it shouldn't be like that.. It shouldn't actually be executing the query if there are no fields. I'm really confused

[TeddyKiller]

Oh.. damnit. For some reason it's not getting the values of the other two fields. ... Why? the $_posts are the same as the names in the fields.. so why is it not doing it?

[TeddyKiller]

The problem is.. the clean function. If I comment those lines out, it works.. if I dont.. then it don't work...

And I just looked at the function.. $arr isn't defined.

I'm such a numpty!

 

Sorry about wasting your time. I guess it was one of these things I've stared at too long.