...

View Full Version : Looking for some assistance on this query...



unrelenting
02-27-2007, 04:40 AM
Can anyone tell me why this isn't working....


$todays_date = date("m/d");

$query="SELECT * FROM bb_schedule WHERE bb_date >= " . $todays_date . " ORDER BY bb_date LIMIT 5";

What's it's giving me is the very first date which is less than todays date.

The dates are in the database like this..... 03/21

Does the column have to be an INT? I used varchar so I could use the / or - symbols.

Any assistance would be appreciated.

CFMaBiSmAd
02-27-2007, 04:58 AM
If your column is a varchar, there should be quotes around the value you are comparing so that it is treated as a string. It is probably converting this to a number and doing the division. Try this -

$query="SELECT * FROM bb_schedule WHERE bb_date >= '$todays_date' ORDER BY bb_date LIMIT 5";

unrelenting
02-27-2007, 05:07 AM
If your column is a varchar, there should be quotes around the value you are comparing so that it is treated as a string. It is probably converting this to a number and doing the division. Try this -

$query="SELECT * FROM bb_schedule WHERE bb_date >= '$todays_date' ORDER BY bb_date LIMIT 5";

Thanks, but that gets the same result. I had tried that initially. Also, it doesn't matter if I use >= or <= I still get the first 5 dates on the schedule which begin 02/02.

unrelenting
02-27-2007, 05:17 AM
How embarrassing. I forgot to upload the file to my live server.

It worked this time. Thanks. :thumbsup:

guelphdad
02-27-2007, 03:13 PM
If your dates are stored as:

The dates are in the database like this..... 03/21

then they are either stored as varchar/char format in which case you lose all DATE/TIME functions in mysql OR you are not using MySQL.

If you aren't using MySQL please specify so I can move the thread to the general database forum and title your thread with the database you are using.

If you are using mysql you might want to consider entering your dates in yyyymmdd format so you can use DATE functions that are available to you in mysql.

unrelenting
02-28-2007, 02:00 AM
If your dates are stored as:


then they are either stored as varchar/char format in which case you lose all DATE/TIME functions in mysql OR you are not using MySQL.

If you aren't using MySQL please specify so I can move the thread to the general database forum and title your thread with the database you are using.

If you are using mysql you might want to consider entering your dates in yyyymmdd format so you can use DATE functions that are available to you in mysql.

I am using MySQL. I tried to use the INT format but it wouldn't accept the extra characters I wanted to use. I suppose there is a way to output with the character but I don't know how to do all of that. I read a little on the timestamp function but I needed to enter the dates in myself and didn't want to have to convert each one.

If it isn't obvious I am very green to databases and am learning on the fly. I have grasped a lot of it but there is much that I still have to learn.

guelphdad
02-28-2007, 03:24 PM
if you are using dates then use a DATE type and insert in the format yyyy-mm-dd if you are using DATE and TIME then use DATETIME and insert in format yyyy-mm-dd hh:mm:ss

you can also use current_date() to insert a current date or now() to insert date and time.

Using TIMESTAMP, will automatically insert the time or update the time depending if you are doing an insert or an update to your table.

If you use char/varchar then you can't use any DATE or TIME functions like DATE_ADD or DATE_SUB used to calculate whether your records are in the next 15 days for example.

You can use DATE_FORMAT to pull your dates out in any number of ways so don't worry about how your dates look when they are inserted.

unrelenting
03-01-2007, 12:48 AM
Thanks, that is very informative and I went ahead and saved it to a text file. I won't be needing much of any of that for this project. All I am using this for is a permanent baseball schedule. I input it (which I already have) and it doesn't change. I just need it to display on the schedule page and I also need it to display the next 5 games for the main page. There's really not much adding and modifying necessary for this one but in the future I can use several of those functions you mentioned.

Thanks again.

Fumigator
03-01-2007, 01:01 AM
I'm glad you are open to using DATE and/or DATETIME data types. You gain SO MUCH functionality when you do so-- even in a simple application where you just want to store a date to display it here or there, you find yourself wanting to perform a comparison against that date and it breaks down because you're storing it as a char or varchar data type.

OK... I'm off my soapbox... :D

unrelenting
03-09-2007, 03:56 AM
OK, I converted all my dates to timestamp in an INT column.

Everything I try is failing. I can't seem to craft a statement that works now.

I need it to select the dates in the table that are later than the current date.

$bsk_date is a timestamp.

Here is what I have at the moment....



$todays_date = date("m/d/y");

$query="SELECT * FROM bsk_schedule WHERE date('%m/%d/%y', bsk_date) >= " . $todays_date . " ORDER BY bsk_date LIMIT 5";

Inigoesdr
03-09-2007, 04:09 AM
SELECT * FROM `bsk_schedule` WHERE `bsk_date` > UNIX_TIMESTAMP() ORDER BY `bsk_date` ASC
I think that's what you're trying to do.

unrelenting
03-09-2007, 04:18 AM
SELECT * FROM `bsk_schedule` WHERE `bsk_date` > UNIX_TIMESTAMP() ORDER BY `bsk_date` ASC
I think that's what you're trying to do.

Bingo. :thumbsup:

I didn't know about the UNIX_TIMESTAMP().

Another question....why did you add the single quotes?

Inigoesdr
03-09-2007, 04:40 AM
They're not single-quotes, they're backticks(under the escape key), and I added them because it's proper syntax.

guelphdad
03-09-2007, 04:55 AM
actually no, backticks are not proper syntax, they don't conform to sql standards.
they are proprietary to mysql and would cause an error if you transferred over to any other database. they should basically never be used.

they can be used to escape a column, table name etc. that uses a reserved word. better practice of course would be to rename that item that is using a reserved word.

guelphdad
03-09-2007, 04:56 AM
OK, I converted all my dates to timestamp in an INT column.
Why did you convert to an INT?

why didn't you use a TIMESTAMP column which will automatically insert/update any time a row is affected and you don't need to worry about inserting it from php. you also get to avoid the need for conversion to/from unixtime.

unrelenting
03-09-2007, 05:08 AM
Why did you convert to an INT?

why didn't you use a TIMESTAMP column which will automatically insert/update any time a row is affected and you don't need to worry about inserting it from php. you also get to avoid the need for conversion to/from unixtime.

Because I am very green. Can I just edit it to a TIMESTAMP column in PHPMyAdmin?

Also, this:



SELECT * FROM `bsk_schedule` WHERE `bsk_date` > UNIX_TIMESTAMP() ORDER BY `bsk_date` ASC


isn't including todays date. When the schedule shows the next few games I need it to show todays also, hence the >=.

What should I add?

unrelenting
03-09-2007, 06:15 AM
I made it work by adding 24 hours to the games timestamp like this.....



SELECT * FROM bsk_schedule WHERE bsk_date + 86400 > UNIX_TIMESTAMP() ORDER BY bsk_date ASC


If there is a better way I am all ears.

unrelenting
03-14-2007, 05:03 AM
I have settled on this code for my basketball schedule table and it works great. It selects 4 games that are greater that greater than this second + 6 hours.


"SELECT * FROM bb_schedule WHERE bb_date + 21600 > UNIX_TIMESTAMP() ORDER BY bb_date ASC LIMIT 4";

On my baseball schedule table I'd like it to display a bit differently, at least until it nears the end of the schedule. I am trying to get it to display 5 games. The five games would be the last 2 games played and the next 3 games to be played.

I want to center the next game like this:

__________________
| Game 22 - 3/11/07 |
| Game 23 - 3/12/07 |
| Game 24 - 3/13/07 |
| Game 25 - 3/16/07 |
| Game 26 - 3/17/07 |
~~~~~~~~~~~~~~

My question is how do I get it to count upwards 2 more rows from WHERE bb_date + 21600 > UNIX_TIMESTAMP()? Or is there a better way?

Thanks.

Fumigator
03-14-2007, 05:35 PM
Sounds like you need a reverse order query to get those previous games. Reverse the ORDER BY (from ASC to DESC) and turn the comparison around (from greater-than to less-than-or-equal-to).

This method will require two different queries.

unrelenting
03-15-2007, 03:28 AM
Sounds like you need a reverse order query to get those previous games. Reverse the ORDER BY (from ASC to DESC) and turn the comparison around (from greater-than to less-than-or-equal-to).

This method will require two different queries.

That's exactly what I was looking for. It selects the correct 2 games but how do I get it to print ascending in my table?

Fumigator
03-15-2007, 04:42 AM
Dump the results of both queries into the same array and then sort the array.

asort() (http://us2.php.net/asort())

unrelenting
03-15-2007, 04:46 AM
Dump the results of both queries into the same array and then sort the array.

asort() (http://us2.php.net/asort())

I am not that advanced in this stuff. I can figure out how to use that function but I need some enlightenment on how to dump the results into the same array if you can spare the time.

unrelenting
03-16-2007, 03:19 AM
I got it working using the UNION syntax. Thanks.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum