FForce2195 Posted October 6, 2008 Share Posted October 6, 2008 Greetings. What a nice place... I found it yesterday. I started learning PHP 8 days ago with no programming knowledge. I'm having a bit of problem with MySQL data selection with PHP. I hope somebody will help me out. I want to extract data where the GMT date is 2 weeks old or newer. There's a field called comment_date_gmt. One entry looks like '2007-05-21 00:55:37' without apostrophes. The table variable name is $table0a. After accessing a database, I state date_default_timezone_set('UTC'); $current_date = date('Y-m-d H.i:s'); $xdate = date('Y-m-d H.i:s', mktime(0, 0, 0, date("m"), date("d")-14 , date("Y"))); If I state the following, I get an error. $sql_date = "select * from $table0a WHERE comment_date_gmt >= $xdate"; If I change $xdate to date('Y-m-d', mktime...), the program will run and data will be drawn except that every single row from the table is drawn. (The data set has a time span of 2008-09-02 to 2008-10-06.) If I state any date like comment_date_gmt >= 2008-10-01 , again, all rows will be drawn. What am I doing wrong? And how can I correctly enforce data selection where comment_date_gmt >= $xdate (or =< or whichever) ? Thank you for your help. Tom Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/ Share on other sites More sharing options...
DarkWater Posted October 6, 2008 Share Posted October 6, 2008 What data type is the comment_date_gmt column stored as? Is it a DATETIME? (it should be) Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658364 Share on other sites More sharing options...
FForce2195 Posted October 6, 2008 Author Share Posted October 6, 2008 What data type is the comment_date_gmt column stored as? Is it a DATETIME? (it should be) Yes, it is. It's set to 'datetime.' The default format is set to 0000-00-00 00:00:00. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658387 Share on other sites More sharing options...
DarkWater Posted October 6, 2008 Share Posted October 6, 2008 Great. Try doing: $sql_date = "SELECT * FROM $tabl0a WHERE comment_date_gmt > DATESUB(NOW(), INTERVAL 14 DAY)"; Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658394 Share on other sites More sharing options...
FForce2195 Posted October 6, 2008 Author Share Posted October 6, 2008 Great. Try doing: $sql_date = "SELECT * FROM $tabl0a WHERE comment_date_gmt > DATESUB(NOW(), INTERVAL 14 DAY)"; Thanks for your help. You guys are very friendly. DATESUB(NOW(), INTERVAL 14 DAY) looks like a nice selection method that I could use in a different occasion. I still get the same error when the program goes through Variable (true or false) = mysql_query(SQL, database connection). Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658488 Share on other sites More sharing options...
DarkWater Posted October 6, 2008 Share Posted October 6, 2008 What do you mean? Which error do you get? Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658491 Share on other sites More sharing options...
FForce2195 Posted October 6, 2008 Author Share Posted October 6, 2008 What do you mean? Which error do you get? $xdate_julia = mysql_query($sql_date,$my_julia); if ($xdate_julia == false) { print "Date selection has failed<BR><BR>"; exit; } Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658522 Share on other sites More sharing options...
FForce2195 Posted October 6, 2008 Author Share Posted October 6, 2008 It's okay. I guess drawing the last 200 records won't be a terribly bad idea. :'( Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658548 Share on other sites More sharing options...
DarkWater Posted October 6, 2008 Share Posted October 6, 2008 Do: $xdate_julia = mysql_query($sql_date, $my_julia) or die(mysql_error()); Also, please enclose code in tags. EDIT: Sorry, I was eating dinner, lol. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658549 Share on other sites More sharing options...
FForce2195 Posted October 7, 2008 Author Share Posted October 7, 2008 You are not suggesting that if ($xdate_julia == false) { print "Date selection has failed<BR><BR>"; exit; } is the root of the problem, are you? It's not. And I still get an error with yours. Thanks for your help, though. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658613 Share on other sites More sharing options...
DarkWater Posted October 7, 2008 Share Posted October 7, 2008 Yeah, you may get an error with mine, but it should be a more descriptive error, correct? Could you post it here? My code would show any errors with the actual query so I could debug it. Did it show some MySQL error with my code? >_< Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658620 Share on other sites More sharing options...
FForce2195 Posted October 7, 2008 Author Share Posted October 7, 2008 Yeah, you may get an error with mine, but it should be a more descriptive error, correct? Could you post it here? My code would show any errors with the actual query so I could debug it. Did it show some MySQL error with my code? >_< Ahh... That's smart. I'm sorry. I didn't get it since I'm a novice. Here it comes. 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 ')' at line 1 The MySQL version is 5.0.51a-community in case you needed it. Thanks a lot. By the way, whoever is the administrator, I wish he made an effort in only allowing him/her to see IP addresses. It's easy to make modifications. I know how to do it and can help them if needed since I also have an SMF website. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658782 Share on other sites More sharing options...
DarkWater Posted October 7, 2008 Share Posted October 7, 2008 @FForce: Only mods and admins can see your IP. You can also see your own IP, but other people can't, don't worry. =P Anyway, the query should be: $sql_date = "SELECT * FROM $tabl0a WHERE comment_date_gmt > DATE_SUB(NOW(), INTERVAL 14 DAY)"; I misspelled DATE_SUB(). I hate it when that happens. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658863 Share on other sites More sharing options...
Daniel0 Posted October 7, 2008 Share Posted October 7, 2008 SELECT * FROM table WHERE comment_date_gmt > NOW() - INTERVAL 2 WEEK; Is better. No need for DATE_SUB(). MySQL can handle the arithmetic itself. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-658902 Share on other sites More sharing options...
FForce2195 Posted October 7, 2008 Author Share Posted October 7, 2008 @FForce: Only mods and admins can see your IP. You can also see your own IP, but other people can't, don't worry. =P Yes. I'm sorry. You are right. $sql_date = "SELECT * FROM $tabl0a WHERE comment_date_gmt > DATE_SUB(NOW(), INTERVAL 14 DAY)"; SELECT * FROM table WHERE comment_date_gmt > NOW() - INTERVAL 2 WEEK; Hmm... I don't know why, but whichever I use, 2 weeks of data will be drawn after I change the interval to 1 week, 4 days or 2 days. Actually, I have only 2 weeks of day. So I don't know if it draw the entire data set. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-659359 Share on other sites More sharing options...
DarkWater Posted October 7, 2008 Share Posted October 7, 2008 Are you sure you're uploading the file again on the server? >_< Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-659364 Share on other sites More sharing options...
FForce2195 Posted October 8, 2008 Author Share Posted October 8, 2008 Are you sure you're uploading the file again on the server? I beg your pardon? I test-run PHP scripts on my computer and access a remote server. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-659632 Share on other sites More sharing options...
FForce2195 Posted October 8, 2008 Author Share Posted October 8, 2008 Hello. It looks like the table names stated in those codes were wrong. The correct one is $table0a. Now, everything is under control. Thank you very much for your help again, DarkWater, also Daniel0. Link to comment https://forums.phpfreaks.com/topic/127296-solved-mysql-data-selection-with-php/#findComment-659655 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.