Need help making a script that moves data into acrhive table

[badeand]

Hi,

 

I'm kinda new to php/mysql

 

And i wrote a simple script to store sales lead for a buisness and they are posted out into a table after they are submitted in a form.

 

The page where you can see all your sales lead have an option to delete sales lead, but i need to write a script that can archive the specific sales lead into an other mysql table.

In my database i've created an excactly identical table to the original to store the archived rows.

I need it to be a link behind each row that says archive and moves the data into the archive table.

 

How can i do this?

[johnsmith153]

 

Are you talking about something like:

 

1. New sales leads submitted in a form

 

2. Display ALL current sales leads to user

 

3. Click link to delete a sales lead

 

Then when it is deleted, a copy of this is stored in another table.

 

 

------

 

 

 

If this is the case you could do one of these three options:

 

(1) Still use just one db, but have an extra field that prevents the record being displayed when a user "deletes" it - in other words it is just prevented from the user seeing it - so no need for extra, duplicate tables.

 

(2) When you add the record, add it to both tables - with one never being touched by a user. Then when the record is deleted from db1 it is already in db2.

 

(3) When deleting, get details of what you are deleting and then add this record to the second table.

 

[badeand]

What i'm talking about is like this

 

1. New sales leads submitted in a form

 

2. Display ALL current sales leads to user

 

3. Click link to delete Sales lead

 

OR

 

4. Click archive to remove the lead from the "display all leads" into archive (like task is done)

 

So my sellers can after dealing with this client push archive and i can see that this task is done.

 

But i cant figure out a way to move these leads into the archive and at the same time remove them from the "Display all" page..

 

 

[johnsmith153]

I would do my option 1 and forget the duplicate table.

 

Simply have an extra field called "done"

 

by default all "done" fields are blank when the record is created.

 

When you click 'Archive', the record is updated (SQL UPDATE) and a '1' is stored in the "done" field for that record.

 

When displaying to users, don't display any where there is a 1 in the "done" field

[badeand]

Could you help me how i could make this link with archive? how will the sql update query look like?

 

Would this update query be in the submit form file or in the form_process file?

 

and how do i make the records with '1' invisible in the display all page?

 

 

[jeff5656]

I do the same thing.  For each record I have a column called "archive".  It is either a y or a n.  Then when you query the table you say WHERE archive = 'n'

so that only records not archived are displayed. 

If a user wants to archive a record they hit a form button and the action script would update the table and SET archive = 'y' WHERE id = $POST['id'].

Then you only need one table and use query to limit what reords are displayed.

[badeand]

Then i would probably need to edit my display page because i use this method to post the differents field in a table.

 

<td>";

echo $row['email'];

echo "</td>

 

and to not show the archived records my form needs to be "auto generated" in a way? I probably need to study this stuff a bit more.

But i already figured alot of this stuff out just by reading what you are posting

 

[johnsmith153]

1. Delete the second duplicate table - this is not needed.

 

2. Add a field to the main table, and call it something like 'done' (for task done / completed etc.)

 

3. When you click 'Archive' do a SQL query like this:

$sql = sprintf("UPDATE $tableName SET done='1' WHERE uniqueID LIKE '%s'",mysql_real_escape_string($uniqueID));

 

4. When displaying add this to the END of your current SQL query

AND done NOT LIKE '1'

 

This should do it.

 

 

[badeand]

I DID IT, I DID IT!!!

 

Genius!!!

 

Thanks alot for your help

 

I didn't just do it, I can see why. And I learned something really handy