CODEWITHSUNDEEP
phpmysql
user1907121
user1907121

Reputation:

Trigger to delete table and then insert value with Mysql

How could i do a query with php and mysqli, to remove an entire table and then add new I have a form where data is received, but before adding the new data, I want to remove all data from table.
$oConni is my connection string

 $cSQLt  = "TRUNCATE TABLE approved";
$cSQLi = "INSERT INTO approved (ID_STUDENT, YEAR, APPROVED)
              VALUES (
              '" . $_POST['NSTUDENT'] . "',
                  '" . $_POST['YEAR'] . "',
                      'YES'
                                       )";

$oConni->query($cSQLt);
$oConni->query($cSQLi);

Upvotes: 3

Views: 1906

Answers (3)

Kakitori
Kakitori

Reputation: 913

You could use TRUNCATE TABLE and then your next query: 

$cSQLt = "TRUNCATE TABLE CALIFICA_APTO";
$cSQLi = "INSERT INTO approved (ID_STUDENT, YEAR, APPROVED)
          VALUES (
          '" . $_POST['NSTUDENT'] . "',
              '" . $_POST['YEAR'] . "',
                  'YES'
                                   )";
$Connect->query($cSQLt);
$Connect->query($cSQLi);

Upvotes: 1

MatsLindh
MatsLindh

Reputation: 52802

You can remove everything from a table with MySQL by issuing a TRUNCATE statement:

TRUNCATE TABLE approved;

.. which usually is the same as 

DELETE FROM approved;

.. but there are a few small differences, which you can read about in the documentation.

In the code you've pasted, please use prepared statements to avoid a sql injection attack. Never use unfiltered POST-data directly in a query!

If you want to do this as a trigger, we'll need to know a bit more about your data handling. Issuing a TRUNCATE before a INSERT will usually lead to only one row being available in the table, which seems like a weird use case for actually using a table.

Upvotes: 2

Samuel Cook
Samuel Cook

Reputation: 16828

If your looking to remove all of the data from a table you should use TRUNCATE:

TRUNCATE TABLE approved

Then you can do your SQL statement.

NOTE: This will delete all data from the table, so be careful! Also, your database user must have the ability to truncate tables.

Upvotes: 0

Related Questions