MySQL/PHP/jQuery - Issue with query

Question

I'm trying to create an user panel for first name/last name changing.

It seems like the $ajax form handling is done well, since i can console.log(data) and see {fname: "Damian", lname: "Doman", id: "20"}.

Also, php class seems to run too as after i send the form i get print 'Your name has been changed! To go back, please click <a href="userpage.php">here</a>'; as it is meant to be.

Finally, the query itself (UPDATE members SET fname = ?, lname = ? WHERE id = ?) when fired manually in SQL console with values instead of question marks works flawless too.

Still, the whole script just won't work, i get message saying it's succesful but still the table won't get UPDATE.

HTML:

<div class="changename-form-wrapper">
    <h1>Define your first and last names here:</h1>
    <div class="form-group">
        <input type="text" name="fname" id="fname" tabindex="3" class="form-control ui-autocomplete-input" placeholder="First name..." value="">
    </div>
    <div class="form-group">
        <input type="text" name="lname" id="lname" tabindex="4" class="form-control ui-autocomplete-input" placeholder="Last name...">
    </div>
    <div class="form-group">
        <input type="text" name="id" id="id" tabindex="4" class="form-control ui-autocomplete-input" style="display:none;" value="<?=$_SESSION['user']['id']?>">
    </div>
    <div class="form-group">
        <div class="row">
            <div class="col-sm-6 col-sm-offset-3">
                <input type="button" name="name-submit" id="name-submit" tabindex="4" class="form-control btn btn-login" value="Apply">
            </div>
        </div>
    </div>
</div>
<div class="alert-space">
    <div class="ui-widget" style="display:none;">
       <div class="ui-state-error ui-corner-all">
           <div class="p"><span class="ui-icon ui-icon-alert"></span></div><div class="p"><div class="ui-state-error-text"></div></div>
       </div>
    </div>
</div>

jQuery/AJAX:

$("#name-submit").click(function(){
                var data = { "fname": $('#fname').val(), "lname": $('#lname').val(), "id": $('#id').val() };
                if($("#fname").val() != "" && $("#lname").val() != ""){
                    $.ajax({
                      method: "POST",
                      url: 'changename.php',
                      data: data,
                    }).done(function( msg ) {
                        console.log(data);
                        if(msg !== ""){
                            $(".ui-widget").show();
                            $(".ui-state-error-text").html(msg);
                        }else{
                            window.location = "userpage.php";
                        }
                    });
                }else{
                    $(".ui-widget").show();
                    $(".ui-state-error-text").html("<strong>Error:</strong> Please, fill in both the first and last name.");
                }
            });

CHANGENAME.PHP FORM HANDLER:

<?php
    require_once '../../class/user.php';
    require_once '../../config.php';

    $id = filter_input(INPUT_POST, 'id', FILTER_SANITIZE_STRING);
    $fname = filter_input(INPUT_POST, 'fname', FILTER_SANITIZE_STRING);
    $lname = filter_input(INPUT_POST, 'lname', FILTER_SANITIZE_STRING);

    if( $user->userUpdate($id, $fname, $lname)) {
        print 'Your name has been changed! To go back, please click <a href="userpage.php">here</a>';
        die;
    } else {
        $user->printMsg();
        die;
    };

And finally the userUpdate($id, $fname, $lname) class code:

public function userUpdate($id,$fname,$lname){
        $pdo = $this->pdo;
        if(isset($id) && isset($fname) && isset($lname)){
            $stmt = $pdo->prepare('UPDATE members SET fname = ?, lname = ? WHERE id = ?');
            if($stmt->execute([$id,$fname,$lname])){
                return true;
            }else{
                $this->msg = 'User information change failed.';
                return false;
            }
        }else{
            $this->msg = 'Provide a valid data.';
            return false;
        }
    }

What am I doing wrong? I don't get any errors in console at the moment.

if($stmt->execute([$id,$fname,$lname])){ looks like those are in the wrong order for that query. — Don't Panic
– Don't Panic, Commented Apr 24, 2017 at 19:40
@ConorReidd Good suggestion in general, but it won't help this case. He's not getting an error. — Barmar
– Barmar, Commented Apr 24, 2017 at 19:43
Right. These problems can be avoided entirely by using named placeholders instead of ?. Then you use an associative array. — Barmar
– Barmar, Commented Apr 24, 2017 at 19:48
If you use question mark placeholders, the order must be exactly the same. If you use named placeholders as in Barmar's answer, the order doesn't matter. — Don't Panic
– Don't Panic, Commented Apr 24, 2017 at 19:54

Barmar · Accepted Answer · 2017-04-24 19:50:35Z

1

The array you're giving to execute has the values in the wrong order, it should be $fname, $lname, $id to match the order in the query.

But IMHO it's best to use named placeholders instead of ordered placeholders. Then the order doesn't matter.

$stmt = $pdo->prepare('UPDATE members SET fname = :fname, lname = :name WHERE id = :id');
$stmt->execute([':id' => $id, ':fname' => $fname, ':lname' => $lname]);

answered Apr 24, 2017 at 19:50

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damian Doman Over a year ago

thanks for your response! just like i wrote in comments section - can't believe it was that simple. So let me get it right - order in execute must be this same as order these names/variables appear in MySQL query? The placeholders trick also seem to save me alot of trouble in this matter for the future, thanks for it!

Collectives™ on Stack Overflow

MySQL/PHP/jQuery - Issue with query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related