SQL insert then delete

[leeandrew]

Does anyone know the php code for this?

 

I want to insert data from a table, copy it into another table, then delete the data from the original table

All in one statement, anyone know?

[trq]

You would start with mysql's SELECT INTO TABLE syntax.

[leeandrew]

I have the following code working when i refresh the page:

 

<?
mysql_query("INSERT INTO archives SELECT * FROM plus_signup");
?> 

 

This duplicates all data in `plus_signup`

 

I tried this on another page for delete:

 

<?
mysql_query("DELETE pos FROM plus_signup WHERE pos >0");
?> 

 

I'm trying to reset the value in cell `pos` (which is in table `plus_signup`), but instead it is deleting all user data not deleting just data in `pos`

[leeandrew]

okay i have this working using statements on 2 pages.

 

Does anyone know how to check if a cell exists in a table e.g. cell1.

If it exists change name to cell2 for new data etc if not cell1

[fenway]

insert... on duplicate update...

[aschk]

Curiously; what on earth are you trying to do? This seems a rather round-about way of just doing an UPDATE statement...

Why are you adding new columns each times (e.g. add cell2 is cell1 exists).

 

By the sounds of it you skewing what you actually mean and aren't thinking about data normalisation. Each time you add a new cell your SQL SELECT query will change...

 

Explain what data you have, and what you're trying to do with it.

[leeandrew]

I have a rating website. The toplist is based on average score. I already have a code that works out the positions in the toplist based on average score. The positions go into a cell called `pos`.

Ideally i want to have a script auto-archive the positions weekly then reset the average score and positions to 0 after archiving it; ready for the next weeks voting.

So after week 1 all data goes to a cell called `week1` which it creates upon execution. Then in week 2 the script archives all the weeks data into another cell, now it cant be week 1, so i need some kind of autonumbering code to check if week1 exists, if it does, create a cell called week2 and dump data in that.

As for week 3 and so on, it needs to check if week1 exists, if it does, check week2, if it does check week3, if it doesnt... then it creates a cell called week3 and dumps it inside.

 

I dont know complex sql codes so thats why its an aboutways suggestion and method of archiving. If you know of any faster and more efficient way please suggest and i will give it a go.

Cheers

[aschk]

Ok, i think i need to sort out some terminology first.

I'm guessing that by cell you mean column? (or field).

 

It seems that you need to normalise out your information.

I'm going to guess and say that you're doing a ratings system for people or products (it doesn't matter which).

 

So theoretically you need 2 tables.

1 for the products (or people), 1 for the ratings that they are given.

 

The products table contains information about the product in question. i.e. it's id, it's name, a description, a price

The ratings table contains information about the ratings. i.e. product_id, timestamp, rating (assuming between 1 & 5).

 

Thus every time a rating is given to a product a new ROW! (not column/cell/field) goes into the ratings table. Because they are timestamped you can use SQL to grab the ratings during a particular period and calculate an average. You can also use SQL to give the product a position for the time period.

 

Hence, you DON'T need to be archiving your records into a seperate table, or adding in new columns (very bad practise).

 

To get an average rating for 1!!! product you simply calculate it in the SQL.

e.g.

SELECT product_id
      ,AVG(rating)
FROM  ratings
WHERE product_id = 7 // for example
AND `timestamp` BETWEEN SUBDATE(NOW(), INTERVAL 7 DAY) AND NOW() ;

 

for multiple products in the last 7 days

SELECT product_id
      ,AVG(rating)
FROM  ratings
WHERE `timestamp` BETWEEN SUBDATE(NOW(), INTERVAL 7 DAY) and NOW()
GROUP BY product_id

 

You can also provide a positioning based on these results using count (code to come shortly).

[leeandrew]

Thanks for the reply.

Yes, by 'cell' i mean column.

 

Currently all user data, including ratings is stored within 1 table. The ratings are continuous and just build up, so i have some old submissions with 2000 votes that are ummovable from the toplist despite them not being popular anymore; you can understand why i want a weekly system in place.

 

I'm not sure how the timestamp system works. I was going to create new columns to contain each weeks new data. How would the timestamp help pull up for example: user4's ratings for week 76?

[leeandrew]

for instance:

 

I have 10 members. they get rated on throughout the week. After the week has ended sql calculates the positions based on highest average and archives the following data:

 

member ID

rating

position

week number or timestamp- something to say exactly when this data was recorded.

 

I then need all data deleted from main table ready for fresh voting of the next week.

 

I want to be able to call up all previous voting results and member positions on a page entitled 'voting_history' which will be a week by week archive.

 

 

I'm not sure how to apply your suggested method of adding new rows instead of fields. If it can produce the above results i will use it. Can someone point me in the right direction so i can get working on it?