delete all the entries

[nakresimin]

is it possible to have a script that when i run that script it will log into a database and delete all the entries from specific database tables?

if it is how would that look for database name "test" and "table 1, table 2, table 3, table 4 and table 5"

thank you

[StephNicolaou]

Yes, connect to your database as you would normally, connection paths, etc.

And use the SQL statement, DELETE * FROM TBLNAME1, TABLENAME2 ...

How would that database look? The tables would just be empty and you would have to make sure you also delete foreign relational data to ensure you don't get errors in the front end.

Hope that helps :)

[nakresimin]

datebase stores banned, filtered ips etc and end of the day i want to delete them.

actually what would be great is copy that table and rename it say "archive", and end of the day move all the ips from table 1 to "archive"

what do you mean with? delete foreign relational data thank you

[nakresimin]

would it look like this?

<?php
/**
 *test1
*/

// Configure for Database Connect
define ("host","XXX.XX.X.XXX");
define ("database","database");
define ("username","username");
define ("password","password");

 $query = 'delete * FROM table1, table2,table3';


?>

[StephNicolaou]

To move data from a table into another table called archive, you have to write a SQL query, e.g.

SELECT * FROM TBL into Archive

And it will create the table Archive on the fly if it's not already created.

'Delete foreign data' is my short way of saying foreign keys, E.G. Employee table has primary keys EmployeeID and a table called EmployeeTimetable will have the foreign key EmployeeID to indicate whos timetable is whos - If you delete the primary key data from the Employee table, EmployeeTimetable will have nothing to refer to...And give you errors as the employee ID will not mean anything to it.

You connect to a database via. the below: (w3schools)

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
?>

Here are some tutorials on how to connect to a database using PHP:

http://www.tizag.com/mysqlTutorial/mysqlconnection.php
http://php.net/manual/en/mongo.tutorial.php
http://www.w3schools.com/php/php_mysql_connect.asp

That should get you started :)

[nakresimin]

woudl it look like below for only deleting

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_query("DELETE * FROM table1,table2,table3");


mysql_close($con);
?>

and removing then deleting

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_query("SELECT * FROM TBL1 into Archive");
mysql_query("DELETE * FROM TBL1");


mysql_close($con);
?>

would this do it?

thank you

[Biiim]

woudl it look like below for only deleting

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }


mysql_query("DELETE * FROM table1,table2,table3");


mysql_close($con);
?>

and removing then deleting

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_query("SELECT * FROM TBL1 into Archive");
mysql_query("DELETE * FROM TBL1");


mysql_close($con);
?>

would this do it?

thank you

The deleting is correct.

On the seond one you want something like this:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con){
	die('Could not connect: ' . mysql_error());
}
if(mysql_query("INSERT INTO `Archive` SELECT * FROM TBL1",$con)){
	echo "Data Archived<br/>\r\n";
	if(mysql_query("DELETE * FROM TBL1",$con)){
		echo "Data Deleted<br/>\r\n";
	}else{
		echo "Delete Failed: ".mysql_error($con)."<br/>\r\n";
	}
}else{
	echo "Data Failed to Archive: ".mysql_error($con)."<br/>\r\n";;
}
mysql_close($con);
?>

The if will make it so it only deletes if the archive is successful

[nakresimin]

it did not work
the datebase was not noted and after i did that i get this error Could not delete data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM TBL1' at line 1

[Biiim]

Oh sorry it sounded right, i don't use delete often.

delete syntax is:

DELETE FROM `table`

I usually use DELETE FROM `table` WHERE something = 'something' , im assuming you don't need a WHERE clause if you don't want it

There is also truncate which also resets the auto_increment back to 1

TRUNCATE `table`