# Moving Mysql Records Across Tables



## daveclose (Jul 24, 2008)

I have some records in, with for instance, ID , POST, TIME

ID - Auto_increment
POST - VARCHAR
TIME - on_update_current_timestamp

I have created a table where I can delete each record.
I want to create a "deleted items" so it would move the record to the deleted items table deleting it from the other, I would then want a restore button which would move the record back in the same place as it was in the other table.

How would I move the record across the tables?
How would I make the record move back without affacting the TIME and in the same place as it was deleted from?
thanks


----------



## daveclose (Jul 24, 2008)

can anyone help?


----------



## TheRobatron (Oct 25, 2007)

You should just be able to read the row from the table and write it to a 'deleted' table. When you restore the row, the date and id will remain the same because you don't reuse the functions, you take the values that are already there. The auto_increment() means no other record will have the same id, so the row will slot back where it started.


----------



## daveclose (Jul 24, 2008)

ahh thanks helpful 

how would i read it and then write it to the other table, is there not a move?
i am assuming that i would read it, write it then delete it.

any more help on the subject?


----------



## TheRobatron (Oct 25, 2007)

There is no move statement in SQL, so yes, you would have to read, write and delete it each time, but you could easily write a function to do that.


----------



## daveclose (Jul 24, 2008)

thanks
I want a efficient way of counting records in a table, i tryed using

```
SELECT COUNT(*) FROM blah
```
but I cant work out how to print out the result a php page, i think i could use mysql_num_rows how ever from tests have ran this seems to be inefficient
can anyone help


----------



## TheRobatron (Oct 25, 2007)

You need to assign the count() funtion a name:

```
SELECT COUNT(1) as "Num of rows" FROM blah
```
This code will return a field called _Num of rows_. Note the COUNT(1) - this is more efficient than COUNT(*) by only returning a boolean value when a row is found, but it shouldn't really make much difference for small databases


----------



## daveclose (Jul 24, 2008)

thanks, would i then need to but this in to an array to echo this out or is there a different/better way?


----------



## TheRobatron (Oct 25, 2007)

You would just echo it as if it was a normal SELECT query, so it would be returned from the MySQL database as an array.


----------

