RE: how to add foreign key in alter command
Date: Sun, 17 May 2009 14:25:55 +0800 From: nathan.vorbei.t...@gmail.com To: mysql@lists.mysql.com Subject: how to add foreign key in alter command Hi guys Please tell me the command syntax, how to add a colmmen foreign key in alter syntax thanks Hello Nathan, Try the syntax below: ALTER TABLE tbl ADD foreign key(x) references(x); N.B: Where (x) is your foreign key. Hope that helps. Greetings from Nigeria. Alugo Abdulazeez www.frangeovic.com _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
RE: Confused about syntax for specific join with 3 tables
Date: Sat, 16 May 2009 15:39:56 -0700
From: davidmichaelk...@gmail.com
To: mysql@lists.mysql.com
Subject: Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the MySQL
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this logically, I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as student joining to score joining to grade_event.
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
Hi David,
Well I could say it's probably because grade_event is a parent table while
score is a child table. And the parent joined first (you know, the deserved
respect) :)). Cheers.
Alugo Abdulazeez
www.frangeovic.com
_
Windows Live™: Keep your life in sync. Check it out!
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_012009
RE: select data from two table and will sort by price in second table
From: haidarpes...@gmail.com To: mysql@lists.mysql.com Subject: select data from two table and will sort by price in second table Date: Wed, 29 Apr 2009 10:46:48 +0700 dear all, please help us mien for select data from two table with details as follows: primery tabel : bookcatalog second table : pricelist for seaching we will try to sort by price (in second table). our databese details like this: SELECT id, title, author from bookcatalog where isbn LIKE '%$query%' or title LIKE '%$query%' or author LIKE '%$query%' for information id in bookcatalog and pricelist are same. thanks a lot Haidarpesebe Hi, Can you be clearer please? _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
RE: SOLVED! (was: RE: Error : Incorrect key file for table 'X')
From: ad...@asarian-host.net Subject: SOLVED! (was: RE: Error : Incorrect key file for table 'X') Date: Sun, 26 Apr 2009 21:15:15 + To: mysql@lists.mysql.com Okay, I finally got it fixed. I was on the right track going the mysqldump route, but turns out I had to physically do a 'rm -rf' on the mysql dir (sans the mysql database itself; and after mysqld was shut down, of course), even though I had mysqldump add command to DROP existing databases first. After that, the import succeeds, and all errors are gone. Odd. It's been quite quiet on this list of late. I expected at least someone to know the answer. If this is indicative of people's interest in MySQL these days, then maybe it really IS a good idea to start using PostgreSQL. - Mark Hello Mark, Well thank God you finally mentioned it. I've been wondering why it has to be s dry on the mysql list when indeed the opposite is the case on the PHP mailing list(I love that list sooo much cos there's never a dull moment). My guess is that on this list, we still haven't imbibed the open source culture of give for the benefit of all. Maybe we should be reminded that this list is created for users of mysql to ask questions when they need assistance and always be ready to help others when they have answers to their questions. This is my humble submission.Greetings from Nigeria. Alugo Abdulazeez www.frangeovic.com _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
RE: Need help with mysql prob
Date: Mon, 20 Apr 2009 08:06:05 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi,
abdulazeez alugo wrote:
Date: Sun, 19 Apr 2009 23:19:56 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi Alugo,
Hi Andy,
Thanks for your prompt response. However, since tbl1_id has an
auto_increment value in tbl1, mysql is actually generating the values for
it automatically.
Ah, I see your point. I'm guessing by your code you're using PHP? If
so call mysql_insert_id() after you've inserted your record into tbl1.
That will give you the ID of the auto increment column - see the note in
the below link about bigint columns if your data type is bigint.
http://uk3.php.net/mysql_insert_id
Hope this helps,
Andy
Yes I'm using PHP. Can it be something like
function newPost_tbl1($id, $entry, $text)
{
$conn;
$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)
VALUES ('$id','$entry', $text)', $conn);
$tbl1_id = mysql_insert_id($conn);
}
Yep that's about it. You said that tbl1_id is an auto-increment column,
why are you including it in the insert query? You should just need
entrytitle and entrytext, then $tbl1_id will be the value of the tbl1_id
field.
I hope you've just missed out everything on the $conn line just for
short-hand, because $conn needs to be a valid connection resource
(result from mysql_connect) before passing it to mysql_query. Also on
your MySQL query line, you've started the insert command string with a
quote but terminated it with a single apostrophe, this terminator
should also be a quote. Your $text should also be enclosed with a
single apostrophe, and don't forget to clean your input ($entry and
$text) otherwise you'll be vulnerable to SQL injection and XSS attacks.
Andy
Hi Andy,
Thank you very much you have been really very helpful. All those mistakes you
pointed at in the script about the apostrophe and others, are simple mistakes I
just made in the rush of typing the message; and yes $conn is the result from
mysql_connect. In my original script I clean my script to avoid mysql injection.
However, if you look at the code very well, you'll realize that I have stored
the result of the mysql_insert-id($conn) in a php variable $tbl1_id and this is
defined within a function newPost_tbl1($id, $entry, $text). My problem now is
how to access that variable in the function that inserts into the child table
so that I'll have the same value for tbl1_id in tbl1 and tbl1_id in tbl2.
Thanks in advance.
Alugo.
_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx
RE: Need help with mysql prob
Date: Mon, 20 Apr 2009 13:03:14 +0100 From: andy-li...@networkmail.eu To: defati...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: Need help with mysql prob Hi Alugo, abdulazeez alugo wrote: Hi Andy, Thank you very much you have been really very helpful. All those mistakes you pointed at in the script about the apostrophe and others, are simple mistakes I just made in the rush of typing the message; and yes $conn is the result from mysql_connect. In my original script I clean my script to avoid mysql injection. No worries, just thought I'd point it out in case ;-) However, if you look at the code very well, you'll realize that I have stored the result of the mysql_insert-id($conn) in a php variable $tbl1_id and this is defined within a function *newPost_tbl1($id, $entry, $text).* My problem now is how to access that variable in the function that inserts into the child table so that I'll have the same value for tbl1_id in tbl1 and tbl1_id in tbl2. Why not just return it from the function? $tbl1_id = mysql_insert_id(); return $tbl1_id; Then in the code that calls newPost_tbl1 ... $tbl1_id = newPost_tbl1($id, $entry, $text); newPost_tbl2($tbl1_id, ); Regards, Andy Thank you very much Andy, Now my problem is solved. Cheers. Alugo Abdulazeez. _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
Need help with mysql prob
Hi guys, I'm having a really terrible problem with my mysql coding and I'ld appreciate any help I can get on this cause it's really driving me crazy. I have two tables and I've succeeded in creating a one to many relationship between both table but the problem I'm having is that I dont know how to put the value of the foreign key into the table. The tales are as below: N.B: I'm using php-mysql //Table 1 $result= mysql_query(CREATE TABLE tbl1 (tbl1_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(tbl1_id), entrytitle VARCHAR(50), entrytext TEXT) ENGINE=InnoDB) //Table2 $result = mysql_query(CREATE TABLE tbl2 (tbl2_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(tbl2_id), tbl1_id INT UNSIGNED NOT NULL, name VARCHAR(50), title VARCHAR(50), comment TEXT, INDEX (tbl1_id), FOREIGN KEY(tbl1_id) REFERENCES tbl1(tbl1_id))ENGINE=InnoDB) Now I have successfully created a relationship between the two tables but how do I make sure the value of tbl1_id in tbl1 is equal to the value of tbl1_id in tbl2??? Thanks in anicipation of your useful suggestions. Alugo Abdulazeez. www.frangeovic.com _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
RE: Need help with mysql prob
Date: Sun, 19 Apr 2009 22:50:20 +0100 From: andy-li...@networkmail.eu To: defati...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: Need help with mysql prob Hi, Now I have successfully created a relationship between the two tables but how do I make sure the value of tbl1_id in tbl1 is equal to the value of tbl1_id in tbl2??? MySQL handles this for you. Simply INSERT the value into tbl1, then INSERT the value of tbl1_id in tbl2. Then try INSERT'ing a value into tbl2 that doesn't exist in tbl1_id and MySQL will throw an error and the insert will fail. Thanks, Andy Hi Andy, Thanks for your prompt response. However, since tbl1_id has an auto_increment value in tbl1, mysql is actually generating the values for it automatically. _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
RE: Need help with mysql prob
Date: Sun, 19 Apr 2009 23:19:56 +0100
From: andy-li...@networkmail.eu
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: Need help with mysql prob
Hi Alugo,
Hi Andy,
Thanks for your prompt response. However, since tbl1_id has an
auto_increment value in tbl1, mysql is actually generating the values for
it automatically.
Ah, I see your point. I'm guessing by your code you're using PHP? If
so call mysql_insert_id() after you've inserted your record into tbl1.
That will give you the ID of the auto increment column - see the note in
the below link about bigint columns if your data type is bigint.
http://uk3.php.net/mysql_insert_id
Hope this helps,
Andy
Yes I'm using PHP. Can it be something like
function newPost_tbl1($id, $entry, $text)
{
$conn;
$result= mysql_query(INSERT INTO tbl1 (tbl1_id, entrytitle, entrytext)
VALUES ('$id','$entry', $text)',
$conn);
$tbl1_id = mysql_insert_id($conn);
}
Is the above code the best way to go about it?
Thanks in advance.
_
Drag n’ drop—Get easy photo sharing with Windows Live™ Photos.
http://www.microsoft.com/windows/windowslive/products/photos.aspx
RE: auto_increment Issue
Date: Fri, 10 Apr 2009 15:15:28 +0530 From: jnaneshwar.banta...@kavach.net To: mysql@lists.mysql.com Subject: auto_increment Issue Hi All I have created a table.Now I need to make a field Auto_increment...Help me with this issue..An example will do.. Regards Jnani Hi Jnani, Assuming your table name is tbl and the field for the auto_increment is id, just write: ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT; I hope that helps. Cheers. Alugo Abdulazeez. _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
RE: auto_increment Issue
Date: Fri, 10 Apr 2009 15:55:33 +0530 From: jnaneshwar.banta...@kavach.net To: orasn...@gmail.com CC: defati...@hotmail.com; mysql@lists.mysql.com Subject: Re: auto_increment Issue Hi While trying for the same,I am getting the following error Incorrect table definition; there can be only one auto column and it must be defined as a key Jnani Hi Jnani, Assuming your table name is tbl and the field for the auto_increment is id, just write: ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT; I hope that helps. Cheers. Alugo Abdulazeez. Oh sorry! it should be ALTER TABLE tbl CHANGE id id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT; I hope this helps unless you have a column already defined as auto_increment in the same table. Cheers. _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx
RE: One to many relationship
Date: Mon, 6 Apr 2009 10:07:01 -0500 From: peter.braw...@earthlink.net To: defati...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: One to many relationship Abdul, Is this what you mean? CREATE TABLE Table1 ( table1_id INT UNSIGNED PRIMARY KEY AUTO INCREMENT, entrydate TIMESTAMP )ENGINE=INNODB; CREATE TABLE table2 ( table2_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, table1_id INT UNSIGNED NOT NULL, entrydate TIMESTAMP, FOREIGN KEY(table1_id) REFERENCES table1(table1_id) )ENGINE=INNODB; After this, the DB engine will ensure that any table1_id value you try to insert in table2 actually exists in table1. See the manual page for foreign keys. PB Yeah PB. Thanks. _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx
RE: PHP-MYSQL Question
Yeah I used the mysql_error and it returned Can't create table
'.\website\table2.frm' (errno: 150). So what does that say?
Date: Tue, 7 Apr 2009 17:38:59 +0400
From: evge...@kosov.su
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: PHP-MYSQL Question
Perhaps you don't have permissions to create tables?
It would have been much clearer if your script was like this:
$result=mysql_query($your_create_table_statement);
if($result){ printSuccessful;}
else {print Unsuccessful: .mysql_error()}
abdulazeez alugo wrote:
Hi guys,
Please can anyone tell me what I'm doing wrong with the code below? It keep
returning unsuccessful.
$result=mysql_query(CREATE TABLE table2(table2_id INT NOT NULL PRIMARY KEY
AUTO_INCREMENT,
table1_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
school VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL,
FOREIGN KEY(table1_id) REFERENCES table1(table1_id))
ENGINE = INNODB );
if($result){ printSuccessful;}
else {print Unsuccessful;}
Thanks in advance. Cheers.
Alugo Abdulazeez.
_
More than messages–check out the rest of the Windows Live™.
http://www.microsoft.com/windows/windowslive/
--
С уважением,
Евгений Косов
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=defati...@hotmail.com
_
Invite your mail contacts to join your friends list with Windows Live Spaces.
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
RE: PHP-MYSQL Question
Table1 is as below:
CREATE TABLE table1(table1_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(table1_id),
entrytitle VARCHAR(100) NOT NULL,
entrytext TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL) );
I did not put the engine. Could that be the problem?
Date: Tue, 7 Apr 2009 17:48:16 +0400
From: evge...@kosov.su
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: PHP-MYSQL Question
# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
What does table1 look like?
abdulazeez alugo wrote:
Yeah I used the mysql_error and it returned Can't create table
'.\website\table2.frm' (errno: 150). So what does that say?
Date: Tue, 7 Apr 2009 17:38:59 +0400
From: evge...@kosov.su
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: PHP-MYSQL Question
Perhaps you don't have permissions to create tables?
It would have been much clearer if your script was like this:
$result=mysql_query($your_create_table_statement);
if($result){ printSuccessful;}
else {print Unsuccessful: .mysql_error()}
abdulazeez alugo wrote:
Hi guys,
Please can anyone tell me what I'm doing wrong with the code below? It
keep
returning unsuccessful.
$result=mysql_query(CREATE TABLE table2(table2_id INT NOT NULL PRIMARY
KEY
AUTO_INCREMENT,
table1_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
school VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL,
FOREIGN KEY(table1_id) REFERENCES table1(table1_id))
ENGINE = INNODB );
if($result){ printSuccessful;}
else {print Unsuccessful;}
Thanks in advance. Cheers.
Alugo Abdulazeez.
_
More than messages–check out the rest of the Windows Live™.
http://www.microsoft.com/windows/windowslive/
--
С уважением,
Евгений Косов
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=defati...@hotmail.com
_
Invite your mail contacts to join your friends list with Windows Live
Spaces. It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
--
С уважением,
Евгений Косов
_
More than messages–check out the rest of the Windows Live™.
http://www.microsoft.com/windows/windowslive/
PHP-MYSQL Question
Hi guys,
Please can anyone tell me what I'm doing wrong with the code below? It keep
returning unsuccessful.
$result=mysql_query(CREATE TABLE table2(table2_id INT NOT NULL PRIMARY KEY
AUTO_INCREMENT,
table1_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
school VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL,
FOREIGN KEY(table1_id) REFERENCES table1(table1_id))
ENGINE = INNODB );
if($result){ printSuccessful;}
else {print Unsuccessful;}
Thanks in advance. Cheers.
Alugo Abdulazeez.
_
More than messages–check out the rest of the Windows Live™.
http://www.microsoft.com/windows/windowslive/
RE: PHP-MYSQL Question
I've tried it and i still got the same error. I even dropped table1 and
recreated it to include the ENGINE=InnoDB and it was successful but table2
remains unsuccessful.
Date: Tue, 7 Apr 2009 17:56:49 +0400
From: evge...@kosov.su
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: PHP-MYSQL Question
I suppose the problem is that table1.table1_id and table2.table1_id are
of different types. The first one is INT UNSIGNED and the second is just
INT.
abdulazeez alugo wrote:
Table1 is as below:
CREATE TABLE table1(table1_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(table1_id),
entrytitle VARCHAR(100) NOT NULL,
entrytext TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL) );
I did not put the engine. Could that be the problem?
Date: Tue, 7 Apr 2009 17:48:16 +0400
From: evge...@kosov.su
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: PHP-MYSQL Question
# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
What does table1 look like?
abdulazeez alugo wrote:
Yeah I used the mysql_error and it returned Can't create table
'.\website\table2.frm' (errno: 150). So what does that say?
Date: Tue, 7 Apr 2009 17:38:59 +0400
From: evge...@kosov.su
To: defati...@hotmail.com
CC: mysql@lists.mysql.com
Subject: Re: PHP-MYSQL Question
Perhaps you don't have permissions to create tables?
It would have been much clearer if your script was like this:
$result=mysql_query($your_create_table_statement);
if($result){ printSuccessful;}
else {print Unsuccessful: .mysql_error()}
abdulazeez alugo wrote:
Hi guys,
Please can anyone tell me what I'm doing wrong with the code
below? It keep
returning unsuccessful.
$result=mysql_query(CREATE TABLE table2(table2_id INT NOT NULL
PRIMARY KEY
AUTO_INCREMENT,
table1_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
school VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
entrydate TIMESTAMP NOT NULL,
FOREIGN KEY(table1_id) REFERENCES table1(table1_id))
ENGINE = INNODB );
if($result){ printSuccessful;}
else {print Unsuccessful;}
Thanks in advance. Cheers.
Alugo Abdulazeez.
_
More than messages–check out the rest of the Windows Live™.
http://www.microsoft.com/windows/windowslive/
--
С уважением,
Евгений Косов
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=defati...@hotmail.com
_
Invite your mail contacts to join your friends list with Windows
Live Spaces. It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
--
С уважением,
Евгений Косов
check out the rest of the Windows Live™. More than mail–Windows Live™
goes way beyond your inbox. More than messages
http://www.microsoft.com/windows/windowslive/
--
С уважением,
Евгений Косов
_
Show them the way! Add maps and directions to your party invites.
http://www.microsoft.com/windows/windowslive/products/events.aspx
RE: PHP-MYSQL Question
I've done that but it still gives the same error message. Date: Tue, 7 Apr 2009 16:25:15 +0200 Subject: Re: PHP-MYSQL Question From: spa...@googlemail.com To: defati...@hotmail.com CC: mysql@lists.mysql.com it is not the ENGINE as Eugene mentioned above, is that you need to use UNSIGNED when creating the second table CREATE TABLE table2 ( table2_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, table1_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, school VARCHAR(100) NOT NULL, comment TEXT NOT NULL, entrydate TIMESTAMP NOT NULL, FOREIGN KEY(table1_id) REFERENCES table1(table1_id)) ENGINE = INNODB _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
RE: PHP-MYSQL Question
Alright guys, I just solved the problem. I read from somewhere that if you're using a foreign key, it should be the either the primary key in the table or the index so since I already used auto increment on table2_id and I've already made it my Primary key, I just made table1_id the index and it returned successful. So that was the problem all along. Thanks for your suggestions. Best regards Alugo Abdulazeez. From: defati...@hotmail.com To: spa...@googlemail.com CC: mysql@lists.mysql.com Subject: RE: PHP-MYSQL Question Date: Tue, 7 Apr 2009 15:32:25 +0100 I've done that but it still gives the same error message. Date: Tue, 7 Apr 2009 16:25:15 +0200 Subject: Re: PHP-MYSQL Question From: spa...@googlemail.com To: defati...@hotmail.com CC: mysql@lists.mysql.com it is not the ENGINE as Eugene mentioned above, is that you need to use UNSIGNED when creating the second table CREATE TABLE table2 ( table2_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, table1_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, school VARCHAR(100) NOT NULL, comment TEXT NOT NULL, entrydate TIMESTAMP NOT NULL, FOREIGN KEY(table1_id) REFERENCES table1(table1_id)) ENGINE = INNODB _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx
One to many relationship
Hello all, I'm trying to design a relational database where two tables link to each other through unique id sa below Table 1 table1_id INT UNSIGNED NOT NULL AUTO INCREMENT, entrydate TIMESTAMP Table2 table2_id INT UNSIGNED NOT NULL AUTO_INCREMENT, table1_id INT UNSIGNED NOT NULL, entrydate TIMESTAMP, Sorry. The above is just a rough sketch of what I have here and I hope you understand what I'm trying to portray but the value of table1_id inside table2 keeps returning the value 0 thereby it doesn't correspond with any value in table1_id in table1. Please I need help on this issue. Thanks in anticipation of your prompt response. _ Drag n’ drop—Get easy photo sharing with Windows Live™ Photos. http://www.microsoft.com/windows/windowslive/products/photos.aspx
