You are here : / / / / MySQL Data Counting with PHP Script

Apr 7, 2018

MySQL Data Counting with PHP Script

MySQL Data Counting with PHP Script

 MySQL Data Counting with PHP Script. In this tutorial, we will calculate the data in a database. Some calculation process used is to find the number of values in a field or count the number of rows from the query result. To run this process, also used some functions that is MySQL COUNT() and SUM().

1. Create Database
Create a new database for example abc_database . You can create it using phpMyAdmin, SQLyog, or similar applications.
MySQL Table Data Counting with PHP Script

Then create a table users and orders with columns like the picture below and fill the table data. status_order field in the orders table is filled with the numbers 0 or 1. 0 it means new orders and 1 are the complete orders
MySQL Table Data Counting with PHP Script

MySQL Table Data Counting with PHP Script

MySQL Table Data Counting with PHP Script

MySQL Table Data Counting with PHP Script

Or you can use other options to create this database with import sql file. sql databse file can be downloaded in this LINK.

2. View data with PHP-HTML Code (index.php)
You can create this page with standart html code or you can use the Bootstrap Admin Template. The main part in this file is contained in the php script used for the database connection and the process to calculate the data.
connection to database
<?php
session_start();
// db connection-----------------------------
mysql_connect('localhost', 'root', '');//'hostname','username','password'
mysql_select_db('abc_database');

?>

count new users
<div class="mr-5">
 <?php
 $sql = "SELECT count(*) AS data1 FROM users";
 $query = mysql_query($sql);
 $result = mysql_fetch_array($query);
 echo $result['data1'];
 ?> Total Users
</div>

calculate the amount of revenue from the complete order (status_order=1)
<div class="mr-5">
 <?php
 $sql = "SELECT SUM(total_price) AS data2 FROM orders WHERE status_order=1";
 $query = mysql_query($sql);
 $result = mysql_fetch_array($query);
 echo $result['data2'];
 ?> USD Earnings
</div>

count new orders (status_order=0)
<div class="mr-5">
 <?php
 $sql = "SELECT count(*) AS data3 FROM orders WHERE status_order=0";
 $query = mysql_query($sql);
 $result = mysql_fetch_array($query);
 echo $result['data3'];
 ?> New Orders!
</div>

count complete orders (status_order=1)
<div class="mr-5">
 <?php
 $sql = "SELECT count(*) AS data4 FROM orders WHERE status_order=1";
 $query = mysql_query($sql);
 $result = mysql_fetch_array($query);
 echo $result['data4'];
 ?> Complete!
</div>

Complete Script (index.php)
<?php
session_start();
// db connection-----------------------------
mysql_connect('localhost', 'root', '');//'hostname','username','password'
mysql_select_db('abc_database');

?>

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">
  <title>SB Admin - Start Bootstrap Template</title>
  <!-- Bootstrap core CSS-->
  <link href="vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
  <!-- Custom fonts for this template-->
  <link href="vendor/font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">
  <!-- Page level plugin CSS-->
  <link href="vendor/datatables/dataTables.bootstrap4.css" rel="stylesheet">
  <!-- Custom styles for this template-->
  <link href="css/sb-admin.css" rel="stylesheet">
</head>

<body class="fixed-nav sticky-footer bg-dark" id="page-top">
  <!-- Navigation-->
  <nav class="navbar navbar-expand-lg navbar-dark bg-dark fixed-top" id="mainNav">
    <a class="navbar-brand" href="index.html">Cheese Store</a>
    <button class="navbar-toggler navbar-toggler-right" type="button" data-toggle="collapse" data-target="#navbarResponsive" aria-controls="navbarResponsive" aria-expanded="false" aria-label="Toggle navigation">
      <span class="navbar-toggler-icon"></span>
    </button>
    <div class="collapse navbar-collapse" id="navbarResponsive">
      <ul class="navbar-nav navbar-sidenav" id="exampleAccordion">
        <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Dashboard">
          <a class="nav-link" href="index.html">
            <i class="fa fa-fw fa-dashboard"></i>
            <span class="nav-link-text">Dashboard</span>
          </a>
        </li>
        <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
          <a class="nav-link" href="charts.html">
            <i class="fa fa-fw fa-area-chart"></i>
            <span class="nav-link-text">Charts</span>
          </a>
        </li>
        <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Tables">
          <a class="nav-link" href="tables.html">
            <i class="fa fa-fw fa-table"></i>
            <span class="nav-link-text">Tables</span>
          </a>
        </li>
        <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Example Pages">
          <a class="nav-link nav-link-collapse collapsed" data-toggle="collapse" href="#collapseExamplePages" data-parent="#exampleAccordion">
            <i class="fa fa-fw fa-file"></i>
            <span class="nav-link-text">Example Pages</span>
          </a>
          <ul class="sidenav-second-level collapse" id="collapseExamplePages">
            <li>
              <a href="#">Login Page</a>
            </li>
            <li>
              <a href="#">Registration Page</a>
            </li>
            <li>
              <a href="#">Forgot Password Page</a>
            </li>
          </ul>
        </li>
      </ul>
      <ul class="navbar-nav sidenav-toggler">
        <li class="nav-item">
          <a class="nav-link text-center" id="sidenavToggler">
            <i class="fa fa-fw fa-angle-left"></i>
          </a>
        </li>
      </ul>
      <ul class="navbar-nav ml-auto">
        <li class="nav-item">
          <form class="form-inline my-2 my-lg-0 mr-lg-2">
            <div class="input-group">
              <input class="form-control" type="text" placeholder="Search for...">
              <span class="input-group-append">
                <button class="btn btn-primary" type="button">
                  <i class="fa fa-search"></i>
                </button>
              </span>
            </div>
          </form>
        </li>
        <li class="nav-item">
          <a class="nav-link" data-toggle="modal" data-target="#exampleModal">
            <i class="fa fa-fw fa-sign-out"></i>Logout</a>
        </li>
      </ul>
    </div>
  </nav>
  <div class="content-wrapper">
    <div class="container-fluid">
      <!-- Breadcrumbs-->
      <ol class="breadcrumb">
        <li class="breadcrumb-item">
          <a href="#">Dashboard</a>
        </li>
        <li class="breadcrumb-item active">My Dashboard</li>
      </ol>
      <!-- Icon Cards-->
      <div class="row">
        <div class="col-xl-3 col-sm-6 mb-3">
          <div class="card text-white bg-primary o-hidden h-100">
            <div class="card-body">
              <div class="card-body-icon">
                <i class="fa fa-fw fa-users"></i>
              </div>
              <div class="mr-5">
  <?php
  $sql = "SELECT count(*) AS data1 FROM users";
  $query = mysql_query($sql);
  $result = mysql_fetch_array($query);
  echo $result['data1'];
  ?> Total Users
       </div>
            </div>
            <a class="card-footer text-white clearfix small z-1" href="#">
              <span class="float-left">View Details</span>
              <span class="float-right">
                <i class="fa fa-angle-right"></i>
              </span>
            </a>
          </div>
        </div>
        <div class="col-xl-3 col-sm-6 mb-3">
          <div class="card text-white bg-warning o-hidden h-100">
            <div class="card-body">
              <div class="card-body-icon">
                <i class="fa fa-fw fa-dollar"></i>
              </div>
              <div class="mr-5">
  <?php
  $sql = "SELECT SUM(total_price) AS data2 FROM orders WHERE status_order=1";
  $query = mysql_query($sql);
  $result = mysql_fetch_array($query);
  echo $result['data2'];
  ?> USD Earnings
       </div>
            </div>
            <a class="card-footer text-white clearfix small z-1" href="#">
              <span class="float-left">View Details</span>
              <span class="float-right">
                <i class="fa fa-angle-right"></i>
              </span>
            </a>
          </div>
        </div>
        <div class="col-xl-3 col-sm-6 mb-3">
          <div class="card text-white bg-success o-hidden h-100">
            <div class="card-body">
              <div class="card-body-icon">
                <i class="fa fa-fw fa-shopping-cart"></i>
              </div>
              <div class="mr-5">
  <?php
  $sql = "SELECT count(*) AS data3 FROM orders WHERE status_order=0";
  $query = mysql_query($sql);
  $result = mysql_fetch_array($query);
  echo $result['data3'];
  ?> New Orders!
       </div>
            </div>
            <a class="card-footer text-white clearfix small z-1" href="#">
              <span class="float-left">View Details</span>
              <span class="float-right">
                <i class="fa fa-angle-right"></i>
              </span>
            </a>
          </div>
        </div>
        <div class="col-xl-3 col-sm-6 mb-3">
          <div class="card text-white bg-danger o-hidden h-100">
            <div class="card-body">
              <div class="card-body-icon">
                <i class="fa fa-fw fa-check"></i>
              </div>
              <div class="mr-5">
  <?php
  $sql = "SELECT count(*) AS data4 FROM orders WHERE status_order=1";
  $query = mysql_query($sql);
  $result = mysql_fetch_array($query);
  echo $result['data4'];
  ?> Complete!
       </div>
            </div>
            <a class="card-footer text-white clearfix small z-1" href="#">
              <span class="float-left">View Details</span>
              <span class="float-right">
                <i class="fa fa-angle-right"></i>
              </span>
            </a>
          </div>
        </div>
      </div>
      <!-- Area Chart Example-->
      <div class="card mb-3">
        <div class="card-header">
          <i class="fa fa-area-chart"></i> Area Chart Example</div>
        <div class="card-body">
          <canvas id="myAreaChart" width="100%" height="30"></canvas>
        </div>
        <div class="card-footer small text-muted">Updated yesterday at 11:59 PM</div>
      </div>
    </div>
    <!-- /.container-fluid-->
    <!-- /.content-wrapper-->
    <footer class="sticky-footer">
      <div class="container">
        <div class="text-center">
          <small>Copyright © Your Website 2018</small>
        </div>
      </div>
    </footer>
    <!-- Scroll to Top Button-->
    <a class="scroll-to-top rounded" href="#page-top">
      <i class="fa fa-angle-up"></i>
    </a>
    <!-- Logout Modal-->
    <div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
      <div class="modal-dialog" role="document">
        <div class="modal-content">
          <div class="modal-header">
            <h5 class="modal-title" id="exampleModalLabel">Ready to Leave?</h5>
            <button class="close" type="button" data-dismiss="modal" aria-label="Close">
              <span aria-hidden="true">×</span>
            </button>
          </div>
          <div class="modal-body">Select "Logout" below if you are ready to end your current session.</div>
          <div class="modal-footer">
            <button class="btn btn-secondary" type="button" data-dismiss="modal">Cancel</button>
            <a class="btn btn-primary" href="login.html">Logout</a>
          </div>
        </div>
      </div>
    </div>
    <!-- Bootstrap core JavaScript-->
    <script src="vendor/jquery/jquery.min.js"></script>
    <script src="vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
    <!-- Core plugin JavaScript-->
    <script src="vendor/jquery-easing/jquery.easing.min.js"></script>
    <!-- Page level plugin JavaScript-->
    <script src="vendor/chart.js/Chart.min.js"></script>
    <script src="vendor/datatables/jquery.dataTables.js"></script>
    <script src="vendor/datatables/dataTables.bootstrap4.js"></script>
    <!-- Custom scripts for all pages-->
    <script src="js/sb-admin.min.js"></script>
    <!-- Custom scripts for this page-->
    <script src="js/sb-admin-datatables.min.js"></script>
    <script src="js/sb-admin-charts.min.js"></script>
  </div>
</body>

</html>

MySQL Table Data Counting with PHP Script

 Okay.., That's all for the tutorial MySQL Table Data Counting with PHP Script, complete code (and file) can be downloaded at the download link bellow. Hope you like it and Enjoy...


Share this post

Related Post

MySQL Data Counting with PHP Script
4/ 5
Oleh
Komentar

Subscribe via email

Like the above article? Add your email to subscribe.

Short Code, use <i rel="code"> ... the code ... </i>
long code, use <i rel="pre"> ... the code ... </i>
insert image, use <i rel="image"> ... image source(URL) ... </i>

Subscribe to: Post Comments (Atom)