选择插入不工作

Question

I am trying to insert a number of ids into a new table. The list of ids is taken from another table.

My Code:

     $stmt = $con->prepare('DROP TABLE tblname;
                            CREATE TABLE tblname (
                                id BIGINT
                                );

                            INSERT INTO tblname (id)
                            SELECT tablename2.colname
                            FROM tablename2
                            WHERE (col1 = "value" AND col2 = "value")');

      $stmt->execute();

I create and dump the table because its part of an update script.

(Is there a better way to do that than dump/create?)

The script needs the current list of ids and I am trying to get create a table with those ids. What happens is, whenever I run the code (using putty) it returns "0" and the table remains empty.

What did I do wrong?

Any general help/advice concerning php/mysql welcome too!

Answer 1

First, make sure PDO is set to throw exceptions if a query fails:

$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Then, perhaps catch the exception (or let the exception halt the application) and see what is wrong.
I do believe your insert query is, erm, off:

INSERT INTO tblname (id)
SELECT tblname2.colname
FROM tablename2
WHERE col1 = "val"

Just seems ambiguous, and messy, even more: it seems unsafe. However, try this -equally messy- query:

INSERT INTO tblname (id) VALUES (
    SELECT colname
    FROM tblname2
    WHERE col1 = "val"
);

Last but not least, make sure you're running PHP version 5.3+, because prior to that version, PDO did not support multiple queries.

My suggestion, though, is not to use multiple queries for the INSERT query. Instead, I'd use a transaction and separate the select and insert query. I'd also add a safety-net to the DROP TABLE and CREATE TABLE queries, too:

try
{
    $con->beginTransaction();//DROP & CREATE:
    if ($con->exec('DROP TABLE IF EXISTS tblname') === false)
    {//query wasn't executed
        $con->rollback();
        exit($con->errInfo());//error
    }
    if ($con->exec('CREATE TABLE IF NOT EXISTS tblname(...);') === false)
    {
        $con->rollback();
        exit($con->errInfo());
    }
    $con->commit();//alter tables.
    $con->beginTransaction();//INSERT TRANSACTION
    $stmt = $con->prepare('INSERT INTO tblname (id) VALUES (:id)');
    $bind = array(
        ':id' => null
    );
    $select = $con->prepare(
        'SELECT colname FROM tblname2 WHERE col1 = :val1 AND col2 = :val2'
    );
    $select->execute(
        array(
            ':val1' => 'value1',
            ':val2' => 'value2'
        )
    );
    while ($row = $select->fetch(PDO::FETCH_ASSOC))
    {
        $bind[':id'] = $row['colname'];
        $stmt->execute($bind);//inserts row
        $stmt->closeCursor();//optional
    }
    $con->commit();//save changes to db
}
catch (PDOException $e)
{
    //rollback transaction
    $con->rollback();
    exit($e->getMessage());//show what went wrong, and exit.
}