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 trial

PHP

Database 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.

Andrew Shook
Andrew Shook
31,709 Points

Zachary 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.

2 Answers

Zachary Green
Zachary Green
16,359 Points

you 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

Thank 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
Andrew Shook
31,709 Points

Allen 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.

Thank you Andrew Shook I will try both. :-)

Andrew Shook
Andrew Shook
31,709 Points

In all honesty, Zac's answer will probably give you better performance, because it will save you a trip to the database.