[SOLVED] MySQL data selection with PHP

[FForce2195]

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

 

[DarkWater]

What data type is the comment_date_gmt column stored as?  Is it a DATETIME? (it should be)

[FForce2195]

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.

[DarkWater]

Great.  Try doing:

 

$sql_date = "SELECT * FROM $tabl0a WHERE comment_date_gmt > DATESUB(NOW(), INTERVAL 14 DAY)";

[FForce2195]

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).

[DarkWater]

What do you mean?  Which error do you get?

[FForce2195]

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;

}

[FForce2195]

It's okay.  I guess drawing the last 200 records won't be a terribly bad idea.  :'(

[DarkWater]

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.

[FForce2195]

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.

[DarkWater]

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? >_<

[FForce2195]

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.

[DarkWater]

@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.

[Daniel0]

SELECT * FROM table WHERE comment_date_gmt > NOW() - INTERVAL 2 WEEK;

 

Is better. No need for DATE_SUB(). MySQL can handle the arithmetic itself.

[FForce2195]

@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.

[DarkWater]

Are you sure you're uploading the file again on the server? >_<

[FForce2195]

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.

[FForce2195]

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.