Insert data into two tables with one query

CMellor · 12-09-2007, 09:33 PM

Hey,

Can you insert data into two tables using only one query? I know you can SELECT and UPDATE two tables with one query, which is why I assumed you could do it with INSERT, but upon trying and looking at sites provided by Google, I came up with nothing... which is where I come to ask you lot :P

I tried this:

PHP Code:


			
//Insert vote into database

    mysql_query(sprintf("

        INSERT INTO poll_vote, poll SET

          poll_vote.poll_id = '%d',

          poll_vote.option_id = '%d',

          poll_vote.voter_id = '%d',

          poll.who_voted = '%s'",

            $_POST['poll_id'],

            $_POST['poll_choice'],

            $_COOKIE['userID'],

            $serial_who_voted

    )) or die(mysql_error());

But naa.

Any ideas? Cheers!

Village Idiot · 12-09-2007, 09:58 PM

Just use mysqli_query and have two INSERT commands.

Wildhoney · 12-09-2007, 09:58 PM

I don't believe you can do it for an INSERT statement, but you can put them in a TRIGGER which can then be executed using one call to it. If you were looking for an easy solution then I think you're out of luck, I'm afraid.

CMellor · 12-10-2007, 01:53 AM

Ah well, nevermind. Thanks anyway.

I've not read much into MySQLi, but I probably should.

Dr John · 12-10-2007, 10:19 AM

From the manual
"INSERT inserts new rows into an existing table. "
notice the use of the singular table.

Wildhoney · 12-10-2007, 10:46 AM

Yeh, try the multi insert function in MySQLi Just be careful though as it allows anyone to add some pretty nasty queries if they can somehow introduce a semi-colon into your query.