Problem with INSERTing to MySQL table, by PHP PDO

Question

I creating simple application, first time used PDO, and stuck on problem...
I searched web for something analogical, but didn't find it.
I cannot insert data into tables. Auto increment value is changing, so some data are sent to table.
If I echo query with values from variable, copy and paste it to phpmyadmin, row is inserted properly.
Log contain same query, MySQL and PHP don't return any errors...

Now what I have:

Class article:

class article{
    private function findMaxSortId($pdo_object){
        $query_sort="
                    SELECT MAX(sort_id) as max_id
                    FROM articles";
        try{
            $pdo_object->prepare($query_sort);
            $max_row=$pdo_object->query($query_sort)->fetch(PDO::FETCH_ASSOC);
        }
        catch(PDOException $eSortId){
            $return['error_code_sort']=$eSortId->errorCode();
            $return['error_info_sort']=$eSortId->errorInfo();
        }
        $return['max_id']=$max_row['max_id'];
        return $return;
    }    
    public function createArticle($pdo_object, $title, $body, $visible, $incerase_sort_id=2){
        $return=$this->findMaxSortId($pdo_object);
        $sort_id=$return['max_id']+$incerase_sort_id;

        $pdo_object->exec("
            INSERT INTO `articles` (sort_id, visible, title, body)
            VALUES ('".$sort_id."', '".$visible."', '".$title."', '".$body."')
        ");
        $return['id']=$pdo_object->lastInsertId();
        return $return;
    }
}

index.php:

if(isset($_POST['save'])&&$_POST['save']=='add'){
    if (isset($_POST['visible'])) {
        $visible=1;
    }
    else{
        $visible=0;
    }
    require_once 'class/class.article.php';
    $art = new article();
    $art->createArticle($pdo, $_POST['title'], $_POST['body'], $visible);
    unset($art);
}

articles table in db:

CREATE TABLE `articles` (
    `article_id` int(11) NOT NULL AUTO_INCREMENT,
    `sort_id` int(11) DEFAULT NULL,
    `lastmod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `visible` tinyint(1) DEFAULT NULL,
    `title` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
    `body` text COLLATE utf8_unicode_ci,
    PRIMARY KEY (`article_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What I am doing wrong?
Please help.

Looks, like problem is more complicated.
I cannot do any operation that write something in DB with PDO.
I can query DB and "read" values, but if i use INSERT or UPDATE, nothing is happening.
I use WampServer 2.1 on 64bit OS.
I can do it without PDO, with mysql query, fetch, etc. ...

Arkh · Accepted Answer · 2011-09-15 09:59:57Z

3

You should use prepared statements instead of those crazy query creation code. Something like:

$stmt = $pdo_object->prepare('INSERT INTO `articles` (sort_id, visible, title, body)
            VALUES (:id, :visible, :title, :body)');
$stmt->execute(array(':id' => $sort_id,
                     ':visible' => $visible,
                     ':title' => $title,
                     ':body' => $body));

It will prevent nasty SQL injection which you don't want.

To see SQL errors you may have, a call to $pdo_object->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); so it will throw exceptions in case of error. Don't use this like that in a production environment as it gives your database login and password in the exception message.

I'd also advise you to use transactions around your code so you don't have multiple articles using the same sort_id if created at the same moment.

public function createArticle($pdo_object, $title, $body, $visible, $incerase_sort_id=2){
  $pdo_object->beginTransaction();
  try{
    $return=$this->findMaxSortId($pdo_object);
    $sort_id=$return['max_id']+$incerase_sort_id;

    $stmt = $pdo_object->prepare('INSERT INTO `articles` (sort_id, visible, title, body)
                VALUES (:id, :visible, :title, :body)');
    $stmt->execute(array(':id' => $sort_id,
                         ':visible' => $visible,
                         ':title' => $title,
                         ':body' => $body));
    $return['id']=$pdo_object->lastInsertId();
  }catch(Exception $e){
    $pdo_object->rollBack();
    return null;
  }
  $pdo_object->commit();
  return $return;
}

edited Sep 15, 2011 at 9:59

answered Sep 15, 2011 at 9:51

Arkh

8,44043 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tomiozo Over a year ago

I have already tried prepared statements, result is same. But i tried it with question marks. I will check it and let You know. I will check also ERRMODE. Thanks for advice with transactions i will use it in other project, but as i wrote this project is simple, there will be 10-15 articles, so it will be sorted once and for all, i think ;) This will be website where i put other website XML affiliate program specification (XML, implementation, etc). I will modify it probably once for 6 months.

sanmai · Accepted Answer · 2011-09-15 09:51:01Z

1

This is wrong:

    $pdo_object->exec("
        INSERT INTO `articles` (sort_id, visible, title, body)
        VALUES (`".$sort_id."`, `".$visible."`, `".$title."`, `".$body."`)
    ");

You should replace backticks with single or double quotes:

    $pdo_object->exec("
        INSERT INTO `articles` (sort_id, visible, title, body)
        VALUES ('".$sort_id."', '".$visible."', '".$title."', '".$body."')
    ");

Backticks are to be used for table and field names.

answered Sep 15, 2011 at 9:51

sanmai

31.2k12 gold badges72 silver badges76 bronze badges

2 Comments

tomiozo Over a year ago

@Jon Stirling & sanmai you are right, but this is result of my combinations ;) quotes was first option and in this form i should post code, but i changed it many times and just forgot to change it back

tomiozo Over a year ago

I just changed those backticks to quotes. Just to not mislead others.

Jonnix · Accepted Answer · 2011-09-15 09:44:57Z

0

Try replacing the backticks from the INSERT INTO with quotes when dealing with values.

answered Sep 15, 2011 at 9:44

Jonnix

4,1671 gold badge33 silver badges31 bronze badges

Collectives™ on Stack Overflow

Problem with INSERTing to MySQL table, by PHP PDO

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related