SQL query displaying same record multiple times with different array data

Question

I'm not sure what I am doing wrong but basically I'm trying to out put the days of the month through an array(done) but that dates that are within a booking date range from my trips table have a different appearance to them, this is how it currently looks

http://oi60.tinypic.com/2zzrc03.jpg

Where I am trying to output like this..

March 1[2 3 4 5]6[7 8 9 10]

The code is working for the most part but for months that have multiple events it's repeating the month and showing the days in their own month row

Here is my code, yes I am aware about it being mysql but currently this is a local project so I am working to get it functioning before I move to that step.

<table width="100%" cellspacing="0">
  <?php
    $cmonth = date('F'); 
    $cyear = date('Y');

    $sql = "SELECT * FROM calendar WHERE year = '$cyear' ORDER BY m_order ASC";
    $res = mysql_query($sql);
    while ($rows = mysql_fetch_array($res)) {
      $month_end = $rows['days_in_month'];
      $month_name = $rows['month_name'];
      $m_order = $rows['m_order'];

      $sql2 = "SELECT * FROM trips WHERE start_date LIKE '____-0$m_order-__' ORDER BY start_date ASC";
      $res2 = mysql_query($sql2);
      while ($row = mysql_fetch_array($res2)) {

        $stdate = $row['start_date'];
        $s = date_parse_from_format("Y-m-d", $stdate);

        $endate = $row['end_date'];
        $e = date_parse_from_format("Y-m-d", $endate);

        $start = $s['day'];
        $end = $e['day'];

  ?>
    <tr>
    <td width="80px"><?php echo $month_name; ?></td>
      <?php

        foreach(range(1, $month_end) as $days) 
        {
          if(in_array($days, range($start, $end)))
          {
            echo "<td style=\"background-color: #ccc;\" align=\"center\">".  $days ."    </td>";`
          }
          else
            echo "<td align=\"center\">". $days ."</td>"; 
        }
      ?>
    </tr>

    <?php } 
    } ?>
</table>

I'm just wondering if my coding is wrong or if I should be trying to accomplish my goals with an alternative method? any suggestions appreciated.

JCain · Accepted Answer · 2015-03-26 04:58:54Z

Through playing around and speaking with people I got it working, I removed the need for the calendar table and changed the array.

<?php 
function date_range($first, $last, $step = '+1 day', $output_format = 'Y-m-d' ) {

$dates = array();
$current = strtotime($first);
$last = strtotime($last);

while( $current <= $last ) {

    $dates[] = date($output_format, $current);
    $current = strtotime($step, $current);
}

return $dates;
}
?>
<table width="100%" cellspacing="0" cellpadding="0">
<?php
$cmonth = date('F'); 
$cyear = date('Y');

$sql = "SELECT * FROM trips WHERE year(start_date) = '$cyear' ORDER BY   start_date ASC";
$res = mysql_query($sql);
$array_days = array();
while ($rows = mysql_fetch_array($res)) {
$all_dates[] = array('start'=>strtotime($rows['start_date']),'end'=>strtotime($rows['end_date']));
$selected_dates = date_range(date("Y-m-d",strtotime($rows['start_date'])), date("Y-m-d",strtotime($rows['end_date'])));
foreach($selected_dates as $selected_date){

    $array_days[$selected_date] = date("d",strtotime($selected_date));
}

}
 $current_month = date("m");
 $next_6_month = date("m", strtotime("+5 month", strtotime(date("F") .  "1")));
    for($i=$current_month;$i<=$next_6_month;$i++){  // 12 months in year

 ?>
  <tr>
  <td width="40px"><?php echo date('M', mktime(0, 0, 0, $i,10, $cyear)); ?></td>
<?php 
$days_in_month = cal_days_in_month(CAL_GREGORIAN,$i,$cyear);

foreach(range(1, $days_in_month) as $days) 
{

 if(array_key_exists(date('Y-m-d', mktime(0, 0, 0, $i,$days, $cyear)),$array_days)){
    echo "<td style='text-align:center;background-color:ccc' width='12px'>$days</td>";
}else{
    echo "<td style='text-align:center;' width='12px'>$days</td>";
}
 }

 ?>
 </tr>
<?php } ?>


 </table>

DavidNeal · Accepted Answer · 2015-03-25 23:58:17Z

0

First off use prepared statements and mysqli because with your current method you could be sql injected!

Try using SELECT DISTINCT

http://www.w3schools.com/sql/sql_distinct.asp

answered Mar 25, 2015 at 23:58

DavidNeal

1

1 Comment

JCain Over a year ago

thanks for your reply, I'll have a read now, I'm just using sql while building offline, plan is to learn the conversation and do it before any site goes live.

Collectives™ on Stack Overflow

SQL query displaying same record multiple times with different array data

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related