Let's work on the condo database that we created with phpMyAdmin in the last part of this class.
If you click the word condo on the left pane of the phpMyAdmin screen, you should see a page that looks like this:
If you didn't finish creating the apartment, dog, and cat tables, go ahead and do that now. Then, click the word, "apartment", in the left panel.
Click the insert tab at the top of the page, and you'll see a page like this:
Remember that the apt_id is auto incremented, so we should leave this field blank and let the database come up with the value. Also remember that we set up the apt_number to be a VARCHAR with a length of 5, so names like "3N" or "10J" will fit but an apt_number like "333left side" will be too long. But then how many buildings have 333 floors?
The attribute size is to contain the square footage or square meters of the apartment. Just put in an integer. Similarly put in a 1 digit integer for the bedrooms field.
Put in 2 or more apartment records, just to have some data to play with.
After you click, you will a screen like this:
Just under the green box, you can see the SQL that was used to insert your data.
Let's look at that code:
INSERT INTO `apartment` (`apt_id`, `apt_number`, `size`, `bedrooms`) VALUES (NULL, '5A', '430', '1'), (NULL, '6C', '790', '1');This line is putting in 2 records into the apartment table. There are 2 lists inside of parentheses and these the values for the fields in the apartment table. If you were just to put in 1 row into the apartment table the SQL would look like this:
INSERT INTO `apartment` (`apt_id`, `apt_number`, `size`, `bedrooms`) VALUES (NULL, '5A', '430', '1');Do you remember that we didn't fill in the field for apt_id because this field is auto incremented? And because we left it blank, the generated SQL shows that we are putting in NULL into that field.
Now we are going to add some dogs to the dog table. There is a one-to-many relationship between the apartment and the dog table. A record in the dog table show which apartment a dog is associated with.
In other words - the dog table's Foreign Key ( FK) will be the apartment table's Primary Key (PK). This means, by looking at the Foreign Key ( apt_id ), you can figure out where where Fido lives.
To put in a valid apt_id in the dog table, we must have previously inserted that apartment into the apartment table to generate its apt_id . That is why we made the apartment records before the dog records.
If you have a few records in the apartment table, let's try putting in some dog records.
Click the SQL tab at the top of the page to get to the SQL window. Then copy the code below:
INSERT INTO `dog` (`dog_id`, `apt_id`, `name`, `breed`, `bites`) VALUES (NULL, '1', 'Mr Pickles', 'Old English Sheep Dog', '0'), (NULL, '2', 'Akhenaten', 'Mexican Hairless', '0');and paste it into the SQL window.
Again, notice that I put in a dog_id as NULL . The database knows that it needs to generate the row's dog_id - so in the SQL a NULL here doesn't not mean insert NULL . It just lets the database go ahead and generate the ID.
Also, in the code above, I have the apt_id 's as 1 and 2. I am assuming that you have apartments with these ID's in your table. You can check if this is true by selecting the apartment table then clicking on the Browse tab. If you don't have these ID's, then change these values to suit what you have in there already. But more about browsing records in the next chapter!
Before we move on, add a few more dogs to these or other apartments. Use both the Insert tab and the SQL tab.
And then add at least 4 records to the cat table. And finally add at least 4 records to the fed_at table.
Now we'll take a look about reading from the database!
