mysql date troubles

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

mysql date troubles

Post by Obadiah »

30 days or so i posted here about having php display a record via sql based on the create date and i figured this out as my solution

Code: Select all

SELECT * FROM merchant WHERE user_name = '{$_SESSION['logname']}' AND create_date<=DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
but for some reason it doesnt allow any records to display...where did i screw up?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What field type is create_date?
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

date type format
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If you echo the above query string then run it using phpMyAdmin or whatever quickie tool you have, does it return records?
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

<=noob :oops: i never even did that before...how do i do that?
User avatar
snowrhythm
Forum Commoner
Posts: 75
Joined: Thu May 04, 2006 1:14 pm
Location: North Bay Area, CA

Post by snowrhythm »

are you using apache server?
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

yes i have apache installed as a package using xampp
[edit]
nice signiture by the way...that was a awsome flick..."Inconcievable!!!"
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

How do you do what? Echo the query? (I hope you're not asking that.)

Running the query in phpMyAdmin is equally as easy so that leaves "other" quickie tools.. like mysql command line.. which is pretty straight forward: you log in. You run the query. It should look something like this:

Code: Select all

> mysql -uMyUsername -pMyPassword

mysql> SELECT * FROM merchant WHERE user_name = '{$_SESSION['logname']}' AND create_date<=DATE_SUB(CURDATE(), INTERVAL 30 DAY);

+---------------+
|   some stuff  |
+---------------+
x rows in result set. 0.43 secs

mysql> exit

Goodbye.

>
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

it says

Code: Select all

ERROR 1064 (42000): 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 'logname']}' AND create_date<=DATE_SUB(CURDATE(), INTERVAL 30 DAY)' at line 1
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You'll have to substitute the value that is in the variable where it should be.. as I said.. ECHO it.
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

its asking me to specify a database how do i do that...i know how to do it from php but not from the command line...also...this wont actually delete records from the database right?...im not wanting to delete records...im only the person logging in to see records no older than 30 days....am i going about this the right way?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

"use MyDatabase;"
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

i appologize for bringin up this dusty post but ive tried everything to get this to work and it still dont...it dosent return any values and doesnt give me any errors

when i go to the command line i substitute the logname for a name in the database and it does the same thing there...it just sakes me to another line...like its waiting for more code...im confused as to where to go from here
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The command line is waiting for the end of your statement.. a semicolon.
User avatar
Obadiah
Forum Regular
Posts: 580
Joined: Mon Jul 31, 2006 9:13 am
Location: Ashland, KY
Contact:

Post by Obadiah »

cool....very cool...kinda sucks that i was just missing a simicolon though :oops: :drunk:

Code: Select all

mysql> SELECT * FROM merchant WHERE user_name = 'JGilchrist1510' AND create_date
<=DATE_SUB(CURDATE(), INTERVAL 30 DAY);
+--------------+--------------+---------------+---------------------------------
----+---------------+-------------+------+--------+----------+----------------+-
-----------------+--------------+----------------------+---------------+--------
----+-------------+----------------+------------------+-------------------------
-----------------+-------------+
| phone_num    | merchant_num | date_recieved | merchant_name
    | purchase_type | lease_score | amex | app_id | discover | user_name      |
check_conversion | gift_loyalty | app_type             | terminal      | serial_
num | nms         | ckmerchant_num | giftmerchant_num | comments
                 | create_date |
+--------------+--------------+---------------+---------------------------------
----+---------------+-------------+------+--------+----------+----------------+-
-----------------+--------------+----------------------+---------------+--------
----+-------------+----------------+------------------+-------------------------
-----------------+-------------+
| 231-843-2058 | 286604381272 | 12/12/2006    | HAMLIN GROCERY
    | Lease         | B/C         | Yes  | NULL   | Yes      | JGilchrist1510 |
Yes              | Yes          | Retail with Debit    | val pak 700   | NULL
    | NULL        | 641-630015-609 | 00012411         | MERCHANT COMPLETE
                 | 2006-12-15  |
| 231-873-4986 | 286604319502 | 12/05/2006    | WALKERS FINE DINING
    | Lease         | D/E         | No   | NULL   | No       | JGilchrist1510 |
Yes              | Yes          | Restaurant with Tips | VALUEPACK 700 | 2392743
    | 800-440-770 | 641-629310-633 | 00012302         | NULL
                 | 2006-12-08  |
| 231-845-8777 | 286604368598 | 12/06/2006    | CREATIVE CUTS DAY SPA & TANNING
LLC | Lease         | A           | Yes  | NULL   | Yes      | JGilchrist1510 |
Yes              | Yes          | Restaurant with Tips | VALUEPACK 700 | 2394754
    | 800-440-770 | 641-629355-637 | 00012304         | NULL
                 | 2006-12-08  |
| 231-873-1820 | NULL         | 12/7/2006     | COUNTRY BOY PRODUCTS INC
    | Lease         | N/A         | No   | NULL   | No       | JGilchrist1510 |
No               | No           | Retail               | NULL          | NULL
    | NULL        | NULL           | NULL             | PENDED OTHER LOCATION IS
 NOT PROCESSING. | 2006-12-15  |
| 231-893-6688 | 28660000     | 12/20/2006    | JOHNSONS GREAT OUTDOORS
    | Lease         | N/A         | No   | NULL   | Yes      | JGilchrist1510 |
Yes              | Yes          | Retail with Debit    | VALUEPACK 700 | NULL
    | NULL        | NULL           | NULL             | NULL
                 | 2006-12-26  |
| 231-861-7552 | 286604345869 | 12/21/2006    | SHIRT CITY USA
    | Lease         | B/C         | No   | NULL   | No       | JGilchrist1510 |
Yes              | No           | Retail               | VALUEPACK 700 | NULL
    | NULL        | 641-630506-642 | NULL             | NULL
                 | 2006-12-26  |
| 231-843-6756 | 286604337895 | 12/14/2006    | ALL SEASONS MOBILE MARINE
    | Lease         | D/E         | No   | NULL   | Yes      | JGilchrist1510 |
Yes              | Yes          | Retail               | VALUEPACK 700 | 2407970
    | 800-440-770 | 641-360106-608 | 00012468         | NULL
                 | 2006-12-26  |
| 231-894-8995 | 286604352844 | 12/28/2006    | jimmy's pub
    | Lease         | A+          | Yes  | NULL   | Yes      | JGilchrist1510 |
Yes              | Yes          | Restaurant with Tips | valpak 700    | 2427710
    | 800-440-770 | NULL           | NULL             | restaurant w tips and de
bit              | 2006-12-28  |
+--------------+--------------+---------------+---------------------------------
----+---------------+-------------+------+--------+----------+----------------+-
-----------------+--------------+----------------------+---------------+--------
----+-------------+----------------+------------------+-------------------------
-----------------+-------------+
8 rows in set (0.00 sec)
what would be the reason that it works there but not in my script :?
Post Reply