CODEWITHSUNDEEP
phpmysql
user3697634
user3697634

Reputation:

taking two array and looping it with mysql query to fetch result

<?php 
//db connection goes here
$arr=array('12:30:00','01:30:01','02:30:01','03:30:01','04:30:01','05:30:01','06:30:01','07:30:01');
$arr1=array('01:30:00','02:30:00','03:30:00','04:30:00','05:30:00','06:30:00','07:30:00','08:30:00');

$cnt=count($arr);
for($i=0;$i<$cnt;$i++){
$sql="SELECT count(*) FROM report WHERE DATE_FORMAT(dt,'%H:%m:%i') BETWEEN  $arr[$i] AND $arr1[$i]";
}
//fetching in while and echo

this is my script that i am trying which will generate report counting number of user and number of application from two different table report and report1. the report will be like 

Time                  count Logged In user    Count-Apps

12:30:00-01:30:00
01:30:01-02:30:00
02:30:01 -03:30:00
03:30:01-04:30:00
04:30:01-05:30:00
05:30:01-06:30:00
06:30:01-07:30:00
07:30:01-08:30:00
08:30:01-09:30:00

report table for counting number of user

user    datetimeuser(datetime)
a       12:30:00
b       01:30:00
c       01:30:01
d       02:30:00

report1 table for counting number of apps

user    datetimeuser(datetime)
a       12:30:00
b       01:30:00
c       01:30:01
d       02:30:00

previously i have done a script which does the work but its slowing the server as my script will be placed in cron job firing in 1 hour interval and fetching the result

previous.php

$time_ranges = array(
    array('12:30:00','01:30:00'),
    array('01:30:01', '02:30:00'),
    array('02:30:01', '03:30:00'),
    array('03:30:01', '04:30:00'),
    array('04:30:01', '05:30:00'),
    array('05:30:01', '06:30:00'),
    array('06:30:01', '07:30:00'),
    array('07:30:01', '08:30:00'),
    array('08:30:01', '09:30:00'), 
);

$sql="SELECT sub0.TimeRange, sub0.number, COUNT(*) AS countapps
FROM
(
SELECT 
CASE
";

foreach ($time_ranges as $r) {
$sql .= "
    WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '$r[0]' and '$r[1]'
       THEN STR_TO_DATE(CONCAT(CURDATE(), ' ', '$r[0]'), '%Y-%m-%d %H:%i:%s') ";
}

$sql .= "
    ELSE NULL
END AS StartRange,
CASE ";

foreach ($time_ranges as $r) {
$sql .= "
    WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '$r[0]' and '$r[1]'
        THEN STR_TO_DATE(CONCAT(CURDATE(), ' ', '$r[1]'), '%Y-%m-%d %H:%i:%s') ";
}

$sql .= "
    ELSE NULL
END AS EndRange,
CASE ";

foreach ($time_ranges as $r) {
$sql .= "
    WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '$r[0]' and '$r[1]'
        THEN '$r[0]-$r[1]' ";
}

$sql .= "
    ELSE NULL
END AS TimeRange,
COUNT(*) as number
FROM report
WHERE DATE_FORMAT(dt,'%Y:%m:%d')=DATE(CURDATE())
GROUP BY StartRange, EndRange, TimeRange
HAVING TimeRange IS NOT NULL
) sub0
LEFT OUTER JOIN report1 
ON  report1.dt  BETWEEN sub0.StartRange AND sub0.EndRange
GROUP BY sub0.TimeRange, sub0.number";

$query=mysql_query($sql);
echo'<html>
<head>
<title>Count User Info TimeWise</title>
</head>
<h1>Count User</h1>
<table border="3" cellspacing="2">

<tr>
<th>range</th>
<th>count</th>
<th>Apps Count</th>';

while($row = mysql_fetch_array($query))
{
echo  "<tr>";
echo "<td>" . $row['TimeRange'] . "</td>";
echo "<td>" . $row['number'] . "</td>";
echo "<td>" . $row['countapps'] . "</td>";
echo "</tr>";
}
echo "</table>";      
echo "</html>";
?>

i want to make the mysql query shorter and more precise by taking only two array and looping it.but could not really make it.please help.how can i do this taking two array and then counting(array) and for loop it and count statement in mysql

$arr=array('12:30:00','01:30:01','02:30:01','03:30:01','04:30:01','05:30:01','06:30:01','07:30:01');

$arr1=array('01:30:00','02:30:00','03:30:00','04:30:00','05:30:00','06:30:00','07:30:00','08:30:00');

$cnt=count($arr);
for($i=0;$i<$cnt;$i++){

$sql="SELECT count(*) AS test FROM report WHERE DATE_FORMAT(dt,'%H:%m:%i') BETWEEN   $arr[$i] AND $arr1[$i]";

Upvotes: 0

Views: 74

Answers (1)

Kickstart
Kickstart

Reputation: 21523

Possibly dynamically build up a select to return the times and then join that against you report table:-

$numbers = array();
foreach($arr AS $key=>$value)
{
    $numbers[] = "SELECT '".$arr[$key]."' AS StartRange, '".$arr1[$key]."' AS EndRange ";
}

$dates_select = "(".implode(" UNION ",$numbers).") sub0";

$sql="SELECT sub0.StartRange, sub0.EndRange, count(report.dt) 
        FROM $dates_select 
        LEFT OUTER JOIN report 
        ON DATE_FORMAT(report.dt,'%H:%m:%i') BETWEEN  sub0.StartRange AND sub0.EndRange
        GROUP BY sub0.StartRange, sub0.EndRange";

Upvotes: 1

Related Questions