Date issue

[homchz]

I have started a project to take information from a calendar database for my web site. I want it to display the next 2 events in sequental order, and what I have works.

Until the December to January as you can see it always is looking for what is equal to or greater than the date on the site. But if my next event is in January = 1 of 2005 it will never appear larger than December = 12 of 2004.

<?php
$m = date(n);
$d = date(j);
$y = date(Y);

mysql_connect("localhost","username","password");

mysql_select_db("database");

$result = mysql_query("select * from calendar_events where ((year >= $y )  and (mday >= $d ) and (month >= $m )) Limit 2");
$row = mysql_fetch_array($result);


  $title=$row["title"];
  $event_text=$row["event_text"];
  $mday=$row["mday"];
  $month=$row["month"];
  $year=$row["year"];
  $eventid=$row["eventid"];

?>

I am not looking for any one to code anything but a few hints. I am new to this and this took me all day :bemused:

I think I woudl be looking for an

if else statement that changes my search perameters, but not sure if that is even possible.

Any hints would be appreciated.

Josh

[devinemke]

why are you storing the date in 3 separate fields? just store the in a single DATE field type then do:

SELECT * FROM calendar_events WHERE date >= CURDATE LIMIT 2

you can always break the date up later using mySQL's Date and Time Functions

[homchz]

unfortunatly, it is not my db to manage the way the input works. It takes three fields to enter the information. Month, Day, and Year. That way you can enter future events and every one can enter int he same format.

Now could I pull the information and then match the info to make one figure and go from there?

Take

5 | 6 | 2004 and convert that too

562004

before I sort?

[devinemke]

SELECT * FROM calendar_events WHERE UNIX_TIMESTAMP(CONCAT(year, '-', month, '-', mday)) >= UNIX_TIMESTAMP() LIMIT 2

[homchz]

Thanks,

That works until I get a number like

2004124 against
20041210

the 10 is awalys bigger but the 4 should be presented first.

is there away to force a db to add a zero to a single number?

but I guess that is a mySQL question no a PHP one.

Thanks for showing me CONCAT though.

Josh

[tsinka]

Hi,

another way could be (based on the query in your first post):

select * from calendar_events where ((year >= $y )  and (mday >= $d ) and (month >= $m )) or year>$y ORDER BY year,month,mday Limit 2

Make sure to have an index on the fields year,month and mday.

Thomas

[homchz]

I decided to try a new appraoch.

I have taken todays date and converted it to UNIX Time. THen I take the numbers that make up the date in the db and convert them to Unix Time. This allows me to compare the numbers with no issues.

This displays what I want but not as I want it.

I am looking to have it read the entire db then post what qualifies. I can do this with one at a time but not as a whole. I am missing something in the display I think.

Any ideas or am I going at the display all wrong.

Thanks

Josh

<?php
//Convert Actual Time to Unix Time//
$timedate = date("Ymd");
$today = strtotime ($timedate);
// Connect to Database//
mysql_connect("localhost","username","password");
mysql_select_db("database");

//Run Query//
$result = mysql_query("select * from ibf_calendar_events WHERE eventid = 11");
$row = mysql_fetch_array($result);


  $title=$row["title"];
  $event_text=$row["event_text"];
  $mday=$row["mday"];
  $month=$row["month"];
  $year=$row["year"];
  $eventid=$row["eventid"];

//Convert Event Date to Unix Time
$event = ("$year$month$mday");
$eventdate = strtotime ($event);


//Display//		
$displaydate = ("$mday-$month-$year");		
if (

$eventdate >= $today 

) {
echo ("$displaydate <br /> <a href=\"http://domain.com/index.php?act=calendar&code=showevent&eventid=$row[eventid]\">$row[title]</a>"); 
}

?>

I think my issue it the fact it pulls the information then converts then compares but I do not know if it has to create a loop to check every entry.

[homchz]

I have moved on to this script and will only return the else when then true. However when the actual is true it returns nothing.

<?php
$url = "http://www.musicianalliance.com/forums/index.php?act=calendar&code=showevent&eventid=";
$displaydate = ("$mday-$month-$year");	
//Convert Actual Time to Unix Time//
$timedate = date("Ymd");
$today = strtotime ($timedate);
// Connect to Database//
mysql_connect("localhost","username","password");
mysql_select_db("database");

//Run Query//
$result = mysql_query("select * from ibf_calendar_events WHERE eventid = '11'");
$row = mysql_fetch_array($result);


  $title=$row["title"];
  $event_text=$row["event_text"];
  $mday=$row["mday"];
  $month=$row["month"];
  $year=$row["year"];
  $eventid=$row["eventid"];

//Convert Event Date to Unix Time
$event = ("$year$month$mday");
$eventdate = strtotime ($event);

//Display//		
if ($eventdate >= $today ) 
	{
		while ($row = mysql_fetch_assoc($result))

		$events['giglist']= '$url$row[eventid]';
}
	else
{
		$events['giglist']= 'No characters';
}
?>

<?php echo $events['giglist'] ?>