Page 1 of 2
mysql date troubles
Posted: Thu Jan 11, 2007 3:22 pm
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?
Posted: Thu Jan 11, 2007 3:40 pm
by feyd
What field type is create_date?
Posted: Thu Jan 11, 2007 3:45 pm
by Obadiah
date type format
Posted: Thu Jan 11, 2007 3:48 pm
by feyd
If you echo the above query string then run it using phpMyAdmin or whatever quickie tool you have, does it return records?
Posted: Thu Jan 11, 2007 3:52 pm
by Obadiah
<=noob

i never even did that before...how do i do that?
Posted: Thu Jan 11, 2007 3:55 pm
by snowrhythm
are you using apache server?
Posted: Thu Jan 11, 2007 3:59 pm
by Obadiah
yes i have apache installed as a package using xampp
[edit]
nice signiture by the way...that was a awsome flick..."Inconcievable!!!"
Posted: Thu Jan 11, 2007 4:17 pm
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.
>
Posted: Thu Jan 11, 2007 4:38 pm
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
Posted: Thu Jan 11, 2007 4:43 pm
by feyd
You'll have to substitute the value that is in the variable where it should be.. as I said.. ECHO it.
Posted: Thu Jan 11, 2007 5:02 pm
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?
Posted: Thu Jan 11, 2007 5:10 pm
by feyd
"use MyDatabase;"
Posted: Wed Jan 31, 2007 11:03 am
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
Posted: Wed Jan 31, 2007 11:08 am
by feyd
The command line is waiting for the end of your statement.. a semicolon.
Posted: Wed Jan 31, 2007 11:21 am
by Obadiah
cool....very cool...kinda sucks that i was just missing a simicolon though
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
