Alex Walker-Ingham Alex Walker-Ingham - 1 year ago 45
SQL Question

How can I Select duplicate results, insert the results to a new table and delete them from the original table? (phpmyadmin)

I have 2 tables, both with the same column names that look like this

enter image description here

I have an app that is used for clocking in/out. A user goes up to a tablet and selects there name, the data is then inputted into table 'clocktable' as shown above.

What I need to happen is when a user presses their name for a second time (to clock out) it detects that there is already a value in the table with the same employeename value, and moves both clocks to a different table 'oldclocks' so that a record is kept of clocking in/out times. The reason it needs to be in a separate table is I have a web page that displays the 'clocktable' table so we can determine who is in the building.

How can I go about doing this?
I can execute

SELECT DISTINCT EmployeeName, time FROM clocktable ORDER BY EmployeeName

from within myphp and then execute

INSERT INTO oldclocks SELECT Employeename, Department, time FROM clocktable;

Afterwards, but I cannot seem to execute them both at the same time in order to get them into a php script. Also is there a way I can delete the select results from the 'clocktable' at the same time?

Help with this would be greatly appreciated.

Answer Source

2 tables are not nessesary. And it is bad idea.

Why dont you simply add boolean IN_BUILDING which will have 0 ->Not in building and 1->In building. Then you can do:

SELECT EmployeeName FROM clocktable WHERE IN_BUILDING = 0; which will show you all employees who are not in building


SELECT EmployeeName FROM clocktable WHERE IN_BUILDING = 1; which will show you all employees who are in building that moment.

Its easier also because you can change it value easy on click.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download