Mini PHP and MySQL Course Tutorial
Let’s see step by step, the logic to create a Web Application to manage a Real Estate agency using a hands-on approach and PHP and MySQL.
In the previous delivery, we discussed the elements necessary to carry out this application Web of Real estate, now we are going to practically see like Creating Base de Datos and the mentioned Tables. In our browser, Internet Explorer, or even better, FireFox, we are going to write the following address:
This address shows us a page similar to the following page, if you have installed the AppServ as we had recommended in the tutorial AppServ installation – Apache, PHP, MySQL, phpMyAdmin
We click in the first link that says phpMyAdmin Database Manager. As its name indicates, it will allow us to Manage the MySQL Database of our PC or, if we are working on line, our Server.
At this stage we will be asked for the MySQL‘s user name and password, we set it up when we installed MySQL. If you do not remember where, you can read it in the Appserv installation – PHP, Apache, MySQL tutorial.
After the user and password are entered we will see a page similar to the following image. This is the main page of phpMyAdmin.
At the top left of the image we can see an icon with a small house. It will allow us to return to this initial page of phpMyAdmin.
On the bottom it says Data base. There we will see the list of Data bases that are in our PC in the MySQL (or hosting). We can select from this list the databases to work with.
Creating the Real Estate Database
In this case we do not even have a Data Base for our Real Estate Agency. Because of this we are going to move to the centre of the page where it says: Create New Data base, we will write “realEstate” and click Create. This way we managed to create the Real Estate Data Base that we mentioned in our second post of this Mini Tutorial Course. This will appear on the left of the screen, as it is shown in the following image. In the centre of the screen, you can Create Tables that the database will contain (remember that they were Properties and Cities).
Creating the Properties table
We will now write the name “properties” where it says Create new table on database realEstate, and in number of fields 20 (it does not matter that later there will be too many – it is better to have many), and then click Go.
A series of 20 text boxes will appear to enter the Name of each Field, the Type of Field and the Length of the field.
To classify the information, Data Bases allow indicating the Data type that each Field will contain. This is called Field Type and will depend on whether it is a numerical value without decimal, a numerical value with decimal, a short text value, a lot of text, or a date. We will see the appropriate one for each case.
We must enter the Fields as the following image shows, with their Names and Types. It is important to remember if we used capital letters or not. PHP differentiates uppercase from lowercase and this can cause some problems. I never use upper case, I write everything in lower case.
After we entered all the fields we click Save, this Creates the Table “properties” with its respective fields.
As you can see, for the id it uses an INT type, indicating that the data to store will be a numerical value without decimal. This will allow us to indentify each Property by id. In the fields title, sqrmeters, toilets, rooms, use VARCHAR with a length of 255. This will allow numbers, letters or symbols to a maximum length of 255. It is not much but the Length Control will be done in the Text Boxes when entering each property, since the VARCHAR in spite of being 255 characters long does not use all of this space. If we record in it only the title “house in the beach”, it will use only 18 bytes or characters.
Also note that I did not use symbols nor spaces in the field names.
Another thing is that although rooms will contain a number, I did not use a type INT, I used VARCHAR. I use as rule that if a number is not used for direct searches or mathematical operations I assign a type VARCHAR.
In saleprice and rentprice, I used DOUBLE, that allows us to save numbers with decimals.
In propertytype, I used INT to record 1 for houses on sale, 2 for houses for rent and 3 if it is both cases. In other words, each type of building will have a numerical code to identify them.
In description, I used TEXT since it is a long text that can be 10, 100 or 500 characters long, therefore exceeds the maximum of a VARCHAR that is 255. A field TEXT is a better option, as it is virtually limitless.
Finally, inputdate, where we will record the date in which the property was entered to the system, uses the type DATE, that allows us to save a date.
Creating the Index for the Properties table
As we said, the id will be the Field that Identifies the Property. Fields used to identify records (records = each one of the properties), is called Index. An index, allows speeding up of searches when there are many Records, that is many Properties in the Table. If we look below, it says “No index defined!” Well, let’s do it. For this, where it says Create an index on 1 column (s), we click Go.
In the following page, we select as field ‘Id’. Then we verify that it says PRIMARY as Index Type, as this will be the main way of searching. Finally we click in Save.
We will see then, that id, is underlined (I) indicating that it is an Index. Also, down in the List of Indexes (II), PRIMARY appears for the field Id.
We could enter the number of property by hand, 1, 2, 3, etc. Or we can set it as an Automatic number that AutoIncrements. For this, we are going to click on the Icon Change (the pencil labeled III in the image).
In the following page, as shown in the image, we select as Extra parameter, auto_increment. This is so that the number increases itself, automatically.
Finally we click Save.
In this way we have our first Table ready, called properties, in our Data base called realEstate.
Creating the Cities table
Now in similar fashion, we will create the table “cities“. While doing this, we will review a little what we have done with properties.
We click in the Data Base realEstate that we see on the left in the image (I).
Then we write the Name of the Table to create, in this case “cities” (II), and the number of fields (III), for example 10, and click Go.
This table, as we see in the image, will have only 2 fields. These are id, type INT and the name of the city of type VARCHAR, 255 of length. The we press Save.
It will show No index defined! To create an index by ID, we click Go.
We indicate that the index will be id, of type PRIMARY and then click Save.
Select change (in the small pencil under Action), to indicate again that id will be a field that AutoIncrements, as we did in the table properties.
And with this we have finished the 2 necessary tables for our Real Estate Web Application.
In our next delivery we will see how to Enter Data with phpMyAdmin to test the application when we build PHP pages.