How to count records by date according to the requested query?

Question

How to count records by date according to the requested query?

Navigation

#1 by (1 votes)

0

I am looking for the counting of these services according to the date, these types of services are stored in a column.

index.php

<!DOCTYPE html>
<html lang = "es">
    <head>
        <title>Consulta De Estadisticas</title>
        <link rel = "stylesheet" type = "text/css" href = "css/bootstrap.css"/>
        <link rel = "stylesheet" type = "text/css" href = "css/jquery-ui.css"/>
        <meta charset = "UTF-8" name = "viewport" content = "width=device-width, initial-scale=1"/>
        <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    </head>
<body>
    <nav class = "navbar navbar-inverse">
        <div class = "container-fluid">
            <a href = "" class = "navbar-brand">Estadisticas Aerosanidad</a>
        </div>
    </nav>
    <div class = "row-fluid">
        <div class = "col-md-3"></div>
        <div class = "col-md-6">
        <div class="panel panel-default">
            <div class="panel-body">
                <h3 class = "text-primary">Búsqueda De Servicios</h3>
                <hr style = "border-top:1px dotted #000;"/>
                <div class = "form-inline">
                <label>Desde:</label>
                <input type = "text" class = "form-control" placeholder = "Inicio"  id = "date1"/>
                <label>Hasta</label>
                <input type = "text" class = "form-control" placeholder = "Final"  id = "date2"/>
                <button type = "button" class = "btn btn-primary" id = "btn_search" onclick="load();"><span class = "glyphicon glyphicon-search"></span></button> <button type = "button" id = "reset" class = "btn btn-success"><span class = "glyphicon glyphicon-refresh"><span></button>
            </div>
            <br /><br />
            <div class = "table-responsive">    
                <table class = "table table-bordered alert-warning">
                    <thead>
                        <tr>
                            <th style = "width:25%;">Servicios Solicitados</th>
                            <th style = "width:30%;">Servicios Finalizados</th>
                            <th>Servicios Fallidos</th>
                            <th style = "width:20%;">Servicios Cancelados</th>
                            <th style = "width:20%;">Servicios Realizando</th>
                        </tr>
                        </tr>
                    </thead>
                    <tbody id = "load_data">

                    </tbody>
                </table>
            </div>  

              </div>
        </div>
            <input type="submit" name="volver" value="Herramienta" class="submit" onclick="location='http://localhost/Aeroasistencia/Estadisticas/index.php'">
        </div>
    </div>
</body>
<script src = "js/jquery-3.1.1.js"></script>
<script src = "js/jquery-ui.js"></script>
<script src = "js/ajax.js"></script>
</html>

    ajax_data.php
    <?php
$date1 = date("Y-m-d", strtotime($_POST['date1']));
$date2 = date("Y-m-d", strtotime($_POST['date2']));

if (!empty($_POST['date1']) and  !empty($_POST['date1'])){
    list($dia,$mes,$anio)=explode("/",$_POST['date1']);
    $date1="$anio-$mes-$dia";
    list($dia,$mes,$anio)=explode("/",$_POST['date2']);
    $date2="$anio-$mes-$dia";

    $sWhere="WHERE 'fecha_Solicitud' BETWEEN '$date1' AND '$date2'";

} else {
    $sWhere=""; 
}

#Conectare a la base de datos

include("conexion.php");

$q_book = $conn->query("SELECT COUNT(servicio_No) AS totalServicios FROM 'servicio' $sWhere") or die(mysqli_error("Error al conectar a la base de datos"));
$v_book = $q_book->num_rows;
$totalServicios = $v_book['totalServicios']; //Este es el valor que acabas de calcular en la consulta
if($v_book > 0){
    while($f_book = $q_book->fetch_array()){
    ?>
    <tr>
         <td><?php echo $f_book['totalServicios']; ?></td>
    </tr>
    <?php
    }
  }
?>

mysql php ajax

asked by Cristian Antonio Trujillo Gris 05.07.2018 в 18:01

source

1 answer

Google charts, I can not use variables in javascript Read text block of a file

score 1 · Accepted Answer

Just use GROUP BY in your MySQL query

<?php

$ date1 = date ("Y-m-d", strtotime ($ _ POST ['date1']))); $ date2 = date ("Y-m-d", strtotime ($ _ POST ['date2']));

if (! empty ($ _ POST ['date1'])) and! empty ($ _ POST ['date2'])) { list ($ day, $ month, $ year) = explode ("/", $ _ POST ['date1']); $ date1="$ anio- $ month- $ day"; list ($ day, $ month, $ year) = explode ("/", $ _ POST ['date2']); $ date2="$ anio- $ month- $ day";

$sWhere="WHERE 'fecha_Solicitud' BETWEEN '$date1' AND '$date2'";

} else { $ sWhere=""; }

I will connect to the database

include ("conexion.php");

// you add GROUP BY indicating the column by which to group $ q_book = $ conn-> query ("SELECT fecha_Solicitud , COUNT (service_No) AS totalServices FROM servicio $ sWhere GROUP BY fecha_Solicitud ") or die (mysqli_error ("Error connecting to the database" )); $ v_book = $ q_book- > num_rows; if ($ v_book > 0) { while ($ f_book = $ q_book- > fetch_array ()) { ? >

to obtain the date related to each service count you use $ f_book ['date_Request']