How to build a PHP CRUD application with star rating using jQuery

Step 1 - Create a MySQL table for hotels

Let us create a table named 'hotels' in MySQL database. This table will have hotel details. I have a database called 'demo'. So 'hotels' Table will be created in this database. If you have an existing database other than demo you can also use it. Just make sure same table name does not exist already. Table structure is given below:

Table has 5 columns.

1. id - It is the primary key and auto incremented
2. hotel_name - Name of the hotel
3. description - Description of the hotel
4. rating - Rating of the hotel, it is an integer, values 0 - 5
5. date_modified - Date of modification, default current date

Create table script for this table is given below, you can use this code to create the table. You can also download and get the scripts.

After you run the scripts, verify if data is inserted correctly, it should have below data in it:

Step 2 - Connect to MySQL database (dbconnect.php)

Use below script to connect to the database. Note that we have this database connection php program in 'cfg' folder. This is written once and used in every program where database connection is needed. This will be easy for maintenance and also will enable reusability of the code.

I am using mysqli_connect() function which needs 4 parameters.

1. server - in our case it is localhost
2. userid - we are using root user
3. password - no password for user root
4. database name - demo in our case.

For detail database connection understanding please read topic How to connect to MySQL database in PHP using MySQLi and PDO.

Step 3 - Create Add/Update forms with Star Rating (index.php)

In index.php, we have an add form, an update form and a table to show the list of existing hotels. So, all CRUD operations can be performed using this. Overview of index.php is given below:

1. Create a form to add a hotel
2. Show a list of hotels in a html table
3. For each hotel displayed, show an update link and a delete link. Use flags (for edit or delete) as a parameter.
4. If edit flag is set, show update hotel form
5. If delete flag is set, write delete hotel scripts
6. When add/update form is submitted use process_hotel.php to add/update hotel in database.

Note that all functions related to highlighting and selection of stars are done using jQuery written in star_rating.js.

Add Hotel Form

This is a simple form to add Hotel Name, Description and a rating. For selection of Stars, there is an input hidden field value which is set by selection of stars. Let's see the form:

For Star Rating, the label and a hidden input field are used. For Not-Rated symbol, glyphicon-minus is used and in a for loop, 5 Stars are displayed. The label or caption for each symbol are displayed.

onMouseOver - showStar() function highlights the stars and corresponding text appears on the right side.
onMouseOut - removeStar() function removes a highlight and corresponding text disappears.
onClick - selectStar() function selects a particular star and displays the corresponding text on the right side. Note that it works exactly same way for Not-Rated also.

Update Hotel Form

Display Hotel List

Screenshot of the list of hotels is given below:

It is a simple html table where details of each hotel in a for loop are displayed after fetching from the database.

To display the rating, integer value is changed to a symbol and a text (one-star, two-star etc.) using a switch statement.

We will display the update form based on the flag, if edit flag is set, update form will be dispalyed with data for the selected hotel. But for delete flag, we will be deleting the hotel from database. See the code below:

Here is the final index.php

Step 4 - Write JavaScripts/jQuery (star_rating.js)

These scripts are used to handle highlighting and selecting Star rating. Also, note that if no star rating is given then there is another symbol (minus symbol) is displayed which denotes Not-Rated.

Function getStar()

Once we know the value of rating, we can pick up the caption and label from the two arrays for the index position of rating value (line 9 and 10). Depending on whether this rating value is = 0 or > 0, we can choose if we need to highlight the stars or highlight the not-rated (minus sign) symbol. Note that classes 'highlight' and 'not-rated' are defined in style.css.

Function removeStar()

removeStar() function simply removes all highlighted stars and not-rated symbol. It also removes the corresponding caption.

Function showStar()

showStar() function is called with the list-item 'li' object as parameter. It first removes all highlights by calling removeStar() function then it checks the index position of list item (li). Note that it highlights all stars with position less than or equal to the index of the list item.

Function selectStar()

selectStar() function works the same way as showStar(), only difference is that it changes the value of the rating field in the form when user clicks on a star or on not-rated symbol.

Step 5 - Process form data (process_hotel.php)

Code to process data after form is submitted, is written in process_hotel.php. It checks if Add form is submitted or update form is submitted. Then it takes the values from the form and inserts into the table 'hotels' or updates the same table. A message is displayed on top of the form.

Let's see the code.

Step 6 - Add CSS (style.css)

I have already added style.css in index.php.

You can see various classes which are dynamically added/removed using jQuery. Keep this style.css file in your css folder.