Cant match date from my table with Date from Database.

imad_imike

Firstly, Heres my code,

 echo 'Do note that 2012-01-01 is in week ' . date('W', strtotime('2012-01-01')) . ' of "weekyear" ' . date('o');$start_date = '2011-04-06';$check_date = strtotime($start_date);$end_date = '2012-04-05';    

  echo '<table border="1" bordercolor="" width="100%" bgcolor="">		    

  <thead>		    

  <tr>			    

  <td>Start Date</td>				    

  <td>Month</td>				    

  <td>Week</td>				    

  <td>Pay in the week or month including statutory payments (SSP/SMP/OSPP/ASPP/SAP)</td>				    

  <td>Total pay to date</td>				    

  <td>Total "free pay" to date as shown by table A</td>				    

  <td>Total Additonal Pay to date as shown by table A</td>				    

  <td>Total Taxable pay to date</td>				    

  <td>Total Tax due</td>		      

  <td>Total Tax due at end of current period</td>    

  <td>Regulatory limit</td>    

  <td>Tax deducted or refunded</td>    

  <td>Tax not deducted</td>    

  </tr>	  	      

  </thead>    	      

  <tbody>';

   $sqlp = mysql_query("SELECT * FROM Payslips WHERE payslip_Employer='".  $_SESSION['UID'] . "' AND payslip_employee='" . $id . "' AND payslip_PayDay=' .  strtotime('+7 day', $check_date) . '");

    		if(mysql_num_rows($sqlp) > 0)

    			{

    				while($a = mysql_fetch_array($sqlp))

    				{

  $i = 0;while (date('Y-m-d', $check_date) <= $end_date && ++$i < 54){        

  $check_date = strtotime('+7 day', $check_date);

  printf('                     

  <tr>                  	    

  <td>%s</td>						    

  <td>%s</td>						    

  <td>%d</td>						    

  <td>' . $a['payslip_Gross'] . '</td>						    

  <td></td>						    

  <td></td>						    

  <td></td>						    

  <td></td>						    

  <td></td>	  

  <td></td>	    

  <td></td>	    

  <td></td>	    

  <td></td>	    

  </tr',                    

  date('d-m-Y', strtotime('-7 day', $check_date)),                  

  date('m', strtotime('-3 month', $check_date)),                  

  date('W', strtotime('-14 week', $check_date))        );} }}   

  echo '	</tbody></table>';

What I want to do Is display $a['payslip_Gross'] in the 4th column and I want going in the right row, so it matches up with the correct week in column 1.

I think the problem is in my query, AND payslip_PayDay=' . strtotime('+7 day', $check_date) . ',

I need to match payslip_PayDay which is in format dd/mm/yyyy, to the correct weekly row.

I'm not too good at explaining things so let me know if you need more information.

Thanks in advance

Michael

bradgrafelman

Why isn't payslip_PayDay a DATE column?

imad_imike

I'm working off a sql database which I didn't create myself, I'm just trying to get things working, so if you think thats the best solution, then I'll get this changed over, but if it can be done another way, other than altering the format of the column, then I'd prefer that.

bradgrafelman

Well one obvious problem I see is that this:

payslip_PayDay=' .  strtotime('+7 day', $check_date) . '

is what MySQL is receiving since that data is inside of the string, in other words PHP is not evaluating it as PHP code. Looks like you forgot to close and subsequently re-open the double quote delimited string (as was done for $id, although it wasn't necessary).

imad_imike

hmm, I added the double quotes in and tested this but still no luck.

But I found out that this is definately the problem. When its in the query, it removes all 53 rows of the table, but when I remove this from the query, It displays the correct number, but not in the correct row, it displays it in all 53 rows

bradgrafelman

Did you re-format the date to be in the same format as it is stored in the DB table?

imad_imike

Ok, I just tried reformatting the date and the whole thing flopped, heres what I got.

  <?php echo 'Do note that 01/01/2012 is in week ' . date('W', strtotime('01/01/2012')) . ' of "weekyear" ' . date('o');$start_date = '06/04/2011';$check_date = strtotime($start_date);$end_date = '05/04/2012';    

  echo '<table border="1" bordercolor="" width="100%" bgcolor="">		    

  <thead>		    

  <tr>			    

  <td>Start Date</td>				    

  <td>Month</td>				    

  <td>Week</td>				    

  <td>Pay in the week or month including statutory payments (SSP/SMP/OSPP/ASPP/SAP)</td>				    

  <td>Total pay to date</td>				    

  <td>Total "free pay" to date as shown by table A</td>				    

  <td>Total Additonal Pay to date as shown by table A</td>				    

  <td>Total Taxable pay to date</td>				    

  <td>Total Tax due</td>		      

  <td>Total Tax due at end of current period</td>    

  <td>Regulatory limit</td>    

  <td>Tax deducted or refunded</td>    

  <td>Tax not deducted</td>    

  </tr>	  	      

  </thead>    	      

  <tbody>';

   $sqlp = mysql_query("SELECT * FROM Payslips WHERE payslip_Employer='".  $_SESSION['UID'] . "' AND payslip_employee='" . $id . "'");

    		if(mysql_num_rows($sqlp) > 0)

    			{

    				while($a = mysql_fetch_array($sqlp))

    				{

  $i = 0;while (date('d/m/Y', $check_date) <= $end_date && ++$i < 54){        

  $check_date = strtotime('+7 day', $check_date);

  printf('                     

  <tr>                  	    

  <td>%s</td>						    

  <td>%s</td>						    

  <td>%d</td>						    

  <td></td>						    

  <td></td>						    

  <td></td>						    

  <td></td>						    

  <td></td>						    

  <td></td>	  

  <td></td>	    

  <td></td>	    

  <td></td>	    

  <td></td>	    

  </tr',                    

  date('d/m/Y', strtotime('-7 day', $check_date)),                  

  date('m', strtotime('-3 month', $check_date)),                  

  date('W', strtotime('-14 week', $check_date))        );} }}   

  echo '	</tbody></table>'; ?>

I removed the entry from the table because I now need to just get the dates working in the correct format first.

The format they are in MySQL database is dd/mm/yyyy

bradgrafelman

What did you try that didn't work?

imad_imike

I changed the dates from Y-m-d to d/m/Y. Well I tried to, but thats what went wrong.

I take it this is the inproper way to reformat the date.

bradgrafelman

How did you try to do that, though? The code snippet you posted above doesn't do anything with the payslip_PayDay column anymore.

imad_imike

Sorry I took a step back, I removed that because when I tried to change the date format, I can't get it to change to d/m/y.

I tried to do that on the first line of the snippet, not sure if its possible doing it this way though.