PDO Syntax error or access violation using implode

Question

I have a list of tags which I want to store in my database. The table called "tags" has 2 columns: id (auto-increment) and tag (varchar). The goal is to append every tag of my array to a unique row of the "tag" column. The error I get is this:

Warning: PDOStatement::execute(): 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 '0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25) VALUES ('al' at line 1 in C:\xampp\htdocs\cmanager\draft.php on line 36 New tags created successfully Notice: Use of undefined constant tags - assumed 'tags' in C:\xampp\htdocs\cmanager\draft.php on line 34

And the code is the following:

<?php
include('../config.php');

$tagList = array('black', 'purple', 'alpha', 'pink', 'normal', 'green', 'shininess', 'specular', 'blue', 'orange',
    'nylon', 'stretched', 'brown', 'yellow', 'green', 'suede', 'wood', 'linen', 'red', 'white', 'no', 'tile', 'gray',
    'velvet', 'mauve', 'white');

sort($tagList);
print_r($tagList);

    try {
        $dbh = new PDO("mysql:host=$hostname;dbname=store", $username, $password);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        echo "Connected to database \r\n";


        foreach ($tagList as $k => $v) {
            $prep[':' . $k] = $v;

            $bind = ':' . implode(',:', array_keys($tagList));
//            $sql = 'INSERT INTO tags (tag) ' .
//                ' VALUES (:bind)';
            $sql = 'INSERT INTO ' . tags . '(' . implode(',', array_keys($tagList)) . ') ' . 'VALUES (' . $bind . ')';
            $stmt = $dbh->prepare($sql);
            $stmt->execute(array_combine(explode(',', $bind), array_values($tagList)));
            echo "New tags created successfully \r\n";

        }
    } catch (PDOException $e) {
        echo $e->getMessage();
    }

I don't understand what is the mistake. Could someone help me with this? Thanks

I'm looping over an array. Arrays have keys (strings since I have an associative array) and values that 'belong' to those keys. My $k is the key, the $v is the value, and I'm looping trough each separate pair with a foreach. — joasa
– joasa, Commented Mar 16, 2016 at 13:47
echo $sql, what does the query look like? It seems like it looks like INSERT INTO .. (0, 1, 2, ...), which is obviously nonsense, no? — deceze
– deceze ♦, Commented Mar 16, 2016 at 13:51
You don't have an associative array for starter. $tagList is a plain array with numeric indexes — Your Common Sense
– Your Common Sense, Commented Mar 16, 2016 at 13:51

fusion3k · Accepted Answer · 2016-03-16 14:06:53Z

First of all, you have a syntax error:

'INSERT INTO ' . tags . '('

must be:

'INSERT INTO tags ('

Then, you have a strange foreach, in which you perform theoretically 26 queries, one per each $tagList value. Theoretically, because loop crash at first.

In the first loop, this is the query sent to your database:

INSERT INTO tags(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25) VALUES (:0,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25)

If you want insert multiple values at once in tag field, the correct syntax is:

INSERT INTO tags ( tag ) VALUES ( 'tag1' ), ( 'tag2' ), ...

Last but not least, you have to perform the foreach() loop to construct your query and then perform the query, outside the loop:

$query = [];
foreach( ... )
{
    // Your stuff to set the query here
    $query[] = "( :varNameToBind )";
}

$query = "INSERT INTO tags (tag) VALUES " . implode( ", ", $query );

echo $query;   //  Test your query
die();         //  then remove these lines

$stmt = $dbh->prepare( $query );
$stmt->execute( ... );

Your bind array construction seems fine for me.

Collectives™ on Stack Overflow

PDO Syntax error or access violation using implode

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related