我收到错误，我无法解决.. PHP中的SQL错误

Question

I got error and i can't fix..

response error:
Database query failedYou have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near '' as Loreta Punzalan'}, 
gender  = {'Female'}, dob = {'01/23/20' at line 2 

JS AJAX

$("button.update_btn").click(function(event){
        //alert("test");
        var clickedEditid = this.id.split('-');
        var dbId = clickedEditid[1];
        var mydata = '&recordToBeUpdated='+ dbId;
        var mydatas = $('#instructorUps').serialize();
            var datas = mydatas+mydata;
        //alert(datas);
        $.ajax({
            type:"POST",
            url:"<?php echo get_stylesheet_directory_uri(); ?>/includes/response_instructor_update.php?>",
            dataType:"text",
            data: datas,
            success: function(response){
                alert(response);
            // $('#ins_'+dbId).fadeIn();
                //$('#instructorModal').append(response);
                location.reload();
                },
            error:function(xhr, ajaxOption, thrownError){
                alert(thrownError);
                }

            });
        });

Response_instructor_update.php

<?php
// Response for AJAX

require_once("connection.php");
require_once("functions.php");
if(isset($_POST['fullnameup'])){

        $idupdate     =   mysql_prep($_POST["recordToBeUpdated"]);
        $fullname     =   mysql_prep($_POST["fullnameup"]);
        $username     =   mysql_prep($_POST["usernameup"]);
        $gender       =   mysql_prep($_POST["genderup"]);
        $dob          =   mysql_prep($_POST["dobup"]);
        $nationality  =   mysql_prep($_POST["nationalityup"]);
        $mobile       =   mysql_prep($_POST["mobileup"]);
        $emailadd     =   mysql_prep($_POST["emailaddup"]);
        $address      =   mysql_prep($_POST["addressup"]);
        $zipcode      =   mysql_prep($_POST["zipcodeup"]);
$query  = "UPDATE instructor SET    
            instructor    = {'$fullname'},
            gender        = {'$gender'},
            dob           = {'$dob'},
            nationality   = {'$nationality'},
            mobile       = {$mobile},
            emailadd     = {'$emailadd'},
            address      = {'$address'},
            zipcode      = {$zipcode},
            username     = {'$username'}
            WHERE instructorid= {$idupdate}";   
    $updatedInstructor = mysql_query($query,$connection);
    confirm_query($updatedInstructor);

    if (mysql_affected_rows() == 1){

        echo "TRUE";
        mysql_close($connection);
        }else{
            die("Database query failed". mysql_error());

            } 
    }
    ?>

Answer 1

I think you have the right idea. However, you have a slight syntax issue.

I believe your variables are not evaluating to strings, and MySQL is expecting strings. Try the following instead:

$query  = "UPDATE instructor SET    
        instructor    = '{$fullname}',
        gender        = '{$gender}',
        dob           = '{$dob}',
        nationality   = '{$nationality}',
        mobile       = {$mobile},
        emailadd     = '{$emailadd}',
        address      = '{$address}',
        zipcode      = {$zipcode},
        username     = '{$username}',
        WHERE instructorid= {$idupdate}"; 

Note that the single quotes (') are OUTSIDE of the curly brackets ({}). You must add the quotes anywhere that MySQL expects strings (column type is text/varchar/etc..).

- Update -

As sverri said, they actually are being treated as strings. The issue here is that php's interpolation has minor variations: you may either use {$variable} OR simply $variable inside double quoted (") strings. In the case of your post, you had {'$variable'} which was interpolating using the $variable method, and just treating the curly brackets as part of the string, when you had the intention of PHP replacing them.