2. PHPRunner General questions
  3. Copying record from one table into an existing table
This topic is locked

Copying record from one table into an existing table

 		9/13/2013 1:28:36 PM
PHPRunner General questions
D
 dustinforehand author

Please help.

I have created a site that schedules events. The main table called Schedule has the following fields

ID (auto-incr)(primary)

User (the user who added the record)

Case (lookup field to populate the Property, State, CAID, CA, CLNA, RTCOrder fields)

Scheduled

Property (populated from Case field)

State (populated from Case field)

CAID (populated from Case field)

CA (populated from Case field)

CLNA (populated from Case field)

RTCOrder (populated from Case field)

Date CreateID - (TimeStamp)
I want to create another table called Ext_Schedule with the same fields that outside users can add to the schedule, but for me to approve

When outside users login they can access the Ext_Schedule and add to this table. When I log in and Edit the Page, I want to put a button on the EDIT page that when I click it. It will copy the the record into the Schedule table.

The issue I have fun into is that when I tried to do this before it returns error saying that ID is not unique.

I am new to PHP and mysql so any help would be great.
C
 		cgphp 9/13/2013
  • Set the ID field of the Ext_Schedule table as auto increment.
  • Remove the auto incement option for the ID field of the Schedule table.
  • When you copy a record from the Ext_Schedule table to the Schedule table make sure

    the record doesn't already exist. Perform an update if the record exists otherwise an insert.
Sergey Kornilov admin 9/13/2013

I would suggest talking a different route.
Instead of using two tables I would keep all data in the same table adding a new 'status' field. New data will be created with 'inactive' status. Admin will be able to change status of any record from 'inactive' to 'active'.
You will need to create two views of this table. First view is for admin who can see and edit all data. The second view is for regular users who can add new records and view existing record with 'active' status. You will need to modify SQL query of the second view adding

WHERE status='active'

at the end.