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.
Below is a screenshot of the folder structure and files used:
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
Below is the screenshot of the page:
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.
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.
id - It is the primary key and auto incremented
hotel_name - Name of the hotel
description - Description of the hotel
rating - Rating of the hotel, it is an integer, values 0 - 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.
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:
Create a form to add a hotel
Show a list of hotels in a html table
For each hotel displayed, show an update link and a delete link. Use flags (for edit or delete) as a parameter.
If edit flag is set, show update hotel form
If delete flag is set, write delete hotel scripts
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:
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.
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.
removeStar() function simply removes all highlighted stars and not-rated symbol. It also removes the corresponding caption.
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.
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.
Test 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:
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
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.
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.