Need mini-code review

$sql = 'DELETE FROM nnet_produkt_bestilling ' .
'WHERE nnet_produkt_placement > ' .
'nnet_produkt_placement + \'' . date("Y-m-d H:i:s", time() +
$maxCartTime) . '\'';

This is supposed to delete all rows in nnet_produkt_bestilling whose value
in the field nnet_produkt_placement is more than $maxCartTime seconds old
($maxCartTime, in this case = 86400 seconds, or 24 hours). I am unsure if
this is the correct syntax using mySQL to do such an action upon a DATETIME
field column datatype, so if someone could look that over and see how far
out of the ballpark I really am I'd appreciate it.

Thanx
Phil

On Tue, 30 Sep 2003 00:32:31 -0400, Phil Powell wrote:
> $sql = 'DELETE FROM nnet_produkt_bestilling ' .
> 'WHERE nnet_produkt_placement > ' .
> 'nnet_produkt_placement + \'' . date("Y-m-d H:i:s", time() +
> $maxCartTime) . '\'';

The easiest way is to use MySQL's built in functions:

$sql = 'DELETE FROM nnet_produkt_bestilling ' .
'WHERE nnet_produkt_placement < DATE_SUB(NOW(), INTERVAL 24 HOURS)';


Cheers,


Andy

Thanx but don't you mean > DATE_SUB(NOW(), INTERVAL 24 HOURS)?

Phil

"Andy Jeffries" <news@andyjeffries.remove.co.uk> wrote in message
news:pan.2003.09.30.08.53.39.940884@andyjeffries.r emove.co.uk...
> On Tue, 30 Sep 2003 00:32:31 -0400, Phil Powell wrote:
> > $sql = 'DELETE FROM nnet_produkt_bestilling ' .
> > 'WHERE nnet_produkt_placement > ' .
> > 'nnet_produkt_placement + \'' . date("Y-m-d H:i:s", time() +
> > $maxCartTime) . '\'';
>
> The easiest way is to use MySQL's built in functions:
>
> $sql = 'DELETE FROM nnet_produkt_bestilling ' .
> 'WHERE nnet_produkt_placement < DATE_SUB(NOW(), INTERVAL 24
HOURS)';
>
>
> Cheers,
>
>
> Andy

On Tue, 30 Sep 2003 07:19:36 -0400, Phil Powell wrote:
> Thanx but don't you mean > DATE_SUB(NOW(), INTERVAL 24 HOURS)?

No, you want to delete all baskets older than a certain time, e.g. 24
hours old.

So, say you have the following table:

+--+-------------------+
|ID|DateCreated |
+--+-------------------+
| 1|2003-09-28 00:00:00|
| 2|2003-09-30 12:31:00|
+--+-------------------+

DATE_SUB(NOW(), INTERVAL 24 HOURS) will equate to:

NOW(): 2003-09-30 12:41:56
SUB : 2003-09-29 12:41:56

As you can see from the table, you want to delete basket ID 1, but not ID
2 (as it's was only created 10 minutes ago).

So the question you want to ask is 2003-09-28 (ID1:DateCreated) less than or greater than
2003-09-29 (SUB)?

Of course it's less than, therefore your WHILE criteria should be less
than.

If you use greater than you will be constantly deleting baskets that are
less than 24 hours old and keeping all your old ones.

Does that make more sense now or have I rambled on and confused the issue?

Cheers,


Andy

My date-math skills are absolutely nonexistent since it's pure logic and I
don't have that, so everything you said made no sense until you used an
example. I still don't get it between "<" and ">" but in this case I'll
just take your word for it.

Unfortunately, it doesn't work :( Produces a mysql_error near 'HOURS)'

I honestly have found nothing on www.mysql.com or anywhere else that can
address this problem. All I simply want to do is to delete rows from a
table where nnet_produkt_record_entered is more than 24 hours old. I
thought this would do it but it produces a mysql_error near 'HOURS)':

$sql = 'DELETE FROM nnet_produkt_bestilling ' .
'WHERE nnet_produkt_placement < DATE_SUB(NOW(), INTERVAL 24
HOURS)';

Any ideas, sorry I'm fresh out as usual.

Thanx
Phil

"Andy Jeffries" <news@andyjeffries.remove.co.uk> wrote in message
news:pan.2003.09.30.11.44.51.130321@andyjeffries.r emove.co.uk...
> On Tue, 30 Sep 2003 07:19:36 -0400, Phil Powell wrote:
> > Thanx but don't you mean > DATE_SUB(NOW(), INTERVAL 24 HOURS)?
>
> No, you want to delete all baskets older than a certain time, e.g. 24
> hours old.
>
> So, say you have the following table:
>
> +--+-------------------+
> |ID|DateCreated |
> +--+-------------------+
> | 1|2003-09-28 00:00:00|
> | 2|2003-09-30 12:31:00|
> +--+-------------------+
>
> DATE_SUB(NOW(), INTERVAL 24 HOURS) will equate to:
>
> NOW(): 2003-09-30 12:41:56
> SUB : 2003-09-29 12:41:56
>
> As you can see from the table, you want to delete basket ID 1, but not ID
> 2 (as it's was only created 10 minutes ago).
>
> So the question you want to ask is 2003-09-28 (ID1:DateCreated) less than
or greater than
> 2003-09-29 (SUB)?
>
> Of course it's less than, therefore your WHILE criteria should be less
> than.
>
> If you use greater than you will be constantly deleting baskets that are
> less than 24 hours old and keeping all your old ones.
>
> Does that make more sense now or have I rambled on and confused the issue?
>
> Cheers,
>
>
> Andy
>

Phil Powell wrote:

> My date-math skills are absolutely nonexistent since it's pure logic and I
> don't have that, so everything you said made no sense until you used an
> example. I still don't get it between "<" and ">" but in this case I'll
> just take your word for it.
>
> Unfortunately, it doesn't work :( Produces a mysql_error near 'HOURS)'
>
> I honestly have found nothing on www.mysql.com or anywhere else that can
> address this problem. All I simply want to do is to delete rows from a
> table where nnet_produkt_record_entered is more than 24 hours old. I
> thought this would do it but it produces a mysql_error near 'HOURS)':
>
> $sql = 'DELETE FROM nnet_produkt_bestilling ' .
> 'WHERE nnet_produkt_placement < DATE_SUB(NOW(), INTERVAL 24
> HOURS)';
>
> Any ideas, sorry I'm fresh out as usual.

Use 'HOUR' instead of 'HOURS'.

--
Seks, seksiæ, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa ) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kszta³ty... http://www.opera.com 007