PHP MYSQL Insert Into one table from another

Question

I have tried a bunch of soultions on here an other forums but always get errors. Here is current PHP code using hostgator and PHP 5.2

$insert = "INSERT INTO events (datecreated, fullname, email, title, url, picurl, desc, psw, skypeid, event) SELECT jvs.datecreated, jvs.fullname, jvs.email, jvs.title, jvs.url, jvs.picurl, jvs.desc, jvs.psw, jvs.skypeid, jvs.event FROM jvs";
if (! mysqli_query($dbh, $insert)  ||  mysqli_affected_rows($dbh) == 0)
            {
die ("<BR><BR><BR>Failed to insert data- " . mysqli_error($dbh));
            }

Error I get is:

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 'desc, psw, skypeid, event) SELECT jvs.datecreated, jvs.fullname, jvs.email, jvs.' at line 1

I have checked every field in both tables and the spelling is correct and the fileds are there. The fileds are not in the same order in events table and that table has many more fields. Thanks, Ed

possible duplicate of [MySQL: "You have an error in your SQL syntax... near 'desc) VALUES ('Idea','Description')'"](http://stackoverflow.com/questions/5357903/mysql-you-have-an-error-in-your-sql-syntax-near-desc-values-idea-des) — Michael Berkowski, Dec 18 '12 at 02:30

score 3 · Accepted Answer · answered Dec 18 '12 at 02:29

3

DESC is a MySQL Reserved keyword. You should escape with backtick, eg

INSERT INTO tableName(......, `desc`, .....)
SELECT ...

MySQL Reserved keyword List

answered Dec 18 '12 at 02:29

John Woo

258,903
69
498
492

2

Thought to myself _who is JW, with 46k that I've never seen before?_ But you're Kuya John/John Woo. :) – Michael Berkowski Dec 18 '12 at 02:34
@MichaelBerkowski it's been a habit to change my name and pass every 30 days. I don't know why. Maybe I'm sick. haha! – John Woo Dec 18 '12 at 02:35
1

Thank you. That worked perfectly. Glad I asked here or I would have spent many more hours with this. – Ed Newman Dec 18 '12 at 03:39

score 0 · Answer 2 · answered Dec 18 '12 at 03:10

0

1. See at desc, desc is a reserved word and readed as statement and you must add backtick to it.

2. You must add seperator between two query (INSERT INTO and SELECT)

3. And where is your VALUES, INSERT INTO must include a VALUES

And the fix code is:

$insert = "INSERT INTO events (`datecreated`, `fullname`, `email`, `title`, `url`, `picurl`, `desc`, `psw`, `skypeid`, `event`) VALUES('datecreated', 'fullname', 'email', 'title', 'url', 'picurl', 'desc', 'psw', 'skypeid', 'event');
SELECT jvs.datecreated, jvs.fullname, jvs.email, jvs.title, jvs.url, jvs.picurl, jvs.desc, jvs.psw, jvs.skypeid, jvs.event FROM jvs";
if (! mysqli_query($dbh, $insert)  ||  mysqli_affected_rows($dbh) == 0){
   die ("<BR><BR><BR>Failed to insert data- " . mysqli_error($dbh));
}

answered Dec 18 '12 at 03:10

Michael Antonius

942
2
11
20

1

This is inserting from one table to another so values are not used. Looks like you backticked everything. I guess that would be a good idea to be safe. Just putting the backticks around desc solved everything. Thanks. – Ed Newman Dec 18 '12 at 03:38
I tried to mark yours as accepted but it wouldn't without unchecking the other answer. He answered first so I gave the accept to him. – Ed Newman Dec 18 '12 at 03:42
i'm sorry ..., i don't know :) – Michael Antonius Dec 19 '12 at 05:01

PHP MYSQL Insert Into one table from another

2 Answers2