Fatal error while trying to save to SQLite db

Question

I'm trying to save data from nodes in a xml doc that's loaded into the app with SimpleXML. Below is my code, but something is wrong with the SQL statement because I get a fatal error.

I can't get why this is thrown because id (as referred in the error message below) is used as a value to be put in, not as a column.

So, what am I doing wrong here? Thanks!

php:

    $db;

    $theProducers = simplexml_load_file('sources/producers.xml');

    foreach ($theProducers->producer as $producer) {
        $attr = $producer->attributes();
        $producers[$i]['id'] = (int)$attr[0];
        $producers[$i]['name'] = (string)$producer->name;
        $producers[$i]['address'] = (string)$producer->address;
        $producers[$i]['zipcode'] = (string)$producer->zipcode;
        $producers[$i]['town'] = (string)$producer->town;
        $producers[$i]['url'] = (string)$producer->url;
        $producers[$i]['imgurl'] = (string)$producer->imgurl;

        $i += 1;
    }

    try {
            $db = new PDO('sqlite:ProducersDB.sqlite');
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
    catch(PDOException $e) {
        die("Something went wrong: " . $e->getMessage());
    }

    for($i = 0; $i < count($producers); $i++) {

        $sql = "INSERT INTO producers (producerid, name, address, zipcode, town, url, imgurl)
                VALUES($producers[$i]['id'], $producers[$i]['name'], $producers[$i]['address'], $producers[$i]['zipcode'], $producers[$i]['town'], $producers[$i]['url'], $producers[$i]['imgurl']); ";

        if(!$db->query($sql)) {
            die("Couln't execute query!");
        }
    }

error message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]:
General error: 1 no such column: 'id'' in
/Applications/XAMPP/xamppfiles/htdocs/app/index.php:55 Stack trace: #0
/Applications/XAMPP/xamppfiles/htdocs/app/Labb2/index.php(55):
PDO->query('INSERT INTO pro...') #1 {main} thrown in
/Applications/XAMPP/xamppfiles/htdocs/app/index.php on line 55

db structure:

Table: Producers

id INTEGER PRIMARY KEY,
producerid INT,
name TEXT,
address TEXT,
zipcode INT,
town TEXT,
url TEXT,
imgurl TEXT

CL. · Accepted Answer · 2012-12-02 21:42:11Z

1

According to the string parsing documentation, multidimensional arrays require that you use the complex syntax; also, SQL strings must be quoted:

$sql = "INSERT INTO producers (...)
        VALUES({$producers[$i]['id']}, '{$producers[$i]['name']}', ..."

To avoid string formatting problems, it is recommended to use parameters.

answered Dec 2, 2012 at 21:42

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Comments

koopajah · Accepted Answer · 2012-12-02 19:23:02Z

1

You query to INSERT element in your table is not properly built. Your values are in a PHP array which is not interpreted properly between your double quotes, it should be something like this:

$sql = "INSERT INTO producers(producerid, name)
        VALUES ('" . $producers[$i]['id'] . "', '" . $producers[$i]['name'] . "')";

You should also look the method prepare from PDO to properly set dynamic values in your query instead of building it by yourself.

EDIT: according to the PHP documentation on Strings I'm wrong and you can interpret an array between double quotes, sorry for the confusion.

edited Dec 2, 2012 at 19:23

answered Dec 2, 2012 at 19:15

koopajah

26k9 gold badges87 silver badges113 bronze badges

Collectives™ on Stack Overflow

Fatal error while trying to save to SQLite db

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related