[SOLVED] Date problem

[FarhanKhalaf]

I have a table nordstromlisting with column datecreated filled with a unix timestamp of when the listing was created. I essentially want to delete all listings older than 5 days (432000 seconds), so I created the following sql. However, it's not working.

 

Anybody see what I'm doing wrong?

 

Thanks.

 

		$timenow = date("Y-m-d");
		$time = strtotime($timenow);

		$movetohistory = mysql_query("SELECT * INTO nordstromhistory FROM nordstromlisting WHERE '$time' - datecreated > 432000");
		$deltefromlisting = mysql_query("DELETE * FROM nordstromlisting WHERE '$time' - datecreated > 432000");	

[Ken2k7]

There's no such thing as SELECT * INTO .... At least I don't think so.

 

Change this:

$movetohistory = mysql_query("SELECT * INTO nordstromhistory FROM nordstromlisting WHERE '$time' - datecreated > 432000");

 

To:

$movetohistory = mysql_query("SELECT * INTO nordstromhistory FROM nordstromlisting WHERE '$time' - datecreated > 432000") or die(mysql_error());

 

That should display some error if there is one.

[FarhanKhalaf]

You're right, the syntax is messed up becuase for one INTO doesn't show up in the proper color, and the error it gives me is 'undefined variable: nordstromhistory'

 

However, the w3 http://www.w3schools.com/sql/sql_select_into.asp does it that way?

 

Thanks in advance.

[Mchl]

Just read what you've written in your first post. You want to DELETE rows, so you should use DELETE query.

 

SELECT .. INTO is used for moving data from one table to another.

 

 

[FarhanKhalaf]

Excuse me, I should of clarified better:

 

I want to MOVE listings older than 5 days into nordstromhistory, AND delete them from nordstromlisting.

 

Sorry about that.

[Mchl]

Heh... yeah... I should've noticed that. Too quick to reply too early in the morning

 

Ok... so is any of your queries working, or do both of them fail?

 

 

[FarhanKhalaf]

Only the first one fails

 

other one doesn't give an error.

[Mchl]

A strange bit is that ''undefined variable: nordstromhistory'' is a PHP notice, not a MySQL error... Is that all you get?

 

[edit]

Seems I was wrong about what SELECT INTO is about

http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html

 

What you need is  INSERT ... SELECT

 

INSERT INTO nordstromhistory SELECT ... FROM nordstromlisting WHERE '$time' - datecreated > 432000

 

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

[FarhanKhalaf]

Yep :-\

[FarhanKhalaf]

I tried

 

		$movetohistory = mysql_query("INSERT INTO nordstromhistory SELECT * FROM nordstromlisting WHERE '$time' - datecreated > 432000") or die(mysql_error());

 

and got the following error:  Duplicate entry '1' for key 1

 

hmm...

[Mchl]

Probably a conflict in primary keys of both tables. That's why you should explicitly state which columns you're moving.

 

Or you can use ON DUPLICATE KEY UPDATE