Problem creating a reference table in mysql using php

Question

Hi all.

I am creating a two MySQL tables in PHP, using the code as given below:

$sql = "CREATE TABLE qotwMember
(
MemberId NOT NULL PRIMARY KEY,
Name varchar(255),
Passwork varchar(255),
emailId varchar(255),
)";


$sql = "CREATE TABLE qotwQuestion1111
(
QuestionId NOT NULL AUTO_INCREMENT,
Question varchar(5000),
MemberId varchar(255) FOREIGN KEY fkname REFERENCES qotwMember(MemberId),
PostDate date,
Vote int,
PRIMARY KEY (QuestionId)
)";
mysql_query($sql,$con);

Then i try to insert data into these tables. In the qotwMember table, the data gets entered, but when I try to insert data into the qotwQuestion1111 table, it gives me the error "Error: Table 'database1.qotwQuestion1111' doesn't exist"

I can not figure out what I am doing wrong here. Please help me with this problem.

Note: Both the tables have been created in a different php.

Regards Zeeshan

Answer 1

Are you sure you are selecting the right database each time? See: mysql_select_db()

Answer 2

I suspect you're not giving us the real SQL because neither of those statements will actually work - you're missing the datatype for the primary column and have some extra commas.

If that is your real SQL, then make sure you put or die(mysql_error($con)); after calls to mysql_query

Answer 3

When you are creating your tables, it is probably easier to use a MySQL front end such as MySQL query browser instead of trying to run the CREATE TABLE statements inside PHP. My guess is there is a syntax error in your second statement, so the table is not getting created. The front end will show you what the syntax error is.

Alternatively, you could check the return value of mysql_query to see if there is an error, and then use mysql_error() to read it out.

Answer 4

I have had the same problem as you with foreign key creation in MySQL (which is what your error is about).

When creating foreign keys, both the foreign key column and the reference column must be of the same data type and size. I noticed you did not give your primary key column any datatype or size. This is probably what is causing your error. Also, as others have pointed out, what engine you are using also will dictate if you can use foreign keys.

If you declare 'MemberID' as 'MemberID varchar(255) NOT NULL PRIMARY KEY' it should work as you have it now. I would suggest always giving your primary key columns a datatype and possible size. I don't know what your tables are for, but for a primary key column that is just an ID, i would recommend making it an INT of some sort (just remember to change your foreign key column to reflect that change).