multiple SQL statements

[Conversation Exchange Help]

Hi,

I am new to F3. Reading the DB notes about transactions I came across this statement:

$db->exec( array( 'DELETE FROM diet WHERE food="cola"', 'INSERT INTO diet (food) VALUES ("carrot")', 'SELECT * FROM diet' ) );

I was wondering if instead I could write the same statement in one single string as:

$db->exec(' DELETE FROM diet WHERE food="cola"; INSERT INTO diet (food) VALUES ("carrot"); SELECT * FROM diet;' );

This would make it easier to copy multiple statements from a SQL file or to paste and test them on Toad.

Thanks!

Fed

[xfra35]

Have you tried it?

If it doesn't work, you can easily workaround it with:

$sql='    

        DELETE FROM diet WHERE food="cola";
        INSERT INTO diet (food) VALUES ("carrot");

        SELECT * FROM diet;';
$db->exec(explode(';',rtrim($sql,'; ')));

[Conversation Exchange Help]

Thanks man,

I will try even though it does not seem safe. Imagine a scenario like:

INSERT INTO diet (food) VALUES("carrot; salad; broccoli;");

The explode would chop that insert in in 3 pieces!

[xfra35]

That's right.

Anyway, you could refine the splitting like this:

$sql='

    INSERT INTO diet (food) VALUES("carrot; salad; broccoli;");

    DELETE FROM diet WHERE food="cola";
    INSERT INTO diet (food) VALUES ("carrot");
    SELECT * FROM diet;'

;
$db->exec(preg_split('/;(?=\v|$)/',$sql,NULL,PREG_SPLIT_NO_EMPTY));

(provided each statement is followed by a line feed)