A Beginner’s Guide to Get Weekly Data In PHP

0
352
A Beginner's Guide to Get Weekly Data In php
A Beginner's Guide to Get Weekly Data In php

In this article, you will going to see how you get weekly data in PHP from MySQL database.You will see in detail and in simple way.

Let’s start with a full guide to get weekly data in PHP step by step :

To do this we will create table in database by executing below command.

Please create a table called employ in Database.

CREATE TABLE `employ` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now after you executed sql query in database you will see like this below screen .

Created Table
Created Table

After creating table in database using above SQL query, now our next important point is to insert some data in table.

To insert data in table in database called ’employ’ you have to execute insert query in database table.

Data can be inserted into database using INSERT INTO statement to add data in the table.

Below given Syntax for INSERT INTO statement.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2,...)

To do practice and learn more about SQL, you can visit SQL tutorial.

Please execute below SQL query in database to insert SQL statement.

INSERT INTO `employ` (`id`, `name`, `email`, `phone`, `created_at`) VALUES (NULL, 'Rocky', 'rocky@gmail.com', '9898989988', CURRENT_TIMESTAMP), (NULL, 'somo', 'somo@gmail.co', '9898989912', '2020-06-15 00:00:00');
INSERT DATA
INSERT DATA

Now you have done with the insert of data into the database table using INSERT STATEMENT query.

Now our main task is to come in front of us.

So to achieve this we have to create a connection between front-end and database.

To do this you have to pass the details regarding database connection.

Such as DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE.

For passing this details using define function in PHP.

After adding all this detail in define function, now another point is to pass this detail for database connection.

To create database connection use mysqli_connect().

As you see in MSQLi, in that i stands for improved.

After connection we going to check whether connection is done or not using mysqli_connect_errno().

If the connection successfully done our main task is get the weekly data from database.

Below PHP script regarding database connectivity.

<?php 
   /* database configuaration */
   define('DB_SERVER', 'localhost');
   define('DB_USERNAME', 'root');
   define('DB_PASSWORD', '');
   define('DB_DATABASE', 'phpdatabase');
   $con  = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

   // Check connection
   if (mysqli_connect_errno()) {
	  echo "Failed to connect to MySQL: " . mysqli_connect_errno();
	  exit();
    }
?>

Now our next step is that how to get weekly data from the database

So to get weekly data we have to execute select query which give below example.

In below example you will see the mysqli_query() function, the main use of this is to get connection and execute SELECT STATEMENT query.

If we get records from select after that we will number of record using mysqli_num_rows() function.

<?php
$sql = "SELECT * FROM employ WHERE WEEKOFYEAR(created_at)=WEEKOFYEAR(NOW()) AND YEAR(created_at) = YEAR(now())";
       if($result = mysqli_query($con, $sql)){
                        if(mysqli_num_rows($result) > 0){
                          // code here
                         }
         }
?>                                       

After execution of the select query as you can see in the below example we get the weekly data.

Weekly Data
Weekly Data

As you see in the above image is that we use DATE, TIME functions ( YEAR(), WEEKOFYEAR(), MySQL) .

NAMEDESCRIPTION
WEEK()Return the week number
WEEKDAY()Return the weekday index
WEEKOFYEAR()Return the calendar week of the date (1-53)
YEAR()Return the year
YEARWEEK()Return the year and week
DATE TIME FUNCTION

How to use AJAX to get weekly data in PHP

In below script you come to know how to use AJAX to get this.

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>contains demo</title>
</head>
<body>
<input class="title" type="text" name="title" id="title" placeholder="title">
<button class="btnSearch" name="btnSearch" id="btnSearch" >Get Week Data</button>
<script type="text/javascript">
$(document).ready(function() {
    //Add a JQuery click listener to our search button.
    $('#btnSearch').click(function(){
		var titlesearch = $('#titlesearch').val().trim();
		$.ajax({
            //The URL of the PHP file that searches MySQL.
            url: 'getdata.php',
			data: {
                title: title,
            },
            data: {
                titlesearch: titlesearch,
                location: location
            },
            success: function(returnData){
			 var results = JSON.parse(returnData);
			}
	}};
	});
</body>
</html>

In the above script we have done that on click of button, using Ajax in which passing data PHP file URL.

What you have to do in PHP file, follow the same process as do above steps.

First check with database connection, then after checking execute the select statement query

Important Note: Use DATE, TIME function in select query.

Then check with number record for weekly data.

If record is found then return the final result into single variable using json.

Finally using jQuery append the success result in HTML.

So Finally done with getting of weekly data using AJAX.

The main important thing in AJAX is passing of URL on click of button.

I hope you liked my this article. If you have any queries or any question regarding this, Feel free to comment on Me.