php code to match column date from database and display customer data

Question

Tablename - Receipt table

Data present in table like below.

 coupondate    customer      
 02-04-2015     A         
 02-05-2015     A         
 02-06-2015     A         
 02-07-2015     A        
 02-08-2015     A       
 02-09-2015     A  

 05-04-2015     B         
 05-05-2015     B         
 05-06-2015     B         
 05-07-2015     B        
 05-08-2015     B       
 05-09-2015     B

In above table customer A present from 02-04-2015 upto 02-09-2015 so i need to display p on customer A from 02-04 upto 02-09.

In above table customer B present from 05-04-2015 upto 05-09-2015 so i need to display p on customer B from 05-04 upto 05-09 like wise...

Expected output

Days  02-04 03-04 04-04 05-04 06-04 07-04 08-04 09-04 10-04 11-04 12-04 13-04 14-04 -15-04.....like wise upto 02-09
 A      P     P     P     P     P      P     P    P    P     P      P    .......like wise upto 15-09   
 B                       P      P      P     P    P    P     P      like wis....upto 05-09

below is my code...

<?php
if($_POST && isset($_POST['Submit']))
{       
        if($_POST['fromdate']!='' && $_POST['todate']!='')
        {
                $fromdate = $_POST['fromdate'];
                $todate = $_POST['todate'];
                $cityname = $_SESSION['Auth']['city'];
                $formdate_r = DateTime::createFromFormat('d-m-Y', $fromdate);
                $todate_r = DateTime::createFromFormat('d-m-Y', $todate);
                $formdate_sql = $formdate_r->format("Y-m-d");
                $todate_sql = $todate_r->format("Y-m-d");

                $data = $database->getRows("SELECT RE.* FROM receipt_entry RE LEFT JOIN city_master CM ON RE.city_name = CM.id WHERE CM.cityname = :cityname
                AND str_to_date(RE.coupondate,'%d-%m-%Y') BETWEEN :fromdate AND :todate ORDER BY STR_TO_DATE(RE.coupondate,'%d-%m-%Y') ASC",
                array(':fromdate'=>$formdate_sql,':todate'=>$todate_sql,':cityname'=>$cityname));

        }
}
?>


<?php
$startdate = $_POST['fromdate'];
$enddate = $_POST['todate'];
$start = date('d', strtotime($startdate));
$end=date('d', strtotime($enddate));
?>  
<?php   for ($x = $start; $x <= $end; $x++) { ?>
    <th width="58%"><?php echo $x; ?></th>
    <?php } ?>

below code is used to check coupondate with days..

this code works fine but only display single P on matched date but i need to display p against all days..

<?php  if (is_array($data)) { foreach($data as $row)    {   ?>
    <?php if($row['coupondate'] == $x) { ?>
            <td>P</td>
            <?php }  else { ?>
            <td>--</td>
            <?php } ?>  
<?php } }?>

Where do you fetch data from db into `$row`? Show all the code together. — Honza Haering, May 18 '15 at 13:15
The `foreach $data` cycle should be inside some cycle similar to the `for $x` cycle. Otherwise `$x` contains the last date only. — Honza Haering, May 18 '15 at 13:31
How about Pivot tables in mysql? http://stackoverflow.com/questions/18629155/pivot-table-in-mysql-convert-to-pivot-table-with-values-in-varchar-format — Arun, May 19 '15 at 07:17
Good job using a database wrapper with parameterized queries! :) — Chris Baker, May 20 '15 at 03:54

Bass Jobsen · Answer 1 · 2015-05-20T17:57:49.860

0

Your are matching if($row['coupondate'] == $x) where $row['coupondate'] is a dd-mm-YYYY string and $x is a number. So try:

if(date('d', strtotime($row['coupondate']) == $x)

Also the comment of @honza-haering makes sense.

edited May 20 '15 at 17:57

answered May 20 '15 at 17:48

Bass Jobsen

48,736
16
143
224

php code to match column date from database and display customer data

1 Answers1