CODEWITHSUNDEEP
phpmysql
mickburkejnr
mickburkejnr

Reputation: 3690

Work out date based on day name of database column and frequency in PHP

I have a table that shows collection dates for a client. In the table, there are seven columns and another column that gives the frequency of collection and a date when the collection schedule starts, like this:

|------------|------------|-------------|---------------|--------------|------------|--------------|------------|-----------|
|    start   | col_monday | col_tuesday | col_wednesday | col_thursday | col_friday | col_saturday | col_sunday | frequency |
|------------|------------|-------------|---------------|--------------|------------|--------------|------------|-----------|
| 2014-08-25 |     AM     |      AM     |       -       |      PM      |     PM     |       -      |     AM     |   Weekly  |

However now I want to display this in a list that the customer will see a bit like this:

Monday 25th August 2014 - AM Collection
Tuesday 26th August 2014 - AM Collection
Wednesday 27th August 2014 - No Collection
Thursday 28th August 2014 - PM Collection
Friday 29th August 2014 - PM Collection
Saturday 30th August 2014 - No Collection
Sunday 31st August 2014 - AM Collection

These would be further changed depending on whether it was a Weekly or Bi-weekly collection based on the start date. So if the collection record is biweekly, based on the start date, it would show no collections for the week beginning 1st September 2014.

The issue is further compounded because the system that the data is taken from is quite old and is cost prohibitive to change, meaning that the work has to be done after the data is created.

Any help would be fantastic.

Upvotes: 0

Views: 138

Answers (1)

Punitha Subramani
Punitha Subramani

Reputation: 1477

I will write in php, try like this

I just written the concept only.

$sqlRun=mysql_query('SELECT *,DAYNAME(start) as dayname from tblname');
while($row=mysql_fetch_array($sqlRun)){
$daynameConcat='col_'.$row['dayname'];
    if($row[$daynameConcat]==""){
     echo $row['start'].$row['dayname'].' No Collection ';
    } else {
     echo $row['start'].$row['dayname'].' Collection ';
    }
}

Upvotes: 1

Related Questions