Trigger to delete table and then insert value with Mysql

Question

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);

MatsLindh · Accepted Answer · 2012-12-16 00:49:35Z

2

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.

answered Dec 16, 2012 at 0:49

MatsLindh

53.5k5 gold badges70 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MatsLindh Over a year ago

Why not? Do you get any error messages? What did you try? You'll have to say something useful if you want help with what you're trying to solve.

Kakitori · Accepted Answer · 2012-12-16 01:18:40Z

1

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);

answered Dec 16, 2012 at 1:18

Kakitori

9136 gold badges17 silver badges42 bronze badges

Comments

Samuel Cook · Accepted Answer · 2012-12-16 00:47:32Z

0

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.

answered Dec 16, 2012 at 0:47

Samuel Cook

16.9k7 gold badges56 silver badges65 bronze badges

Collectives™ on Stack Overflow

Trigger to delete table and then insert value with Mysql

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related