Real Estate Website – Part 1, Database

by Jorge Oyhenard - 16 June 2008

Apache, PHP and MySQL

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.

Since long ago in the forum (www.forocreativo.net) users have wanted to learn PHP, and since it is a language that I like, I decided to write this mini hands-on course that will help them to learn with a practical application. This practical case will be a Real Estate Website.

It is important to clarify that this it is an exercise designed with the purpose of teaching in a practical way the possibilities of PHP as a language to build dynamic Websites and MySQL as database. In a real case we must have built more controls and a deeper analysis of the database. These are not part of this course, but we would happily answer any additional doubts in our forum.

Database Structure

There are basically two ways of building a Real Estate Website. One is to write the HTML of the site and a page per property, these being fixed and forcing you to manually create a HTML file per property. This is not advisable for a real business where properties are bought, sold, added and rented daily or weekly. Therefore my suggestion is to dynamically build the pages.

UA dynamic website would have a Database with all the properties, and in the database properties would be added, or marked as sold or rented and would no longer appear in the Web. With this purpose we will use a MySQL database.

We will read this MySQL database with a PHP (not HTML) page that automatically reads each property and shows them by repeating that page per each property without having to do it manually one at a time. That is the big advantage of building websites in this way.

By definition, a Database is a set of data.are the paper cards that some offices had (some still have) and those that we can electronically store in our computers. In this case the set of data will be stored in our data base in the Server, where it will be stored and managed by the MySQL Database Engine. This is also the engine that best gets along with PHP and the best option for these cases.

This set of information must have what is called Structure. The structure is how the information is organised. To understand the structure we must know the data we want to store, this is called Database Analysis. In big applications, the Database Analysis takes time and requires knowledge and practice to develop since it can facilitate or complicate the application in the future, and it is more or less possible to extend it for new goals.

As we said, this tutorial is to illustrate the operation of PHP and MySQL, not to create software analysts or software engineers. Therefore we will do it in a simple way.

¿What data are we going to handle in the Website?

We are obviously going to handle a Properties list. These properties are going to have data like square meterage measurements, number of bathrooms, bedrooms, price, location etc (in addition to the Rent or Sale price), among other data that will be able to be added as preferred in each case.

We must think how we would fill in the mentioned data. For example, if it will be a text box, a drop-down list, etc. The square metres, the number of baths, the number of rooms, can be simple text boxes.


The price depends on the type of property. In other words, if it is a property for sale, it will have a sale price, but if it is a monthly rent we will have another price. Then we have 3 pieces of data:

  • Sale Price
  • Rent Price

And another that indicates the Type of Property, that could be:

  • Sale
  • Rent
  • Both

Since it can be for sale and/or rent.

La ubicación puede ser una lista desplegable de ciudades formada por las ciudades de la zona donde esta nuestra inmobiliaria, en este caso es mejor prevenir que se ingresen los mismos datos de diferente forma, por ejemplo que un operario ingrese como “Santa Lucia”, otro como “Ciudad de Santa Lucia” y otro como “St Lucia”, es por eso que mi sugerencia es usar una lista de ciudades que estarán previamente almacenadas en la Base de Datos y se mostrarán en una lista desplegable al ingresar cada propiedad. Esta lista de ciudades puede ser ampliable o modificarse en futuro, por tanto así como las propiedades ocupara otra Tabla de la base de datos.

The location can be a drop-down list of cities made up of the cities of the region, that has been previously stored in the database. This is the preferable option in this case as it is better to prevent data from being entered in different ways, for example that a user enters “Santa Lucia”, other “City of Santa Lucia” and other “St Lucia. This list of cities can be expandable to be modified in future. Therefore they will use another Table of the database as the properties do.

What is a table?

As we said, the database is the set of all the information of our Real Estate Website. That database is divided in Tables, each table represents an information group. So far we have thought of two Tables:

Properties: stores the properties list. Properties will be able to be added, modified and deleted at will.

Cities: stores the cities list. Cities will be also able to be added, modified and deleted at will.

What are the fields?

As we said, the Database is made up of Tables that group the data, and each Table is made up of Fields. The Fields are each single piece of data that identifies an element of the Table. For example, we have already mentioned the fields: square metres, bathrooms, rooms, price, etc.

What are the records?

The Records are the elements stored in each Table. For example, each property will be a record and each city will also be a record.

The final scheme will be, already with its names – for which it is advisable not to have spaces or symbols:

Database: Real Estate

Tables:
Properties

  • Id
  • Title
  • SqrMeters
  • Bathrooms
  • Rooms
  • idCity
  • SalePrice
  • RentPrice
  • PropertyType
  • Description
  • InputDate

Cities

  • Id
  • Name

As we can see, I have added an Idfield for each table. This field will be a number that uniquely identifies each element. This has been done to ease the future programming of the site and search of information.

In the case of the Properties table, we can see the CityId field. This will be simply the number of city in the list, this is to avoid writing the name again and again. We only need to store the reference number of the Cities table.

We have now finished with this first installment. In the next one we will see how to create the database and the tables in MySQL using phpMyAdmin, you can see how to install them in your PC in this tutorial:

AppServ installation

One Comment on “Real Estate Website – Part 1, Database”

  1. Codespanish » Blog Archive » Mini PHP and MySQL course Says:

    […] MySQL Database with PHP: learn step by step the logic to create a web application using PHP and MySQL. […]

Leave a Reply