Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialAllen Lamphear
Courses Plus Student 6,660 PointsDatabase Normalization .... But A PHP Question
Hopefully a quick answer for a quick question. I was reading and trying out Database Normalization but I don't think I get some of it with using PHP. I'll show my examples.
Without DB Normalization:
CREATE TABLE Users (
user_id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(30) NOT NULL
email VARCHAR(80) NOT NULL,
phonenumber VARCHAR(16) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE INDEX (email)
) ENGINE=INNODB;;
Now, in php you would have a form with all the input elements. Then you would have a script that would insert those inputs into the database. ie:
$sql = "INSERT INTO user (firstname,lastname,email,phonenumber) values ('$firstname', '$lastname', '$email', '$phonenumber')";
Now With DB Normalization
CREATE TABLE Users (
user_id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(30) NOT NULL
email VARCHAR(80) NOT NULL,
PRIMARY KEY (user_id),
UNIQUE INDEX (email)
) ENGINE=INNODB;;
CREATE TABLE PhoneNumber (
phone_id INT NOT NULL AUTO_INCREMENT,
pnum VARCHAR(16) NOT NULL,
PRIMARY KEY (phone_id),
UNIQUE INDEX (email)
) ENGINE=INNODB;
There obviously would be a foreign key in the PhoneNumber table for Users(user_id)
But how would I use the normalization in php to have the same 1 form but input the firstname, lastname, email into the first table and then the phonenumber in the next?
I know when selecting them both to be shown in site you would be joining the tables.
2 Answers
Zachary Green
16,359 Pointsyou can use a SQL transaction where a group of related queries can be excted if no errors are present. so you could write: PDO::beginTransaction // insert in to user table PDO::lastInsertId // insert into phonenumber table PDO::commit() here are some links http://php.net/manual/en/pdo.begintransaction.php, http://php.net/manual/en/pdo.lastinsertid.php
Allen Lamphear
Courses Plus Student 6,660 PointsThank you Zachary Green for the advice! I will give this a try today and see how it does. I appreciate it very much!
Andrew Shook
31,709 PointsAllen Lamphear, you can either do it the way Zachary recommend, which is probably better, or you can have pdo return the id after the user is inserted and then insert the phone number. I think Zac has the better idea but I figured I would give you some options.
Allen Lamphear
Courses Plus Student 6,660 PointsThank you Andrew Shook I will try both. :-)
Andrew Shook
31,709 PointsIn all honesty, Zac's answer will probably give you better performance, because it will save you a trip to the database.
Andrew Shook
31,709 PointsAndrew Shook
31,709 PointsZachary Green, could you move your response from a comment to an answer. It will show that the question has been answered and you can get some upvotes for your good response.