reset database settings?

[gangsterwanster1]

How can i from a command button re-set a database so no entries are entered?

 

[gangsterwanster1]

so can anyone help me figure out how from php to clear a database?

[lemmin]

this simple query will remove all rows:

DELETE FROM table

[ignace]

this simple query will remove all rows:

DELETE FROM table

 

Wrong. It's:

 

TRUNCATE TABLE tableName

[lemmin]

Wrong. It's:

 

Why is that wrong?

[Philip]

this simple query will remove all rows:

DELETE FROM table

 

Wrong. It's:

 

TRUNCATE TABLE tableName

 

Wrong.

 

Both ways are acceptable (give you're using MySQL)

TRUNCATE TABLE empties a table completely. Logically' date=' this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.[/quote']

 

The main difference, besides performance, is truncate will resest the auto_increment counter. See http://dev.mysql.com/doc/refman/5.0/en/truncate.html

[lemmin]

I forgot that some flavors don't allow that. I think I remember Oracle requires you to select a row when using delete. So just a star would make it more universal.

DELETE * FROM table

 

[ignace]

this simple query will remove all rows:

DELETE FROM table

 

Wrong. It's:

 

TRUNCATE TABLE tableName

 

Wrong.

 

Both ways are acceptable (give you're using MySQL)

TRUNCATE TABLE empties a table completely. Logically' date=' this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.[/quote']

 

The main difference, besides performance, is truncate will resest the auto_increment counter. See http://dev.mysql.com/doc/refman/5.0/en/truncate.html

 

Then I'm right as the question was:

 

so can anyone help me figure out how from php to clear a database?

 

delete will not entirely clear the table truncate does.

[Philip]

I forgot that some flavors don't allow that. I think I remember Oracle requires you to select a row when using delete. So just a star would make it more universal.

DELETE * FROM table

 

Almost correct, no * is needed, because you cannot specify specific columns in a DELETE command. You CAN use a WHERE clause though, both of the following commands are acceptable and will delete every row:

DELETE FROM table WHERE 1
-- or, alternatively:
DELETE FROM table

 

 

 

Then I'm right as the question was:

 

so can anyone help me figure out how from php to clear a database?

 

delete will not entirely clear the table truncate does.

 

In a way yes, but it also depends on the OP's definition of "clear a database", do they just want to delete all the rows, or do they want to completely reset all aspects of it?  Remember not every table has an auto incrementing field, so DELETE FROM table might also be a viable solution. Not trying to be an ass to you or anything by all means, just wanting to make sure everyone has their facts straight on what the best solution for the OP's specific solution.

[ignace]

MSDN:

 

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

 

MySQL:

 

For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE TABLE  by deleting rows one by one. As of MySQL 5.0.3, row by row deletion is used only if there are any FOREIGN KEY constraints that reference the table. If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.

 

TRUNCATE is faster