PHP SQL - Simple Query Help Requested

Author

Simple Query Help Requested

C. David Rossen

2004-11-20, 8:55 pm

Hello:

I have developed a MySQL database that outputs of garage sales in a
neighborhood. One of the fields obviously is the the date of the garage
sale. I have called that field "date" and the table name is "garagesale".
When the output script is called, I would like any garage sales that have
already taken place to drop off of the output list. I know it involves a
query that will not display any record where date < today's date, but I am
having trouble putting it together. If someone could help me out I would
greatly appreciate it. Thank you in advance.

David

Jim Marnott

2004-11-21, 3:57 am

Try making your date a Unix timestamp. It makes comparing dates so much
easier. I just discovered this method and I would never go back. PHP has
some wonderful functions for this. Research it.

$todaysdate = time();
$sql = "SELECT * FROM garagesale WHERE date > '$todaysdate'";
$query = mysql_query($sql);

.................. or something along those lines.

"C. David Rossen" <cdrossen@cdrmarketing.com> wrote in message
news:TJKdnfYwzuk1RwLcRVn-vw@comcast.com...
> Hello:
>
> I have developed a MySQL database that outputs of garage sales in a
> neighborhood. One of the fields obviously is the the date of the garage
> sale. I have called that field "date" and the table name is "garagesale".
> When the output script is called, I would like any garage sales that have
> already taken place to drop off of the output list. I know it involves a
> query that will not display any record where date < today's date, but I am
> having trouble putting it together. If someone could help me out I would
> greatly appreciate it. Thank you in advance.
>
> David
>
>

Jim Evans

2004-11-21, 8:55 am

If you don't want to keep the old dates in the table any more, you could
run a query first that deletes them, or maybe moves them to an
"old_dates" table - for archiving.

Still requires using date as an index - and 'd go with Jim's suggestion
too - so much simpler.

Jim Evans (no relation)

Jim Marnott wrote:
> Try making your date a Unix timestamp. It makes comparing dates so much
> easier. I just discovered this method and I would never go back. PHP has
> some wonderful functions for this. Research it.
>
>
> $todaysdate = time();
> $sql = "SELECT * FROM garagesale WHERE date > '$todaysdate'";
> $query = mysql_query($sql);
>
> ................. or something along those lines.
>
>
>
> "C. David Rossen" <cdrossen@cdrmarketing.com> wrote in message
> news:TJKdnfYwzuk1RwLcRVn-vw@comcast.com...
>
>
>
>

C. David Rossen

2004-11-22, 3:57 am

Jim and Jim:

Thanks for the help. When you say making date a Unix timestamp, are you
talking about changing it from varchar to timestamp? Thank you.

David

"Jim Evans" <longhairedjim@ntlworld.com> wrote in message
news:P1Ynd.10$W91.2@newsfe5-win.ntli.net...[color=darkred]
> If you don't want to keep the old dates in the table any more, you could
> run a query first that deletes them, or maybe moves them to an
> "old_dates" table - for archiving.
>
> Still requires using date as an index - and 'd go with Jim's suggestion
> too - so much simpler.
>
> Jim Evans (no relation)
>
> Jim Marnott wrote:
has[color=darkred]
"garagesale".[color=darkred]
have[color=darkred]
am[color=darkred]

Jim Marnott

2004-11-22, 3:57 am

No. Mysql timestamp and unix timestamp are very different things. A Mysql
timestamp is something like "20040706" (ie: just a regular date) where a
Unix timestamp is the number of seconds since Jan 1, 1970. Unix timsetamps
may not look very pretty, but you can do comparisons and date "math" very
easily because it's just a matter of simple addition and subtraction.

eg: If it's now 1101093660, you know tomorrow will be 1101093660 + 86400
because there are 86400 seconds in 24 hours.

One very important thing to remember when working with UNIX timestamps and
Mysql is that your timestamp column must be INT. Otherwise comparisons
become difficult, if not impossible. I once spent hours trying to debug
date comparison code that just wouldn't work, when It finally dawned on me
to check the column type.

JM

"C. David Rossen" <cdrossen@cdrmarketing.com> wrote in message
news:z5OdnbT8k64poTzcRVn-jw@comcast.com...
> Jim and Jim:
>
> Thanks for the help. When you say making date a Unix timestamp, are you
> talking about changing it from varchar to timestamp? Thank you.
>
> David
>
> "Jim Evans" <longhairedjim@ntlworld.com> wrote in message
> news:P1Ynd.10$W91.2@newsfe5-win.ntli.net...
> has
> "garagesale".
> have
> am
>
>

C. David Rossen

2004-11-23, 3:56 am

Acutally, there are 4 fields which involve the dates and times of 2 days
(all garage sales are two days). In the php output, here iw what I have:

$date1=mysql_result($result,$i,"date1");
$date2=mysql_result($result,$i,"date2");
$time1=mysql_result($result,$i,"time1");
$time2=mysql_result($result,$i,"time2");

This displays the data captured from the html form that I have. date1 &
date2 are date fields and time1 and time2 are time fields. How would I
display them? For example, date1 is now displaying like 2004-12-03. I
understand that because that is how a date field is formatted in MySQL. But
how would I get it to display, something like: Friday, 12/03/04? Same with
time...how would I format it to display say 9:00 AM instead of 09:00:00?
Thanks.

David

"Jim Marnott" <sdlkj@lkj.com> wrote in message
news:H_bod.12121$yx6.489374@weber.videotron.net...
> No. Mysql timestamp and unix timestamp are very different things. A
Mysql
> timestamp is something like "20040706" (ie: just a regular date) where a
> Unix timestamp is the number of seconds since Jan 1, 1970. Unix
timsetamps
> may not look very pretty, but you can do comparisons and date "math" very
> easily because it's just a matter of simple addition and subtraction.
>
> eg: If it's now 1101093660, you know tomorrow will be 1101093660 + 86400
> because there are 86400 seconds in 24 hours.
>
> One very important thing to remember when working with UNIX timestamps and
> Mysql is that your timestamp column must be INT. Otherwise comparisons
> become difficult, if not impossible. I once spent hours trying to debug
> date comparison code that just wouldn't work, when It finally dawned on me
> to check the column type.
>
> JM
>
>
> "C. David Rossen" <cdrossen@cdrmarketing.com> wrote in message
> news:z5OdnbT8k64poTzcRVn-jw@comcast.com...
could[color=darkred]
PHP[color=darkred]
garage[color=darkred]
involves[color=darkred]
I[color=darkred]
>
>

Sponsored Links