Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]. I don't understand what's wrong

Question

the backend part:

$producttitle = $_POST['product-title'];
$price = $_POST['price'];
$category = $_POST['Category'];
$file = $_FILE['file_upload'];
$description = $_POST['description'];

$adq = "INSERT INTO Advertenties (Titel, Prijs, Categorie, Image, Beschrijving) VALUES (:product-title, :price, :category, :file_upload, :description);";

$query = $GLOBALS['$odb']->prepare($adq);


$results = $query->execute(array(       
    ":product-title" => $producttitle,
    ":price" => $price,
    ":category" => $category,
    ":file_upload" => $file,
    ":description" => $description
    ));

Front end:

 <?php
require_once('../classes/layout_shared.php');

?>

<html lang="NL">
    <head>
        <meta charset="UTF-8">
        <link rel="stylesheet" href="../css/bootstrap.css">
    </head>
    <body>
        <div class="container">
            <div class="col-md-9">
                <form method="POST" action="../classes/upload.php" class="ad-form" enctype="multipart/form-data">
                    <lable>Titel</lable>
                        <input class="form-control" type="text" name="product-title"/>
                   <lable>Bedrag/Bieden vanaf:</lable>
                        <input class="form-control" type="text" name="price"/><br>
                        <lable>Categorie</lable>
                        <input class="form-control" type="" name="category"/><br>
                   <lable>Image</lable>
                        <input class="form-control" type="file" name="file_upload"/> <br>
                   <lable>Beschrijving:</lable>
                       <textarea class="form-control" placeholder="Voeg een beschrijving van het product toe" name="description"></textarea></br>
                   <input type="submit" name="toevoegen" value="Toevoegen"/>
                </form> 
            </div>
        </div>
    </body>
</html>

So, this is my code above. It's for some reason giving me an error but I just can't undertand why. I've scoured the internet, and all I can find is something doesn't match something. I've gone through this so many times, its starting to annoy me now.

Any ideas?

shouldn't matter. `;` is valid at the end of a single query. it's not necessary, but it's not invalid. — Marc B, Jun 07 '16 at 20:38

score 2 · Answer 1 · answered Jun 07 '16 at 20:25

2

try replacing :product-title with :product_title

answered Jun 07 '16 at 20:25

Alex

566
3
15

this fixed it, just added this problem: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'Categorie' cannot be null' in – ssjturtle Jun 07 '16 at 20:33
$category = $_POST['Category']; is the problem, replace "Category" with "category" – Alex Jun 07 '16 at 20:35
@JayBlanchard looks like the hyphen isn't supported as a placeholder, http://stackoverflow.com/a/5810058/4333555. – chris85 Jun 07 '16 at 20:37
@JayBlanchard: `:product-title` would be parsed as `:product MINUS title`, and most likely OP doesn't have a title field in the db, producing a parse error. – Marc B Jun 07 '16 at 20:39
@MarcB I've changed it to :product_title now, but still getting the same issues – ssjturtle Jun 07 '16 at 20:42
$_FILE is an array of information about an upload. you can **NOT** bind that array entry to your query. that's NOT how you insert data about an upload into a db. – Marc B Jun 07 '16 at 20:44
Sorry, I meant my question as the standard "why should the OP try this" response. I should've posted that. – Jay Blanchard Jun 07 '16 at 20:45
@MarcB it worked on an older project i did however, all i want is the name of the uploaded file and add that, i don't know why it wont work now though – ssjturtle Jun 07 '16 at 20:45
Why should the OP "try" this? A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Jun 07 '16 at 20:47
@ssjturtle then use the name of the file input like ":file_upload" => $file['name'], – Alex Jun 07 '16 at 20:47
It would NEVER have worked in another project. you cannot bind an array to a placeholder. – Marc B Jun 07 '16 at 20:48

score 1 · Answer 2 · answered Jun 07 '16 at 20:35

When in doubt, break your query into as many lines as possible so that you can sniper the issue rather than having MySQL always report an error on line 1:

Turn

$adq = "INSERT INTO Advertenties (Titel, Prijs, Categorie, Image, Beschrijving) VALUES (:product-title, :price, :category, :file_upload, :description);";

Into

$adq = "INSERT INTO Advertenties
        (Titel,
        Prijs,
        Categorie,
        Image,
        Beschrijving)
        VALUES
        (:product-title,
        :price,
        :category,
        :file_upload,
        :description);";

Also, is :product-title a valid placeholder?

score 1 · Answer 3 · answered Jun 07 '16 at 20:46

1

Beyond the sql placeholder name problem, you have multiple OTHER bugs:

Field names are case sensitive:

$category = $_POST['Category'];
                    ^---
<input class="form-control" type="" name="category"/><br>
                                          ^----

$_FILES is an array of arrays:

$file = $_FILE['file_upload'];
":file_upload" => $file,

You can NOT bind array to a query placeholder. No idea what you're trying to do here - insert the actual file contents, or just the name of the file? Either way, it should be something like

":file_upload" => $file['tmp_name'],

to bind only one particular value out of the array.

answered Jun 07 '16 at 20:46

Marc B

356,200
43
426
500

Alright I've done that but I'm still getting the same thing. It's super odd seeing as it does upload, but the problem arises at `$results = $query->execute(array( ":product_title" => $producttitle, ":price" => $price, ":category" => $category, ":file_upload" => $file, ":description" => $description ));` – ssjturtle Jun 07 '16 at 21:00
You have one more issue not mentioned. `$GLOBALS['$odb']` single quotes don't process in PHP, so currently `$odb` is literal. – chris85 Jun 07 '16 at 22:50

score 0 · Answer 4 · answered Jun 07 '16 at 20:31

instead of having the array as a parameter for the execute function you should bind all the values separately using bindValue. If you passed them as a parameter every value would be treated as a string, with bindValue you maintain the type of the variable. The code would look something like this:

$adq = "INSERT INTO Advertenties (Titel, Prijs, Categorie, Image, Beschrijving) VALUES (:product-title, :price, :category, :file_upload, :description);";
$query = $GLOBALS['$odb']->prepare($adq);
$query->bindValue(":product-title", $producttitle);
$query->bindValue(":price", $price);
$query->bindValue(":category", $category);
$query->bindValue(":file_upload", $file);
$query->bindValue(":description", $description);
$result = $query->execute();

I hope that this helps.

Succes gewenst

score 0 · Answer 5 · answered Jun 07 '16 at 20:37

0

looks like you have $category = $_POST['Category']; when you are sending 'category'. This is causing an undefined index. lowercase the c. Along with Alex answer and you should be good

answered Jun 07 '16 at 20:37

Andrew Nolan

1,987
2
20
23

Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]. I don't understand what's wrong

5 Answers5