PDO SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Question

I have an idea of what the error means but for the life of me I can't find it no matter how hard I check and rewrite. Been at it for a while, need a fresh pair of eyes to see where I'm going wrong.

The update function

public function student_acadbg_update($acadbgID, $LRN, $gradesix, $gradesixyear, $gsixawards, $gradeten, $gradetenyear, $gtenawards, $orgs, $extraawards)
{
    try
    {
        $query=$this->db->prepare("UPDATE tb_student_acadbg SET LRN = :LRN,
                                                        gradesix = :gradesix,
                                                        gradesixyear = :gradesixyear,
                                                        gsixawards = :gsixawards,
                                                        gradeten = :gradeten,
                                                        gradetenyear = :gradetenyear,
                                                        gtenawards = :gtenawards,
                                                        orgs = :orgs,
                                                        extraawards = :extrawards
                                                        WHERE acadbgID = :acadbgID");
        $query->bindParam(":LRN", $LRN, PDO::PARAM_STR);
        $query->bindParam(":gradesix", $gradesix, PDO::PARAM_STR);
        $query->bindParam(":gradesixyear", $gradesixyear, PDO::PARAM_STR);
        $query->bindParam(":gsixawards", $gsixawards, PDO::PARAM_STR);
        $query->bindParam(":gradeten", $gradeten, PDO::PARAM_STR);
        $query->bindParam(":gradetenyear", $gradetenyear, PDO::PARAM_STR);
        $query->bindParam(":gtenawards", $gtenawards, PDO::PARAM_STR);
        $query->bindParam(":orgs", $orgs, PDO::PARAM_STR);
        $query->bindParam(":extraawards", $extraawards, PDO::PARAM_STR);
        $query->bindParam(":acadbgID", $acadbgID, PDO::PARAM_INT);
        $query->execute();

        return true;
    }
    catch(PDOException $e)  
    {
    echo $e->getMessage();
    return false;
    }   
}

Where the user input is collected and where the function is called

if(isset($_POST['btn-update']))
{
$LRN = $_GET['LRN'];
$acadbgID = $_POST['acadbgID'];
$gradesix = $_POST['gradesix'];
$gradesixyear = $_POST['gradesixyear'];
$gsixawards = $_POST['gsixawards'];
$gradeten = $_POST['gradeten'];
$gradetenyear = $_POST['gradetenyear'];
$gtenawards = $_POST['gtenawards'];
$orgs = $_POST['orgs'];
$extraawards = $_POST['extraawards'];

if($crud->student_acadbg_update($acadbgID, $LRN, $gradesix, $gradesixyear, $gsixawards, $gradeten, $gradetenyear, $gtenawards, $orgs, $extraawards))
{
    $msg = "<div class='alert alert-info'>
            <strong>WOW!</strong> Record was inserted successfully! <a href='student.php'>Click here to see!</a>
            </div>";
}
else
{
    $msg = "<div class='alert alert-warning'>
            <strong>SORRY!</strong> you fucked up !
            </div>";
     }
}

This is the 2nd part in a two part process the user goes through with registration and LRN is my main reference point to bind various other table entries and details to a specific person. I have no issue getting the data from the database according to the LRN but for some reason this update isn't working whereas my 10+ other CRUD functions are all working fine. I just can't figure it out.

I originally skipped the 'acadbgID' variable since it's just an auto-increment field in my DB that I have yet to really put to use. I've skipped similar auto-increment fields in my other Update functions but out of frustration I decided to try putting it in but still no cigar.

Sorry if it's a bit messy, I just put it together to test core functionality, polishing will be for when everything actually works properly. Just let me know if there are any details I need to add. Thanks in advance.

P.S. If anyone sees any bad practices please do point them out, I am still learning as I go so I'm sure there's room for improvement.

@YvesLeBorg Kindly answer the question so I can give you the proper credit. Close to 6 hours for such a minor mistake. Thank you very much. — Cai, Nov 27 '17 at 13:58
I feel you pain : been there done that got the tee-shirt. I like the way you air out and space out your code, makes locating these much easier :) — YvesLeBorg, Nov 27 '17 at 14:00
I'm not sure your table design but `gradesixyear`, `gradesix`, `gsixawards` and then the repeat for 10 seems like a bad table structure to me. Do you have columns for grades 1 through 10? This is just in relation to `bad practices`, not the question asked. This also isn't a PHP practice, but DB design. — chris85, Nov 27 '17 at 14:02
@YvesLeBorg Good to hear, yeah it makes staring at my code for hours on end a lot easier too. Just wasn't enough in this case :P — Cai, Nov 27 '17 at 14:04
@chris85 Oh those are somewhat "special" for transfer students if they are past Grade 6 or Grade 10, they're milestone Grades where I'm from. Those are the only occurrences in my table design thankfully. I'm still thinking of a better way to handle it but for now it works so it'll do. — Cai, Nov 27 '17 at 14:05
Life Pro Tip: if you cannot make your named parameters consistent, go for the positional ones, there is less possibility to introduce a typo — Your Common Sense, Nov 27 '17 at 14:37
as of the bad practices, `echo $e->getMessage();` is one. [PHP erro reporting](https://phpdelusions.net/articles/error_reporting) — Your Common Sense, Nov 27 '17 at 14:40

score 2 · Accepted Answer · answered Nov 27 '17 at 13:59

2

you have a mismatch between the SQL parameter placeholder and the bind parameter specification.

extrawards in SQL is not same as extraawards in bind

answered Nov 27 '17 at 13:59

YvesLeBorg

9,070
8
35
48

Will accept it in 10 minutes when the system allows me, so embarrassing that even the system says it was resolved too quickly :)). – Cai Nov 27 '17 at 14:02

PDO SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

1 Answers1