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

CRUD operations are related to Select, Insert, Update and Delete operations on the data stored in a database. CRUD stands for Create (insert) Read (select) Update Delete. We can develop applications in PHP that can do CRUD operations along with a star rating. For example, you can create an application to maintain a list of hotels with star ratings. You can add/update hotels, you can add a star rating to a hotel, or you can change the rating of a hotel.

In this topis, we will develop a similar application using PHP, MySQL and jQuery.

PHP CRUD Operation Using JQuery ExampleAssumption

  1. You must have basic knowledge of PHP, HTML, CSS and JavaScript/jQuery. You can read below topics for more details:
    1. How to install xampp for Windows with setup for PHP development
    2. How to write PHP code with examples for beginners
    3. How to build a simple CRUD application in PHP and MySQL

Star Rating in PHP and MySQLFolders and Files

Below is a screenshot of the folder structure and files used:

star rating CRUD application in PHP using jQuery

We have created a folder called 'star_rating' under 'xampp/htdocs' as I am using XAMPP. If you are using WAMP, create 'star_rating' folder under 'www'.

Folder 'cfg' - In this folder I have kept dbconnect.php which is used to connect to the MySQL database.
Folder 'css' - This is for our custom stylesheet
Folder 'js' - In this folder, custom JavaScripts file is kept
index.php is my main program that allows add/update hotels with star rating and also it displays a list of hotels.
process_hotel.php is my php program to add/updates database after the form is submitted. This program is included in index.php.

Below is the screenshot of the page:

Crud Operation using jquery in PHP

In the above screen, admin (for example) can add a new hotel, also can update/delete and existing hotel. You can see, there is a star rating field in the form, rating can be selected from Not-Rated to Five-Star.

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:

Star Rating CRUD in PHP and MySQL

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.

hotels.sql

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

Star Rating CRUD in PHP and MySQL and jquery

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.

dbconnect.php

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

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

PHP Star Rating System with JavaScript

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:

crud operation and star rating using php/mysqli and jquery

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

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.

process_hotel.php

Step 6 - Add CSS (style.css)

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

style.css

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

Star Rating in PHP and MySQLTest the Application

Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/star_rating. You will see the home page displayed as below:

CRUD application in PHP using jQuery with star rating

Test if rating and captions are showing correctly when you take the mouse on any star. Note that by default, not-rated will be shown. Add a new hotel with a rating and see that newly added hotel is appearing in the list. You can test to modify rating of an existing hotel and also can delete a hotel.

download source code for Star Rating in PHP and MySQLDownload Source Code

I have put all codes in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can directly use the code or you can modify them as per your requirements.

Simple star rating CRUD system using PHP, jQueryConclusion

In this topic, I have given example of the hotel; it can be rating for a product or someone's performance. You can change it to meet your requirements. You can use it for any similar application. Hope it will be useful for you.