insert into table from another table mysql

[nadiam]

hey guys, so i'm trying to insert data from one table to another.

table 1:
|event_id|event_name|start|end|event_venue|

table 2:
|guest_id|guest_name|event_id|event_name|

$event = mysql_query("INSERT INTO event VALUES('','$name','$dstart $tstart','$dend $tend','$venue')");
$insertg = mysql_query("INSERT INTO guest(event_id, event_name) SELECT event_id, event_name, FROM `event` WHERE `event_name` = `$name` ");

i get :

Column count doesn't match value count at row 1

why? and how to fix?

[AndrisP]

Would not it be more convenient to use a trigger?

[nadiam]

i fixed the

Column count doesn't match value count at row 1

there was something wrong with my first insert query but i still have trouble with my insert into table from another table.

Triggered? Ive never tried that. skimmed through a website, is it something like this?

CREATE TRIGGER after_insert_event AFTER INSERT ON event 
FOR EACH ROW BEGIN
AS INSERT INTO guest SET action = 'insert',
event_id = event_id,
event_name = event_name,

correct me where/if im wrong. thank you

[AndrisP]

Something like this:

CREATE TRIGGER after_insert_event AFTER INSERT ON event 
FOR EACH ROW 
    BEGIN
        INSERT INTO guest (event_id, event_name) VALUES (NEW.event_id, NEW.event_name)
    END;

but your first example not correct "INSERT INTO event ????? VALUES"

[nadiam]

okay i will try the trigger.

for the first, well im use to just table_name VALUES(according to column arrangement). i guess its not the best practice? i will try and get out of that habit.

oh and where do i add the trigger into my code?
this is my current php:

<?php
if(isset($_POST['submit']))
{   
    $name = mysql_real_escape_string($_POST['ename']);
    $dstart = mysql_real_escape_string($_POST['datestart']);
    $dend = mysql_real_escape_string($_POST['dateend']);
    $tstart = mysql_real_escape_string($_POST['timestart']);
    $tend = mysql_real_escape_string($_POST['timeend']);
    $venue = mysql_real_escape_string($_POST['evenue']);

    if(preg_match('/^[a-zA-Z0-9\s]+$/', $name))
    {
        require "connection.php";

        $event = mysql_query("INSERT INTO event VALUES('','$name','$dstart $tstart','$dend $tend','$venue','')") or die(mysql_error());
    }
}
?> 

[AndrisP]

trigger you write once and it is stored in the database

[nadiam]

so i put it in the SQL of my database? i use phpmyadmin. i got an error.

screenshot + error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 5

[AndrisP]

DROP TRIGGER IF EXISTS after_insert_event;
DELIMITER $$
CREATE TRIGGER after_insert_event AFTER INSERT ON event
    FOR EACH ROW
    BEGIN
    INSERT INTO guest (event_id, event_name) VALUES (NEW.event_id, NEW.event_name);
    END; $$

And check your syntax also in this:

$event = mysql_query("INSERT INTO event VALUES('','$name','$dstart $tstart','$dend $tend','$venue','')") or die(mysql_error());

Where is column names?

[nadiam]

i got this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$' at line 5

[Daemon_CC]

query should be like..

INSERT INTO def (catid, title, page, publish) 
SELECT catid, title, 'page','yes' from `abc`

[AndrisP]

Me it works perfectly. Maybe try another delimiter e.g. ||

[Daemon_CC]

or to copy ALL fields from the source table to destination table you can do more simply:
INSERT INTO def
SELECT * from abc

[nadiam]

thanks andrisP but i think i'll stick to insert.. select.. for now. which i successfully did.
$insertg = mysql_query("INSERT INTO guest(event_id) SELECT event_id FROM event WHERE event_name = '$name' ") or die(mysql_error());

[AndrisP]

Ok as You Like

[guruparthi]

This is the query for copy values from one table to another in mysql

 Insert into table2 (column_name1, column_name2) select column_name1,
       column_name2 from table1