CODEWITHSUNDEEP
phpmysql
DrinkJavaCodeJava
DrinkJavaCodeJava

Reputation: 808

Why does the array go in one of my prepared sql statements but not the other

Right I'm trying to make it so I can update a blog post so a specific post can be edited. A similar prepared statement works but the other one does not. Here is the set method that sets the array for the prepared statment:

public function set($key, $value){

        $this->data[$key] = $value;

    }

Now heere is the code that does not work were the post will not update.

public function edit($pID){

            $this->postObject = new Post();

            $post = $this->postObject->getPost($pID);

            $this->set('pID', $post['pID']);
            $this->set('title', $post['title']);
            $this->set('content', $post['content']);
            $this->set('categoryID',$post['categoryID']);
            $this->set('date', $post['date']);
            $this->set('task', 'update');
            //Im totally lost on how the task update thing works on addpost. Just going to call the update post class method. 
            $post = new Post();
            $post->update($this->data);
    }

public function update($data){

    $sql='UPDATE posts SET title=?,content=?,categoryID=?,date=?
    WHERE pID=?';
    $this->db->execute($sql,$data);
    $message = 'Post updated';
    return $message;    

}

Now here is code for a similar statement that works.

public function add(){

        $this->postObject = new Post();

        $data = array('title'=>$_POST['post_title'],'content'=>$_POST['post_content'],
        'categoryID'=>$_POST['categoryID'],'date'=>$_POST['date']);



        $result = $this->postObject->addPost($data);

        $this->set('message', $result);


}

public function addPost($data){

    $sql='INSERT INTO posts (title,content,categoryID,date) VALUES (?,?,?,?)';
    $this->db->execute($sql,$data);
    $message = 'Post added.';
    return $message;

}

I have tried counting the array that comes in and the array has rows. I tried doing mysqli functions to debug, but the fact that this done on a pre fabricated framework makes the use of mysqli_functions a little more complicated. Why exactly isn't the array going into the update statement like it is for the insert statement?

EDIT about the db->execute, that part is not my code. I think this may be the code for it.

function Execute($sql,$inputarr=false) 
    {
        if ($this->fnExecute) {
            $fn = $this->fnExecute;
            $ret = $fn($this,$sql,$inputarr);
            if (isset($ret)) return $ret;
        }
        if ($inputarr) {
            if (!is_array($inputarr)) $inputarr = array($inputarr);

            $element0 = reset($inputarr);
            # is_object check because oci8 descriptors can be passed in
            $array_2d = $this->bulkBind && is_array($element0) && !is_object(reset($element0));

            //remove extra memory copy of input -mikefedyk
            unset($element0);

            if (!is_array($sql) && !$this->_bindInputArray) {
                $sqlarr = explode('?',$sql);
                $nparams = sizeof($sqlarr)-1;
                if (!$array_2d) $inputarr = array($inputarr);

                foreach($inputarr as $arr) {
                    $sql = ''; $i = 0;
                    //Use each() instead of foreach to reduce memory usage -mikefedyk
                    while(list(, $v) = each($arr)) {
                        $sql .= $sqlarr[$i];
                        // from Ron Baldwin <ron.baldwin#sourceprose.com>
                        // Only quote string types  
                        $typ = gettype($v);
                        if ($typ == 'string')
                            //New memory copy of input created here -mikefedyk
                            $sql .= $this->qstr($v);
                        else if ($typ == 'double')
                            $sql .= str_replace(',','.',$v); // locales fix so 1.1 does not get converted to 1,1
                        else if ($typ == 'boolean')
                            $sql .= $v ? $this->true : $this->false;
                        else if ($typ == 'object') {
                            if (method_exists($v, '__toString')) $sql .= $this->qstr($v->__toString());
                            else $sql .= $this->qstr((string) $v);
                        } else if ($v === null)
                            $sql .= 'NULL';
                        else
                            $sql .= $v;
                        $i += 1;

                        if ($i == $nparams) break;
                    } // while
                    if (isset($sqlarr[$i])) {
                        $sql .= $sqlarr[$i];
                        if ($i+1 != sizeof($sqlarr)) $this->outp_throw( "Input Array does not match ?: ".htmlspecialchars($sql),'Execute');
                    } else if ($i != sizeof($sqlarr))   
                        $this->outp_throw( "Input array does not match ?: ".htmlspecialchars($sql),'Execute');

                    $ret = $this->_Execute($sql);
                    if (!$ret) return $ret;
                }   
            } else {
                if ($array_2d) {
                    if (is_string($sql))
                        $stmt = $this->Prepare($sql);
                    else
                        $stmt = $sql;

                    foreach($inputarr as $arr) {
                        $ret = $this->_Execute($stmt,$arr);
                        if (!$ret) return $ret;
                    }
                } else {
                    $ret = $this->_Execute($sql,$inputarr);
                }
            }
        } else {
            $ret = $this->_Execute($sql,false);
        }

        return $ret;
    }

Upvotes: 1

Views: 154

Answers (1)

invisal
invisal

Reputation: 11171

One thing that I think it is more likely to be incorrect is 

$sql='UPDATE posts SET title=?,content=?,categoryID=?,date=?
WHERE pID=?';
$this->db->execute($sql,$data)

You are passing 6 parameters and there are only 5 parameters in your query. Secondly, you pass the data in a different order. You pass the pID data first, while the first ? is title.

Noted that this is only my suspicion.

Here is the suggestion that I've made above

public function edit($pID) 
{
        $this->postObject = new Post();

        $post = $this->postObject->getPost($pID);

        $this->set('title', $post['title']);
        $this->set('content', $post['content']);
        $this->set('categoryID',$post['categoryID']);
        $this->set('date', $post['date']);
        $this->set('pID', $post['pID']);

        $post = new Post();
        $post->update($this->data);
}

Another thing that I just freshly observe is that you are getting the data from ID $pID, then you try to update the same data back to ID $pID. So, it is very obvious that there will be no update.

Upvotes: 2

Related Questions