Query to update a table with gontents added more than 10 minutes ago.

I don't understand why this doesn't work as I want...

[mrnabla]

Hi all,
I want to insert into a table values from another one.
I need to select only contents inserted before of ten minutes ago,
and to order this from the more recento to less one.
The query I use is the following:

<?

$offset_ldg_cron_script=7; /* dued to server timezone offset*/
$seconds_of_tolerance=600; /*10 minutes*/
$date_10_minutes_ago=date('Y-m-d', time()-$seconds_of_tolerance + $offset_ldg_cron_script * 3600);
$time_10_minutes_ago=date('H:i:s', time()-$seconds_of_tolerance + $offset_ldg_cron_script * 3600);

$query_var_appoggio=" SELECT DISTINCT id_content,value
FROM my_table
WHERE content_insert_date <='".$date_10_minutes_ago."'
AND content_insert_time <='".$time_10_minutes_ago."'
ORDER BY content_insert_date DESC,content_insert_time
DESC LIMIT 30";

$query_result = mysql_query($query_var_appoggio);

>

Some additional info:
into table, the fields content_insert_date and content_insert_time
are defined as: date and time fields, and in the same format of
compared values ( eg; '2008-06-25' , '14:26:23')
If I remove third and forth line in the query
WHERE content_insert_date <='".$date_10_minutes_ago."'
AND content_insert_time <='".$time_10_minutes_ago."'
the query goes well but so I can't filter contents avoiding to insert too recent ones.

Is there somebody that can help me?

Thanks in advance!

lore

(sorry for my english)

[npwsol]

Personally, I do not use the Date and Time fields. I use an Integer and I store a timestamp from the php time() function

I don't see any problems with your code, but I know if you change methods it would be much easier (You would not have to keep track of both data and time, only timestamp). The problem with the query may be the Data and Time columns; they take data in a specific way, and I'm not sure what format it should be using.

If you switch to the timestamp function, you need only search for "WHERE content_insert_timestamp <= " . (time()-600)

If you cannot do that change, check the format of the date and time columns. I think they use the same format, and PHP variables do not match the format, I think.

[mrnabla]

Thankyou for the answer, npwsol.
The problem is that I am not allowed to change
date and times into tables.
I developer an extremely wide php code, and I need to
use this representation.
Please note that into php code too I do some query
like the above one, but without problems.
Mmm...
I thought to possible solutions.
the first...
avoid to use <= but only = in some way
the second...
apply some "internal" mysql function in order to
convert data and time as strings.
mmm...
a possible third way:
force $date_10_minutes_ago / $time_10_minutes_ago
to be of the same format of mysql.
Is it possible to convert $date_10_minutes_ago / $time_10_minutes_ago
into the format you suggest to me using an internal function
of mysql to convert on-the-fly date and time into timestamp?
I mean... using timestamp without need to convert
the native table format?

Thanks in advance.

lore

(i think that I have to search the solution in MySQL website...
...there are a lot of mistakes in MySQL engine (like in every kind of software, of course...)
and the list of bugs is always very long...)
Sometimes the problem is caused by "lower-layer" bugs.

lore

[eelixduppy]

Maybe try a query like this?:

$query_var_appoggio=" SELECT DISTINCT id_content,value
FROM my_table
WHERE content_insert_date <= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
AND content_insert_time <= DATE_SUB(NOW(), INTERVAL 10 MINUTES)
ORDER BY content_insert_date DESC,content_insert_time
DESC LIMIT 30"; 

[mrnabla]

Thank you, eelixduppy
I think I'll go to understand what the query does
to undestand how to modifi it to my needs.
Then I'll try it.
Cleary, when (if) I will have the solution to this
(and many other question I posted in this forum)
I'll give a feedback to help others with similar
questions.
Thanks!

lore