...

View Full Version : PHP / Mysql's Date Interval



david07
02-24-2008, 11:18 AM
I normally enter a php date string into mysql when needed. However this time I am using Mysql automatic date. I am trying to retrieve records that are no more then 30days old, and delete those that are.

I can not figure out why my queries are not working. Even though no errors are being generated. The code is as follows:


Retrieve
$conn->query("SELECT * from statistics WHERE 'date_visited' > DATE_SUB(CURDATE(),INTERVAL 30 DAY)";



And Delete Query
$conn->query("DELETE FROM statistics WHERE 'date_visited' <= DATE_SUB(NOW(),INTERVAL 30 DAY)";


I have been changing the dates through phpmyadmin and comming back to the script to see if any changes have occured. But nothing.

Please any help would be much appreciated. Thank You.

abduraooft
02-24-2008, 01:12 PM
What is this $conn? where is you mysql_query() ? Find it and have a try by changing
mysql_query() or die(mysql_error());

Inigoesdr
02-24-2008, 01:17 PM
Retrieve
$conn->query("SELECT * from statistics WHERE 'date_visited' > DATE_SUB(CURDATE(),INTERVAL 30 DAY)";



And Delete Query
$conn->query("DELETE FROM statistics WHERE 'date_visited' <= DATE_SUB(NOW(),INTERVAL 30 DAY)";
Did you notice you used CURDATE() in the select and NOW() in the delete query? Make sure you have error checking for your queries.

What id this $conn? where is you mysql_query() ? Find it and have a try by changing
mysql_query() or die(mysql_error());
It seems to be a connection class. :p

abduraooft
02-24-2008, 03:15 PM
It seems to be a connection class. :p
Ya, I thought so, but seems he is unaware of the code structure to debug.

david07
02-25-2008, 02:29 AM
No i do know the code structure to debug. But im not getting any errors.
$conn is a connection class. I am using php 5 OO. EG.



//Connect to the database. (host,username,password,database)
$db = new mysqli("localhost", "user", "password", "database");
// Check for errors connecting to database.
if (mysqli_connect_errno()) {
die('Unable to connect to database.');
}
// All queries and commands go here.
$query = $db->query("SELECT id,name FROM `tb_classList`");
while ($data = $query->fetch_assoc()) {
echo '<p>'.$data['name'].'</p>';
}
$numResults = $query->num_rows;
echo '<p>'.$numResults.'</p>';
$query->free_result();
// Close $db connection
$db->close();

I use the following for debugging


// display sql errors
if ($conn->error) {
printf("<div class='error'><br/><br/>Errormessage: %s\n", $conn->error ."</div>");
}

I find this much more efficient then using the old "mysql_query() or die(mysql_error());"

CFMaBiSmAd
02-25-2008, 02:45 AM
You have got single-quotes around your column name - 'date_visited' This makes it a literal quoted string "date_visited" and then that string is being compared with the results of the DATE_SUB().

Remove the single-quotes around the column names.

Inigoesdr
02-25-2008, 02:52 AM
The call to the error method should be on the db object:

if (!$query)
{
printf("<div class='error'><br/><br/>Errormessage: %s\n", $db->error ."</div>");
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum