CODEWITHSUNDEEP
phpmysqlcount
Carlvic Japitana Lim
Carlvic Japitana Lim

Reputation: 149

Get count occurrence with the date filtering

Hello I have this table

table name : addresses


|  id  |branch |      datetime      |
____________________________________

|16875 |north  | 2015-07-13 02:11:34|
|16345 |north  | 2015-07-11 08:04:42|
|16000 |north  |2015-07-10 08:16:07 |
|16960 |north  |2015-07-13 05:16:04 |
|15909 |north  |2015-07-10 05:16:05 |
|16669 |south  |2015-07-12 07:16:03 |
|16513 |south  |2015-07-12 00:20:43 |
|16699 |south  |2015-07-12 08:16:02 |
|15939 |east   |2015-07-10 06:16:05 |
|16449 |east   |2015-07-11 11:16:04 |
|16517 |east   |2015-07-12 01:16:01 |
|16729 |east   |2015-07-12 09:16:02 |
|16418 |west   |2015-07-11 10:18:16 |
|15971 |west   |2015-07-10 07:16:04 |
|16785 |west   |2015-07-12 11:16:01 |
|16757 |west   |2015-07-12 10:16:02 |
|16353 |west   |2015-07-11 08:16:04 |
|16877 |west   |2015-07-13 02:16:03 |

On my php page i want to query this table but only show the count of the duplicate values since i have datetime column I want to pick date and show the duplicate value on that date.

for example is I pick date

2015-07-13

So I it will query 

|Branch|count|
______________
|north |  2  |
|west  |  1  |
______________

another example is I want to pick date is

2015-07-10 It will query

|Branch|count|
______________
|north |  2  |
|east  |  1  |
|west  |  1  |
______________

on my php page I use this code

   <table cellpadding="0" cellspacing="0" border="0" id="table">
        <thead>
            <tr>
                <th><h3>Branch</h3></th>
                <th><h3>Count</h3></th>



            </tr>
        </thead>
        <tbody>
            <?php
// Connect to database server
mysql_connect("localhost", "user", "user") or die (mysql_error ());

// Select database
mysql_select_db("data") or die(mysql_error());

// SQL query
$strSQL  = "SELECT branch,count(branch) as occurence FROM `addresses` WHERE datetime >= \"2015-07-10\" AND datetime < \"2015-07-11\" group by branch";

// Execute the query (the recordset $rs contains the result)
$rs = mysql_query($strSQL);

// Loop the recordset $rs
while($row = mysql_fetch_array($rs)) {

   // W
    echo"<tr>";

    echo"<td>". $row['branch'];
    echo"<td>". $row['occurence'];





  }

// Close the database connection
mysql_close();
                    ?>

and my output is

|Branch|count|
______________
|north |  2  |
|east  |  1  |
|west  |  1  |
______________

So how can I integrate the date filtering on my php page? that count the duplicate values.

Thank You

Upvotes: 0

Views: 78

Answers (1)

dialogik
dialogik

Reputation: 9552

You can pass the from and to date as parameters to the query:

// Connect to database
$dbh = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');

// Prepare statement
$stmt = $dbh->prepare("SELECT branch, count(branch) AS occurence
                        FROM `addresses`
                        WHERE datetime >= :from AND datetime < :to
                        GROUP BY branch");

// Bind parameters
$stmt->bindParam(':from', $from);
$stmt->bindParam(':to',   $to);

// Execute
$from = '2015-07-10';
$to   = '2015-07-11';
$stmt->execute();

// Fetch result
$result = $stmt->fetchAll();

See the PHP documentation on Prepared Statements for more information.

Upvotes: 1

Related Questions