SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens error

Question

I got this error on 2 of my pages and I can't understand why. I have rewritten the code and triple checked it, but I can't find what is wrong.

The first one is:

 public function academics ($id,$problem_solving ,  $math_understanding,      $cs_understanding ,   $math_useful ,  $cs_useful, $math_ava,  $cs_ava,    $assigment_start,   $assigment_submit,  $travel_time,   $stress,$assigment_when,    $full_part,     $pair_programming,  $attending_class,   $attending_labs,$attending_tutorials,   $extra_reading, $p_progamming,  $q_cs,  $q_maths,   $procrastinating_assigments,    $procrastinating_studying){
    try{
        $stmt = $this->db->prepare("UPDATE student_data SET problem_solving=:problemS, math_understanding=:math_U, cs_understanding=: cs_u, math_useful =:m, cs_useful=:cs_u, math_ava=:ma, cs_ava=:ca, assigment_start=:ass_st, assigment_submit=:assigment_submit, travel_time =:travel_time, stress=: stress, assigment_when =:assigment_when, full_part =:full_part, pair_programming=: pair_programming,   attending_class=: attending_class, attending_labs=: attending_labs, attending_tutorials =:attending_tutorials, extra_reading =:extra_reading, p_progamming=: p_progamming,  q_cs=: q_cs,    q_maths=: q_maths, procrastinating_assigments=:procrastinating_assigments, procrastinating_studying=:procrastinating_studying WHERE user_id=:uid");
        $stmt->bindparam(":uid", $id);
        $stmt->bindparam(":problemS",$problem_solving );
        $stmt->bindparam(":math_U",$math_understanding);
        $stmt->bindparam(":cs_u",$cs_understanding );
        $stmt->bindparam(":m",$math_useful );
        $stmt->bindparam(":cs_u",$cs_useful);
        $stmt->bindparam(":ma",$math_ava);
        $stmt->bindparam(":ca",$cs_ava);
        $stmt->bindparam(":ass_st",$assigment_start);
        $stmt->bindparam(":assigment_submit",$assigment_submit);
        $stmt->bindparam(":travel_time",$travel_time);
        $stmt->bindparam(":stress",$stress);
        $stmt->bindparam(":assigment_when",$assigment_when);
        $stmt->bindparam(":full_part",$full_part    );
        $stmt->bindparam(":pair_programming",$pair_programming);
        $stmt->bindparam(":attending_class",$attending_class);
        $stmt->bindparam(":attending_labs",$attending_labs);
        $stmt->bindparam(":attending_tutorials",$attending_tutorials);
        $stmt->bindparam(":extra_reading",$extra_reading);
        $stmt->bindparam(":p_progamming",$p_progamming);
        $stmt->bindparam(":q_cs",$q_cs);
        $stmt->bindparam(":q_maths",$q_maths);
        $stmt->bindparam(":procrastinating_assigments",$procrastinating_assigments);
        $stmt->bindparam(":procrastinating_studying",$procrastinating_studying);
        $stmt->execute(); 
    } catch(PDOException $e)
    {
        echo $e->getMessage();
    }  
}

The second one is:

public function comps ($id,$long,$often_comp,$gaming,$research,$doc,$music,$vid,$fix,$social){
    try{
        $stmt = $this->db->prepare("UPDATE student_data SET long=:long, often_comp=:often , gaming=:gaming, research=:research, doc=:doc, music=:music, vid=:vid, fix=:fix, social=:social  WHERE user_id=:uid");
        $stmt->bindparam(":uid",$id);
        $stmt->bindparam(":long",$long);
        $stmt->bindparam(":often",$often_comp);
        $stmt->bindparam(":gaming",$gaming);
        $stmt->bindparam(":research",$research);
        $stmt->bindparam(":doc",$doc);
        $stmt->bindparam(":music",$music);
        $stmt->bindparam(":vid",$vid);
        $stmt->bindparam(":fix",$fix);
        $stmt->bindparam(":social",$social);
        $stmt->execute(); 
    } catch(PDOException $e)
    {
        echo $e->getMessage();
    }  
}

possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — Funk Forty Niner, Jun 02 '16 at 01:25
^ that being `long` which is a MySQL reserved word and may be closed because of it. If someone pops in an answer for it, I'll close it with that possible dupe. — Funk Forty Niner, Jun 02 '16 at 01:25
then there's `: q_cs` also, another space. and `: stress` - Edit: @chris85 this just gets better, *don't it?* — Funk Forty Niner, Jun 02 '16 at 01:29
THANK YOU!! i had no idea spaces count for php, thought it ignored them like for C/C++, works now. thank you all again!! — bandos, Jun 02 '16 at 01:36
@bandos you're welcome, but that isn't the only thing wrong with your code. The MySQL reserved word being `LONG`. — Funk Forty Niner, Jun 02 '16 at 01:37

Funk Forty Niner · Accepted Answer · 2016-06-02T01:47:55.887

3

Ok, this is too long for a comment (no pun intended on that "long" btw).

Seeing that IF those are not typos (and I hope they're not), you have quite a few binds that have spaces after the colon.

You need to delete the spaces for them and also wrap the MySQL reserved word in ticks being LONG, or rename it to something other than a reserved word.

I.e.

SET `long`=:long, ...

Reference:

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

Footnotes:

TBH: I didn't count all your binds, so if I missed something, you'll need to go over them all and make sure they all match.

Edit:

"i had no idea spaces count for php, thought it ignored them like for C/C++, works now. thank you all again!! – bandos"

This isn't C/C++, it's PHP and spaces do count in that language.

edited Jun 02 '16 at 01:47

answered Jun 02 '16 at 01:32

Funk Forty Niner

74,450
15
68
141

i got it, thanks. i had thought of long being a keyword in php but it wasnt, didnt think of it being a part of the database section. that along with the spaces fixed both. thanks again for the help! – bandos Jun 02 '16 at 01:42
spaces do not count in c/c++, they are ignored at compile time unless it is a hard coded string – bandos Jun 03 '16 at 22:50

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens error

1 Answers1

Linked