[Resolved] Calendar Type Question

cmccomas

Hey,

First off, I know a good bit of PHP, but this one problem is giving me fits.

I want to setup a table so a band I did a website can enter their upcoming tour dates to be published on the site.

In the database we want the show date, city, venue, and link to the venue, but what I can't figure out is how to set it up so that on the page there is a way to sort the shows out in order from closest to furtherest.

Like if they have three shows July 1, July 3, and July 5, I want them to be displayed in that order on the site. I could just use show_ID (the primary key), but sometimes they add shows between others and it wouldn't sort right.

If anyone could help, I'd greatly appreciate it.

rachel2004

From what you're describing, you could either do it in PHP using the mktime() function or in MySQL (if that's your db) using UNIX_TIMESTAMP() function.

Basically, you want to convert all dates to EPOCH time and then order by that in your query.

cmccomas

Thanks rachel.

Now, they manage the site and the updates, I have some PHP and textboxes that allow them to do this and enter the info directly into the database.

How can I setup a textbox/textboxes to allow them to pick a date and it enter it into the database?

devinemke

maybe i'm missing something obvious here but can't you simply do:

SELECT * FROM shows ORDER BY date

i'm assuming that your table is called "shows" and the date is stored in a date type field called "date"

cmccomas

mike,

I guess I can, but what I'm really trying to ask, I guess, is how can I setup a page with textboxes and PHP to insert the date into the table?

Using this code:

$query = "INSERT INTO ryan(ryancontact, coachcontact, pitchcontact) VALUES('$ryancontact', '$coachcontact', '$pitchcontact')";

devinemke

here's a very basic example (no validation, no error checking, etc...)

<form action="<? echo $_SERVER['PHP_SELF'] ?>" method="POST">
date: <input type="text" name="date">(YYYY-MM-DD)<br>
city: <input type="text" name="city"><br>
venue: <input type="text" name="venue"><br>
<input type="submit" name="submit" value="submit">
</form>

<?
if (isset($_POST['submit']))
{
	// connect to database

$_POST = array_map('stripslashes', $_POST);

$date = date('Y-m-d', strtotime($_POST['date']));
$city = $_POST['city'];
$venue = $_POST['venue'];

$result = mysql_query("INSERT INTO shows SET date = '$date', city = '$city', venue = '$venue'");
if ($result) {echo 'database insert OK';}
else {echo 'database insert FAILED';}
}
?>

rachel2004

Originally posted by devinemke
maybe i'm missing something obvious here but can't you simply do:
SELECT * FROM shows ORDER BY date
[/B]

My mistake... for some reason when I read the original post, I had it in my head that he was doing datetime comparison and checking for intervals between show dates. Your query is actually the better route, devinemke.

cmccomas

Thanks, you guys have been a huge help. Two more quick questions and I'll mark this thread resolved.

When I do simple news items, I insert them into the database using timestamp and now(), then when calling them to a page I can use "date_format(news_date, '%c.%d.%y')as news_date" to format it how I want. Can I do the same with the date field in this example?

Also, if I only wanted to use PHP to call and display events in June, could I do that?

devinemke

Can I do the same with the date field in this example?

yes, in fact if you are just storing dates you should use the "date" field type rather than timestamp. the point of the mySQL timestamp type is to store the last update time of the record. for the data structure you are doing (listing of events) the normal "date" or "datetime" types would suffice. again, you can always format the date anyway you want:

SELECT DATE_FORMAT(datetime, '%c.%d.%y') FROM table

Also, if I only wanted to use PHP to call and display events in June, could I do that?

yes, better to do that in your query:

SELECT * FROM table WHERE MONTH(datetime) = 6

cmccomas

Thanks, devinemke! You've been a tremendous help!