PHP SQLite3 Query INSERT, DELETE & Redirect

Question

I've spent the last couple of days grappling wit the simple concept of using a php script as Save button. When pressed from a webpage the button will INSERT data from table A to table B then delete table A and redirect to back to my main index.html.

<?php
try {
    $db = new PDO('sqlite:/srv/db/data.db');
}

    $db->exec("INSERT * INTO Archive FROM resultstbl");
    $db->exec("DELETE * FROM resultstbl") 
 unset($db);
?>

So far I could use some help with this PHP query, as well as any guidance.

Luís Sousa · Accepted Answer · 2011-09-22 11:47:33Z

1

I would do something like this:

<?

if(isset($_POST['but1'])) // this checks if the button is clicked
{
   $db = new PDO('sqlite:/srv/db/data.db');  // I assume this is working fine
   $db->exec("INSERT INTO Archive SELECT * FROM resultstbl"); // tables must be equivalent in terms of fields
   $db->exec("DELETE FROM resultstbl") // You want to delete the records on this table or the table itself? This deletes the records

   header("Location: index.php?page=home"); // This will only work if you didn't output anything to the screen yet. If you displayed something it will fail
   die();

}

?>

<form action="sql.php" method="POST"> <!-- Assuming this page is sql.php -->

   <input type="submit" name="but1" value="GO!"/>
</form>

You can check the syntax for the insert and delete statments for SQLite here:

http://www.sqlite.org/lang_insert.html

http://www.sqlite.org/lang_delete.html

answered Sep 22, 2011 at 11:47

Luís Sousa

781 silver badge7 bronze badges

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

3 Comments

bikerben Over a year ago

Thank you very very much, I was begging to pull my hair out over this. Just one quick additional question though.... the script cycles through and deletes the contents of my table just as I wanted but it has not copied the contents of resultstbl into the Archive. Could this be because there is a key in the Archive and not matching column in the resultstbl?

Luís Sousa Over a year ago

definitely, the tables must have exactly the same structure to be able to select from one directly to the other. To get around the problem you are having now you should do something like this: insert into Archive (field1, field2, field3) (select field1, field2, field3 from resultstbl)

bikerben Over a year ago

Just a foot note, the answer to my question above is yes both fields need have the exact same fields in for the mentioned INSERT syntax to work.

Collectives™ on Stack Overflow

PHP SQLite3 Query INSERT, DELETE & Redirect

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related