[RESOLVED] Date manipulations

[phattymatty]

I am designing a softball team website. The team typically plays every Thursday but sometime on a different night for tournaments etc. I would like to create a function or formula which returns who the next opponent is and on what day / date. I have the team data stored in the MYSQL table called schedule and the following var's assigned: id week date day time diamond vs result

The current date is stored in 'date' using the formula :

$offset = $row['offset'];//this is stored in mysql
$hm = $offset * 60;
$ms = $hm * 60;
$date = gmdate("M d, Y  g:i a", time()-($ms)); 

So I guess my question would be how do I take the current date and determine when the next game is which is stored in the database under schedule. I am relatively new to this but thought I would seek advice here.

Also, I have another question which is just out of curiosity if someone knows as I realize this is a php forum... I am beginning to learn some AJAX and I would like to use it to call data from other pages to a (center like frame) in my main index. I am currently using an IFRAME but I wondered if its possible with AJAX and if you would return the data into a DIV tag or what.

Thanks for all the help guys,

Matt

[bpat1434]

[man]strtotime/man

Something like....

SELECT `date`, day, `time`, diamond, vs
FROM schedule
WHERE time >= UNIX_TIMESTAMP(NOW())
ORDER BY time ASC
LIMIT 0,1

That should return the game results you need 😉

For your second question: PatterNet.com is a site I made. It's using AJAX to completely rewrite the contents of the main content div.

It's possible. It's basically you write the innerHTML of the document node with the response from the server (php page).

[phattymatty]

Cool I will try the SQL - i think I will have to change the line

WHERE time >= UNIX_TIMESTAMP(NOW()) to
WHERE date >= $shortdate 

//sidenote:    $shortdate= date("m/d/y"); 

I'll take a look at your site and try to implement the same thing to mine. Thanks for the quick reply!

you can compare two dates that are in the format:
xx/xx/xx

through MYSQL right? Stored in the db is ex: 07/28/06 and i will be using

date("m/d/y') 

to get current date in the same format.

thanks matt

[phattymatty]

<?php
//set up a formula to determine opponents for next game.
$shortdate = date("Y-m-d"); //for database compare to get opponent
$sql="SELECT `date`, day, `time`, diamond, vs
FROM schedule
WHERE `date` >= $shortdate
ORDER BY `date`,`time` ASC
LIMIT 0,1";
$result=mysql_query($sql);

if (isset($result)) {
            $row = mysql_fetch_assoc($result);
	}

The results I am getting are just the first row in the db, it is not sorting them by date like expected- it is returning a game from 4/27/06 as the current next game when it should be returning 8/3/06. The date field in the DB is listed as type (DATE) and the format is YYYY-MM-DD. The short date comes out to the same format. For some reason, they are not comparing against each other.

Please Help!

[bpat1434]

you can compare two dates that are in the format:
xx/xx/xx

Nope. It has to be in either YYYYMMDD or Unix Timestamp format to be compared. That's why I queried the way I did....

I would try this query:

$sql="SELECT `date`, day, `time`, diamond, vs
FROM schedule
WHERE UNIX_TIMESTAMP(`date`) >= UNIX_TIMESTAMP($shortdate)
ORDER BY `date`,`time` ASC
LIMIT 0,1";

[phattymatty]

Ok, I renamed a few of the vars to avoid confusion or misinterpretation... all the vars are now preceded with schedule_ . The code I have is as follows:

<?php
//set up a formula to determine opponents for next game.

$shortdate = date("Y-m-d"); //for database compare to get opponent
$sql="SELECT *
FROM schedule
WHERE UNIX_TIMESTAMP(schedule_date) >= UNIX_TIMESTAMP($shortdate)
ORDER BY schedule_date, schedule_time ASC
LIMIT 0,1";
$result=mysql_query($sql);
if (isset($result)) {
            $row = mysql_fetch_assoc($result);
	}else {
	print ('There are no upcoming games listed');
	}

print_r($row);

//end formula
?> 

The result I get from a print_r is:
Array ( [schedule_id] => 1 [schedule_week] => 1 [schedule_date] => 2006-04-27 [schedule_day] => Thursday [schedule_time] => 06:15:00 [schedule_diamond] => 6 [schedule_vs] => RE\MAX [schedule_result] => Win )

when the result should be:

Array ( [schedule_id] => 14 [schedule_week] => 14 [schedule_date] => 2006-08-03 [schedule_day] => Sunday [schedule_time] => 06:15:00 [schedule_diamond] => 0 [schedule_vs] => Casto [schedule_result] => )

Now I have the data type in MYSQL set as (DATE) for schedule_date. It looks like this 2006-08-03. In php, the $shortdatedate(Y,m,d) looks like this 2006-07-23. Now I have both of those #'s in the same format and even tried UNIX_TIMESTAMP() to both and it still will not allow them to compare. If i change the sql to WHERE week >= 13 it works fine with pulling the right data. So, I hope to get this figured out as I understand the concept but cant get the numbers to come out right. Should I be using the mysql_fetch_assoc? Also, why does it keep defaulting or displaying the first entry in the db? That doesnt fit the WHERE clause. Thanks again ! I really appreciate it!

[phattymatty]

Well strangely enough I tried a strtotime conversion of the $shorttime as $unixtime and then used that to compare and it works. At any rate I am glad and now I will move on to setting up the AJAX. Thanks for all your help and if you wanted to take a peak at the work in progress its at http://bucknuts.phattymatty.net

Here was the code that worked:

<?php
//set up a formula to determine opponents for next game.

$shortdate = date("Y-m-d"); //for database compare to get opponent
$unixdate = strtotime($shortdate); //change type up
$sql="SELECT *
FROM schedule
WHERE UNIX_TIMESTAMP(schedule_date) > $unixdate
ORDER BY schedule_date, schedule_time ASC
LIMIT 0,1";
$result=mysql_query($sql);
if (!isset($result)) {
            print ('There are no upcoming games listed');
	}else {
			$row = mysql_fetch_assoc($result);
			}

I'll let you know how things go with the AJAX. I have a book here that I just started to look at about AJAX apps and how to use it. I know that my first task is relatively easy in that I just want my main center div to display all the links in the same fashion you would use the iframe as a "target".

Matt