MySQL copying record from one table to another problem.

[shreyaskudav]

   $sql = " INSERT INTO trade (trade_id, user_id, pkmn_id, level, exp, health, attack1, attack2, attack3)    SELECT (id, user_id, pkmn_id, level, exp, health, attack1, attack2, attack3)    FROM user_pokemon_db    WHERE user_id = '".$id."' AND id = '".$db_id."' ";    mysql_query($sql) or die(mysql_error());

When I run the code, I get this error now

 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 '203''' at line 1

I know the query is wrong as the error says..I took help from google to check the syntax..and its quiet the same as I have used!What problem is there in the query?

[davej]

A question like that would make more sense if you included your table definitions.

[thescientist]

are you sure $id and $db_id are both defined?

[shreyaskudav]

Table definitions:

CREATE TABLE IF NOT EXISTS `trade` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `trade_id` int(11) NOT NULL,  `user_id` int(11) NOT NULL,  `pkmn_id` varchar(50) NOT NULL,  `level` int(11) NOT NULL,  `exp` int(11) NOT NULL,  `health` int(11) NOT NULL,  `attack1` int(11) NOT NULL,  `attack2` int(11) NOT NULL,  `attack3` int(11) NOT NULL,  PRIMARY KEY (`id`)) 

CREATE TABLE IF NOT EXISTS `user_pokemon_db` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_id` int(11) NOT NULL,  `pkmn_id` int(11) NOT NULL,  `level` int(11) NOT NULL,  `exp` int(11) NOT NULL,  `health` int(11) NOT NULL,  `attack1` int(11) NOT NULL DEFAULT '11',  `attack2` int(11) NOT NULL DEFAULT '53',  `attack3` int(11) NOT NULL DEFAULT '90',  `team` int(11) NOT NULL DEFAULT '0',  PRIMARY KEY (`id`),)

These are the 2 tables @davej...!

 

@thescientist.. Actually yess! they are well defined cause, when I checked the query through echo, they were correctly evaluated !

 

PS: I think the error is in the query..if you see the error displaying... it says..

near '203''' at line 1

while there is nothing like ''' in the query..

AND id = '".$db_id."' ";

[justsomeguy]

Why not just print out the query so that you can see exactly what you are sending to MySQL? My guess is that your variables have quotes in them. But there's really no point in guessing when you can just print out the query and look at it.

[rootKID]

Is it me, or should you not set a "value" after "insert" in the sql?

 

I mean.. something like so:

"INSERT INTO trade (value1, value2) VALUES (derp1, derp2)"....

 

Just asking? Not sure tho x)...

[justsomeguy]

It's fine to use insert..select if you're inserting records by selecting something from another table.

[thescientist]

Is it me, or should you not set a "value" after "insert" in the sql?

 

I mean.. something like so:

"INSERT INTO trade (value1, value2) VALUES (derp1, derp2)"....

 

Just asking? Not sure tho x)...

 

http://www.w3schools.com/sql/sql_insert_into_select.asp

[shreyaskudav]

Why not just print out the query so that you can see exactly what you are sending to MySQL? My guess is that your variables have quotes in them. But there's really no point in guessing when you can just print out the query and look at it.

Yes..got it!

Actually the problem was to minor!

I don't know why query could not recognize the formatting I did for the variable : "'.$db_id.'"

It worked when I removed everything and just used variable: $db_id

[rootKID]

Ohh.. well, my bad then