Datetime

[thekidscareya]

I have a table with the field "date_time" with a field type of "datetime".

How do I query for the next 3 upcoming events?
How do I query for all the events coming in the next month? the next year?
How do I create a form and insert query to insert new events?

Thanks.

[devinemke]

thekidscareya wrote:

How do I query for the next 3 upcoming events?

SELECT * FROM table WHERE date_time >= CURDATE() LIMIT 3

thekidscareya wrote:

How do I query for all the events coming in the next month? the next year?

SELECT * FROM table WHERE date_time BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 30 DAY)

SELECT * FROM table WHERE date_time BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 1 YEAR)

[thekidscareya]

How would I make the form? Should I have a seperate field for the date and time? Any suggestions?

Thanks for your help so far. I really appreciate it.

EDIT: One more question:

When using the select query, how could I break up $row['date_time'] (if $row = mysql_fetch_array($query)) into 2 variables - the date and the time?

Thanks.

[Drakla]

Devinemke, I think the next 3 items needs an order by clause otherwise you could get any 3 items as long as they're beyond the current date, not the closest.

SELECT * FROM table WHERE date_time >= CURDATE() ORDER BY date_time LIMIT 3;

[devinemke]

Drakla wrote:

Devinemke, I think the next 3 items needs an order by clause otherwise you could get any 3 items as long as they're beyond the current date, not the closest.

quite right, good call

[devinemke]

thekidscareya wrote:

When using the select query, how could I break up $row['date_time'] (if $row = mysql_fetch_array($query)) into 2 variables - the date and the time?

SELECT DATE(date_time) AS date, TIME(date_time) AS time FROM table

i assume that you are using mySQL. if so you should really do some reading on mySQL's Date and Time Functions.

[thekidscareya]

I get this error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(date_time) AS date, TIME(date_time) AS time, id, subject, html

I'm using the following code:

				$sql = "SELECT DATE(date_time) AS date, TIME(date_time) AS time, id, subject FROM `tbl_calendar` WHERE date_time BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 30 DAY)";
				$query = mysql_query($sql) or die(mysql_error());
				$num_rows = mysql_num_rows($query);

			?>
			<p>Upcoming Events</p>
			<?php
			if($num_rows == 0)
				{
				?>
				<p>There are no events in the next month.</p>
				<?php
				}else{
				while($row = mysql_fetch_array($query))
					{
					?>
					<p><a href="calendar.php?id=<?php echo $row['id']; ?>"><?php echo $row['subject']; ?></a>:
				    <?php echo $row['date']; ?> at <?php echo $row['time']; ?></p>
					<?php
					}
				}
?>

[devinemke]

you have two choices:

1. upgrade to mySQL 4.1.1 or higher
2. use the DATE_FORMAT() fucntion to acheive the same result

[thekidscareya]

1. how do i write an if statement so i know what sql to run?

eg:

if($mysql_version > 4.1.1)
{
// run the query
}else{
// run another query
}

2. how do i write the sql if im using mysql 4.0?

thanks

[devinemke]

as i metioned a few posts above, you should read up on mySQL's Date and Time Functions, including the syntax for the DATE_FORMAT() function.