samedi 28 octobre 2017

Trying to UPDATE a database through HTML forms throws Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064

I am working on a project for school. I am new to PHP and databases, but managed to make a quite a good system for adding posts dynamically to my website. All is done with OOP aproach using the MVC framework and handling database is done through PDO. This is the add function, that works wonders and is called once user submits the HTML form:

public function add(){
    // Sanitize POST
    $post = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

    if($post['submit']){
        if($post['title'] == '' || $post['content'] == '' || $post['link'] == '' || $post['image_name'] == ''){
            Messages::setMsg('Please Fill In All Fields', 'error');
            return;
        }
        // Insert into MySQL
        $this->query('INSERT INTO posts (country, language, title, content, link,  image_name) VALUES(:country, :language, :title, 
                    :content, :link, :image_name)');
        $this->bind(':country', $post['country']);
        $this->bind(':language', $post['language']);
        $this->bind(':title', $post['title']);
        $this->bind(':content', $post['content']);
        $this->bind(':link', $post['link']);
        $this->bind(':image_name', $post['image_name']);
        $this->execute();
        // Verify
        if($this->lastInsertId()){
            // Redirect
            header('Location: '.ROOT_URL.'shares/add');
        }
    }
    return;
}

The problem began, when I tried to do an editing system, for the posts in the database. When user is in admin mode, he can click a edit button, that redirects him to a URL with a queery, where the ID is written like this:

http://localhost/shares/edit/?id=2

Once user is on this URL, he has the option to input new values into the form, to owerwrite the current database row on that ID. (in the future, the form will be autofilled once you get redirected with the stuff on the ID)

This is the function edit:

public function edit(){
    // Sanitize POST
    $post = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

    if($post['submit']){
        if($post['title'] == '' || $post['content'] == '' || $post['link'] == '' || $post['image_name'] == ''){
            Messages::setMsg('Please Fill In All Fields', 'error');
            return;
        }
        // Get ID from URL
        $url = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
        $parts = parse_url($url);
        parse_str($parts['query'], $query);
        $id = $query['id'];
        // Insert into MySQL
        $this->query('UPDATE `posts` (`id`, `country`, `language`, `title`, `content`, `link`, `image_name`) VALUES(:id, :country, :language, :title, 
                    :content, :link, :image_name) WHERE id = :id');
        $this->bind(':id', $id);
        $this->bind(':country', $post['country']);
        $this->bind(':language', $post['language']);
        $this->bind(':title', $post['title']);
        $this->bind(':content', $post['content']);
        $this->bind(':link', $post['link']);
        $this->bind(':image_name', $post['image_name']);
        $this->execute();
        // Verify
        if($this->lastInsertId()){
            // Redirect
            header('Location: '.ROOT_URL);
        }
    }
    return;
}   

and it throws this error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(`id`, `country`, `language`, `title`, `content`, `link`, `image_name`) VALUES('' at line 1 in C:\xampp\htdocs\classes\Model.php:43 Stack trace: #0 C:\xampp\htdocs\classes\Model.php(43): PDOStatement->execute() #1 C:\xampp\htdocs\models\share.php(27): Model->execute() #2 C:\xampp\htdocs\controllers\shares.php(18): ShareModel->edit() #3 C:\xampp\htdocs\classes\Controller.php(12): Shares->edit() #4 C:\xampp\htdocs\index.php(29): Controller->executeAction() #5 {main} thrown in C:\xampp\htdocs\classes\Model.php on line 43

I went through a lot of questions, that asked a similar question, but none helped me with debugging mine. Any help with debugging this function will get you my eternal thanks. :)




Aucun commentaire:

Enregistrer un commentaire