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?

Recommended Answers

Would not it be more convenient to use a trigger?

Jump to Post

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"

Jump to Post

trigger you write once and it is stored in the database

Jump to Post
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','')") …
Jump to Post

All 14 Replies

Would not it be more convenient to use a trigger?

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

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"

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());
    }
}
?> 

trigger you write once and it is stored in the database

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

screenshot + error:

c141e5c766eefab72a5f14f74e99cd2b

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

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?

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

query should be like..

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

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

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

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());

Ok as You Like

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.