2 tables with the same named columns doesnot work

Question

I have two table and both have 1 field named personID, in one table it auto-increment and I want to write it to another table. it happens on the same page. I generate person id and then trying to call using select and write in another but it doesn't work. please help me .

The PHP code that does not work looks like this:

$result2 = mysql_query ("
    INSERT INTO 'topics' ('personID') 
        SELECT personID 
        FROM persons 
        WHERE personID = 1
");

I am not very familiar with mysql, but isn't your query just wrong? — Loko, Oct 31 '13 at 08:47
[Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). — zessx, Oct 31 '13 at 08:48

score 1 · Answer 1 · answered Oct 31 '13 at 08:48

1

You are telling the database to insert PersonID from second table to you first table where PersonID in second table is 1.

That is equivalent to saying Insert 1 into first table table.

$result2 = mysql_query ("INSERT INTO topics (personID) VALUES (1)");  // equivalent to your query.

answered Oct 31 '13 at 08:48

Hanky Panky

46,730
8
72
95

Quotes removed, Thanks. – Hanky Panky Oct 31 '13 at 08:50
The quotes are his only problem, see the example code above :) – Fluffeh Oct 31 '13 at 08:51
Nope. That's an overkill. Look at the question again, that sub query is not even needed – Hanky Panky Oct 31 '13 at 08:51
Oh, I agree that it's not needed - but it WILL work. Besides, he might want to copy more columns along rather than just the ID. – Fluffeh Oct 31 '13 at 08:53

score 0 · Answer 2 · answered Oct 31 '13 at 08:47

There isn't anything wrong with the query, except you are using single quotes:

INSERT INTO topics (personID) 
SELECT personID FROM persons where personID = 1

If you are using a reserved word in MySQL, you should use a backtick ` (normally next to the 1 on a keyboard) to encompass the name.

See following example:

mysql> select * from test1;
+------+-------+
| id   | varry |
+------+-------+
|    1 | aaa   |
+------+-------+
1 row in set (0.07 sec)

mysql> select * from test2;
+------+-------+-------+
| id   | barry | third |
+------+-------+-------+
|    1 | ccc   |  NULL |
| NULL | d     |     1 |
| NULL | d     |     2 |
| NULL | d     |     3 |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> insert into test1 (id) select id from test2 where barry='ccc';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into test1 ('id') select 'id' from test2 where barry='ccc';
ERROR 1064 (42000): 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 ''id')
 select 'id' from test2 where barry='ccc'' at line 1
mysql>

score 0 · Answer 3 · answered Oct 31 '13 at 08:50

0

After Calling insert inside auto increment table just do this

$id = mysql_insert_id ();
$result2 = mysql_query ("INSERT INTO 'topics' (personId) VALUES ('$id')");

And that's all

answered Oct 31 '13 at 08:50

Samy Massoud

4,295
2
35
48

score 0 · Answer 4 · edited Oct 31 '13 at 09:18

0

In your query you always select person with personID = 1. Does it exist? If you insert person in transaction, make sure the transaction is committed.

edited Oct 31 '13 at 09:18

IvanH

5,039
14
60
81

answered Oct 31 '13 at 08:51

Zali

311
2
5

score 0 · Answer 5 · answered Oct 31 '13 at 08:51

0

You need to specify table.columnName.

$result2 = mysql_query ("
INSERT INTO topics (topics.personID) 
    SELECT personID 
    FROM persons 
    WHERE persons.personID = 1
");

answered Oct 31 '13 at 08:51

Maz I

3,664
2
23
38

2 tables with the same named columns doesnot work

5 Answers5