Working with Dates

[Xtremefaith]

Having issues working with dates in PHP, very new aspect of PHP for me. Couple things I'm trying to accomplish and wouldn't mind a simple tutorial/explanation of since everything I'm finding assumes I know more than I do:

1. How to sort by dates?
Currently I am trying to query log of information for bills and payments, ordered by their due date. I want to group this query by months, so as of right now I'm using this:

$sql = "select * from $table WHERE MONTH(date) between '11' and '12'";
$query = mysql_query($sql);

Then I display the query as needed, then close the sql, then reopen and use another query for the previous month; which I'm not sure how to do just yet. For some reason this currently pulls some of Nov and Dec, not just Nov or Dec like I want.

2. How can this be dynamic
This whole project I expect to be dynamic so I don't want to hardcode dates, but rather I want it to use today's date (which I think is the today() function) to determine $this_month, and the descend to the previous month, possible as far as 1 year back at the most.

Is it possible to do something like this in a loop? I am new to PHP but I learn quickly when things are explained well for me to grasp and I've programmed in other languages, AS2 (Flash) is my primary language.

Hope that helps you help me, thanks in advance for anything you guys can offer, means a lot, already you guys have helped me grow leaps and bounds on this project.

[rch1231]

I think you will find that date is a reserved word and you need to change the field name.

[mschroeder]

What you're asking is more of a MySQL (I'm assuming) question than directly related to php.

MySQL has a ton of date handling capabilities. Is the date column, which is not a reserved keyword (http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html), setup to be a Datetime, Date, Timestamp or Integer (unix timestamp) field?

[Xtremefaith]

Under type is says 'date'. I wonder if I change it to anything else how it will effect things. ultimately even if it effects my scripts, as long as I can still accomplish everything I need by changing the type then I should be ok.

Does that help?

[mschroeder]

The current type shouldn't be an issue, I just wanted to make sure it was actually a date column type and not a varchar or unix timestamp stored as an integer.

When I look at that query and run one of a similar structure on my localhost I do get the expected results. I get all of 11 and 12. If you want only November or only December than update the query so it reflects WHERE MONTH(date) = 11

The other option you have is to use IN which can accept 1+ comma delimited values.

Gets all of november AND december

SELECT * FROM table WHERE MONTH(date) IN(11, 12)

Gets only Novemeber

SELECT * FROM table WHERE MONTH(date) IN(11)

Gets only odd months

SELECT * FROM table WHERE MONTH(date) IN(1, 3, 5, 7, 9, 11)

You can use the standard = operator, but IN provides you with some more flexibility to reuse the query for other purposes.

[Xtremefaith]

Ok so this is working great and I even accomplished getting this to work in a loop to accomplish everything I wanted to do. Now I have to other questions about using dates.

1. I want certain rows to be greyed out if the paid date exist (basically), the problem is the default value for that field is always '1999-11-30'. I'm not sure why. I've even tried to update the structure of that field/column to make the default NULL. For some reason mySQL won't let me.

2. Once that is fixed it should make my code work I think. Here's the code:

if(strtotime($row['paid']) < time()){
   echo '<tr id="paid">';
} else {
   echo '<tr id="data">';
}

Currently my function always appears TRUE but I think that's just because the are false values in the empty fields. Plus this function is based on today's date, but honestly if there is a value at all then it should be TRUE, doesn't matter today's date. Does that make sense?

[mschroeder]

Did you set the default value for that field to 1999-11-30?

When I create a table and set the default value for a DATE type column to be NULL I have no issues inserting a NULL value into the column. Even if you inserted incorrect data into the column, like a string or an integer it should default to 0000-00-00.

Putting a switch like this on a value is actually something that could be moved to your query.

e.g.

SELECT IF(dates.date < CURDATE(), 'paid', 'data') AS row_id FROM dates

In mysql NULL < CURDATE() will evaluate to false. 0000-00-00 < CURDATE() will evaluate to true.

In PHP:
strtotime(NULL) evaluates to false, making your statement if( false < time() ) which evaluates to true.

strtotime('0000-00-00') also evaluates to false so it follows the same as above.

strtotime('1999-11-30') evaluates to '943920000', making your statement if( 943920000 < time() ) evaluate to true.

So as you see any of those conditions will actually evaluate to true.

[Xtremefaith]

Ok figured out how to get it to let me set field to NULL. Now I'm wondering why I can't just use this as my function:

if($row['paid']) = "0000-00-00)"{
	echo '<tr id="paid">';
} else {
	echo '<tr id="data">';
}

I checked the output of that index and it seems like it would be correct, I keep getting an error saying it won't accept my operators, I've tried =,==.>=, and none of them are acceptable, not sure why.
Getting errors like:
Parse error: syntax error, unexpected T_IS_EQUAL in .../bTracker.php on line 102
Parse error: syntax error, unexpected T_IS_GREATER_OR_EQUAL in .../bTracker.php on line 102

Something I'm missing here? Thanks for all the help by the way I'm getting some great breakthroughs, so thank you.

[mschroeder]

your if statement has an extra ) after $row if($row['paid']) = "0000-00-00)"{ change to if($row['paid'] == "0000-00-00)"{

[Xtremefaith]

Good news is the concept works now! It was still displaying the Nov-30-1999, but I figured out it was my date reformat function that was messing it up. I created an if statement that checked for "0000-00-00" and that way it would not explode the array, hence causing some display issues. This is looking great now, when its all said and done I'll have to show you so you can see it.

Now in my loop that creates the tables for each month I need it to group by year too so that it displays correctly. The easiest way to describe this is probably to just let you see it all, so here's the link: http://sandbox.nickworth.com/iBudget/bTracker.php
And here's the code:

$m = 12;
while($m > 0){
	$month =  date("F", mktime(0, 0, 0, $m)); 
	$monthNum =  date("n", mktime(0, 0, 0, $m)); 

	$sql = "select * from $table WHERE MONTH(Date) IN('$monthNum')";
	$query = mysql_query($sql);
	if(mysql_num_rows($query) > 0){
		echo "<table>";
		echo "<td colspan='9'><h2>".$month."</h2></td>";
		include ("getHeaders.php");
				
				// Add new entry row
				echo "<form action='bTracker.php' method='post'>";
				echo "<td><input id='dueDate' name='dueDate' size='10' value='YYYY/MM/DD'></td>";
				echo "<td nowrap='nowrap'>$<input type='text' name='minDue' size='6'</td>";
				echo "<td nowrap='nowrap'>$<input type='text' name='payment' size='6'</td>";
				
		////////// Code for VENDORS pulldown menu /////////////////////////////////////////////////////////////
				echo "<td>";
				echo "<select name='payees'>";
				$payeeSQL = "SELECT * FROM `vendors` ORDER BY `Company/Name` ASC";
				$payeeQuery = mysql_query($payeeSQL);
				if( mysql_num_rows($payeeQuery) > 0)
				{
					$payeeRow=mysql_fetch_assoc($payeeQuery); 
					do{ ?>
						<option value="<? echo $payeeRow['Code'] ?>"><? echo $payeeRow['Code'] ?></option>
					<? }while($payeeRow=mysql_fetch_assoc($payeeQuery));
					echo '</select>';
				}
				
		/////////////////////////////////////////////////////////////////////////////////////////////////////
		//////////Code for ACCOUNTS pulldown menu ///////////////////////////////////////////////////////////
				echo "<td>";
				echo "<select name='accounts'>";
				$accountSQL = "SELECT * FROM `accounts` ORDER BY `name` ASC";
				$accountQuery = mysql_query($accountSQL);
				if( mysql_num_rows($accountQuery) > 0)
				{
					$accountRow=mysql_fetch_assoc($accountQuery); 
					do{ ?>
						<option value="<? echo $accountRow['code'] ?>"><? echo $accountRow['code'] ?></option>
					<? }while($accountRow=mysql_fetch_assoc($accountQuery));
					echo '</select>';
				}
		/////////////////////////////////////////////////////////////////////////////////////////////////////
				?> 
				<script type="text/javascript">
					$(function(){$("#dueDate").datepicker({ dateFormat: 'yy-mm-dd' });});
					$(function(){$("#billDate").datepicker({ dateFormat: 'yy-mm-dd' });});
					$(function(){$("#sentDate").datepicker({ dateFormat: 'yy-mm-dd' });});
					$(function(){$("#paidDate").datepicker({ dateFormat: 'yy-mm-dd' });});
				</script>
				<?
				echo "<td><input id='billDate' name='billDate' size='10'></td>";
				echo "<td><input id='sentDate' name='sentDate' size='10'></td>"; ?>
				<? echo "<td><input id='paidDate' name='paidDate' size='10'></td>";
				echo "<td><input type='text' name='notes' size='35'></td>";
				echo "<td style='border:none'><input type='Submit' name='addBill' value='Add Bill'></td>";
				echo "</tr>";
			echo "</form>";
		
		// This section is pulling the data from the db and populating the 
		//table accordingly
		if( mysql_num_rows($query) > 0)
		{
			$row=mysql_fetch_assoc($query);
		
			do{
				//save the value of id
				$id=$row['ID'];
				
				//"erase" ID from $row
				unset($row['ID']);
	
				if($row['paid'] == "0000-00-00"){
					echo '<tr id="data">';
				} else {
					echo '<tr id="paid">';
				}
				
				echo '<td>'.dateconverter($row["Date"]).'</td>';
				echo '<td>$'.$row["Minimum Due"].'</td>';
				echo '<td>$'.$row["Payment"].'</td>';
				echo '<td<b>'.$row["Payee"].'</b></td>';
				echo '<td>'.$row["Account"].'</td>';
				echo '<td>'.dateconverter($row["billed"]).'</td>';
				echo '<td>'.dateconverter($row["sent"]).'</td>';
				echo '<td>'.dateconverter($row["paid"]).'</td>';
				echo '<td style="background:#FFC;"><i>'.$row["Notes"].'</i></td>';
				//implode will "join" all the $row elements using '</td><td>' as the 'glue'
				echo '<td style="border: medium none;"><a href="editBill.php?id='.$id.'">[Edit]</a></td>';
				echo '<td style="border: medium none;"><a href="delete.php?id='.$id.'&table='.$table.'">[X]</a></td></tr>';
			}while($row=mysql_fetch_assoc($query));
			echo '</table>';
		} 
		else
		{
			echo '<div id="error">No Records found</div>';
		}
		
	}
	
$m--;
}

So essentially it should say in the header of each table, "December of 2010", "January of 2010", etc. Then the table below should filter that specific month. I'm not sure if the top should be the most recent (Jan of 2011), or the current month (Dec of 2010). I think it should be based on the furthest date out found in the DB, not sure how I would do that though. Does that make sense?