Hello,
I am trying to set up a relational database in MySQL.
What I want is Three tables (Applicants, Credit, Address)
In the Applicants table I want to only store the Applicant_ID(PRIMARY KEY), First_Name, and Last_Name.
In the Credit table I want to store the Credit_ID, Name_On_Card, Card_Number, Pin, Expiration, and then I read that I add use the Primary key from my (Parent) Applicant table to create a foreign key.? This is confusing me big time!!
In the Address table I want to store Address_ID and am trying to have a foreign key from the Applicant Table Applicant_ID
What I’m trying to get out of this is a user enters their information in PHP input box, then the data will be stored in the MySQL database in three tables. With this will I be achieving what I want? Is this how I go about doing this?
Theres also syntax errors after the first table is created…
[php]
// BUILD A TABLE FOR THE APPLICANTS INFORMATION
$query = “CREATE TABLE Applicant (
Applicant_ID SMALLINT(8) ZEROFILL NOT NULL AUTO_INCREMENT,
First_Name VARCHAR(32) NOT NULL,
LastName VARCHAR(32) NOT NULL,
PRIMARY KEY (Applicant_ID)
) ENGINE=INNODB”;
$result = mysql_query($query);
if (!$result) echo("Unable to create Applicants table in icemt databse! " . mysql_error());
// BUILD A TABLE TO HOLD APPLICANTS ADDRESS INFORMATION
$query = “CREATE TABLE Address (
Address_ID SMALLINT(8) ZEROFILL NOT NULL AUTO_INCREMENT,
FKApplicant_ID SMALLINT(8) NOT NULL,
INDEX Applicant_Index (Applicant_ID),
Street = VARCHAR(64) NOT NULL,
Apt_Number = VARCHAR(15) NOT NULL,
City = VARCHAR(32) NOT NULL,
State = VARCHAR(32) NOT NULL,
Zip = VARCHAR(16) NOT NULL,
FOREIGN KEY (FKApplicant_ID) REFERENCES Applicant(Applicant_ID) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB”;
$result = mysql_query($query);
if (!$result) echo ("Unable to create the Address table in icemt database! " . mysql_error());
// BUILD A TABLE TO HOLD APPLICANTS CREDIT INFORMATION
$query = “CREATE TABLE Credit (
Credit_ID SMALLINT(8) ZEROFILL NOT NULL AUTO_INCREMENT,
FKApplicant_ID SMALLINT(8) NOT NULL,
Name_On = VARCHAR(128) NOT NULL,
Number = VARCHAR(128) NOT NULL,
Pin = VARCHAR(128) NOT NULL
Expires = VARCHAR(16) NOT NULL,
FOREIGN KEY (FKApplicant_ID) REFERENCES Applicant(Applicant_ID) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB”;
$result = mysql_query($query);
if (!$result) echo(“Unable to create Credit table in icemt database!” . mysql_error());
[/php]