Insert using prepared statements via MySqli fails to insert all data properly

Question

I'm using MySqli to insert a piece of page-content into the database in the table content with the following columns: id, name, content, parent, order where content is of type TEXT. The problem however is, that when executing the insert statement it does insert a new entry into the table, but does not insert the content and the parent value, while properly inserting the name and order value. I checked the return values of all the prepare functions and all returned true or gave no error so I'm extremely confused as to what is going on.

The class properties ($this->Content and $this->Parent) are filled and contain the proper data. Also, the update/edit part of the function works just fine (seeing as the prepared functionality of both these parts is exactly the same, this is an odd error).

This is the code that performs the queries. the $type variable can take two values: edit or new and when its new it doesn't function properly.

public function Save($type)
{
    if($type == "edit")
    {
        $query = "UPDATE `content` SET `name` = ?, `content` = ?, `parent` = ? WHERE `id` = ?";
        $stmt = $this->sql->prepare($query);
        $stmt->bind_param("ssii", $this->Name, $this->Content, $this->Parent, $this->ID);
    }
    else if ($type == "new")
    {
        $query = "INSERT INTO `content`(`name`, `parent`, `content`, `order`) VALUES(?, ?, ?, 999)";
        $stmt = $this->sql->prepare($query);
        $stmt->bind_param("ssi", $this->Name, $this->Content, $this->Parent);
    }       
    if(!$stmt->Execute())
        echo "Failed saving content-object with ID $this->ID" . mysqli_error($this->sql);
    $stmt->close();
}

I have no idea why the edit part works perfectly fine, but the new part does not. The query does insert a new row with the correct name and order values, so I guess it has something to do with the bind_param function. The row in the database always results with the value 0 for both content and parent.

First and foremost: http://stackoverflow.com/a/22662582/285587 — Your Common Sense, Apr 12 '14 at 11:45
@YourCommonSense: Added it before creating the MysqlI object. Did not show any errors though after an insert. — Joey Dewd, Apr 12 '14 at 11:46
@JoeyDewd Your binding and value order is wrong for inserting new content. See my answer below — Hüseyin BABAL, Apr 12 '14 at 11:48
@YourCommonSense: Ahh okay, that is a nice thing to know. thanks — Joey Dewd, Apr 12 '14 at 11:50

score 1 · Accepted Answer · answered Apr 12 '14 at 11:45

1

You have binded in wrong way;

Your value types must be sis, and also, value order is wrong. See update below;

...
else if ($type == "new")
{
    $query = "INSERT INTO `content`(`name`, `parent`, `content`, `order`) VALUES(?, ?, ?, 999)";
    $stmt = $this->sql->prepare($query);
    $stmt->bind_param("sis", $this->Name, $this->Parent, $this->Content);
}
....

answered Apr 12 '14 at 11:45

Hüseyin BABAL

15,400
4
51
73

You're right, it seems I messed up my order and stupid that I did not see that. I've been staring at this piece of code for hours. I edited the code and it works perfect now. Thank you – Joey Dewd Apr 12 '14 at 11:52
Yeah tried that, but I can only accept it after a given amount of time after creating the question ;) – Joey Dewd Apr 12 '14 at 11:57

r3wt · Answer 2 · 2014-04-12T12:04:16.007

i'm not sure what the problem is, i don't like that if/else logic though. i prefer to use a switch myself.

public function Save($type)
{
    switch($type) {

        case 'edit': 

        $query = "UPDATE `content` SET `name` = ?, `content` = ?, `parent` = ? WHERE `id` = ?";
        $stmt = $this->sql->prepare($query);
        $stmt->bind_param("ssii", $this->Name, $this->Content, $this->Parent, $this->ID);

        if(!$stmt->Execute()) {
            echo "Failed saving content-object with ID $this->ID" . mysqli_error($this->sql);
                    $stmt->close();
        }else{
                    $stmt->close();
            }
        break;

        case 'new':

        $query = "INSERT INTO `content`(`name`, `parent`, `content`, `order`) VALUES(?, ?, ?, 999)";
        $stmt = $this->sql->prepare($query);
        $stmt->bind_param("ssi", $this->Name, $this->Content, $this->Parent);

        if(!$stmt->Execute()) {
            echo "Failed saving content-object with ID $this->ID" . mysqli_error($this->sql);
                    $stmt->close();
        }else{
                     $stmt->close();
            }
        break;

        default: die;

    }
}

Another thing i noticed: you connect to your database and execute your queries in an extremely unorthodox way. just create a db connection in a config file you require on all pages and cast it globally, then access it through the global array inside of your function, like global $database. then you can perform your stuff more easily like $stmt = $database->prepare("INSERT INTO ....");

another thing, don't use double quotes in php unless you are going to leave a variable in them, like this echo "welcome to my website, $username";

when you use double quotes in php, php is searching for a variable inside them, so use single quotes as much as possible to increase the performance of your script and save server resources.

@r3wt: Thanks for the tips (double quotes tip is a nice one), yeah the database is actually a global object that's passed to the class object but that second step isn't really necessery so you're right. Also, the logic is correct, there's nothing wrong with it. — Joey Dewd, Apr 12 '14 at 11:58
That's fantastic collection of superstitions. @JoeyDewd NONE of these tips are true — Your Common Sense, Apr 12 '14 at 11:59
@YourCommonSense: oh, sorry :p, I'm not an expert at PHP so tips quickly seem legit to me. Thanks for pointing it out — Joey Dewd, Apr 12 '14 at 12:01

Insert using prepared statements via MySqli fails to insert all data properly

2 Answers2