I have two tables exactly the same except for one is called drafts and the other is final.

I have a form page that upon focusout of its input fields, runs a mysql command to either update (if entry exists in drafts) or inserts a new entry. When the form is completed to the user's satisfaction they can click the final copy button which moves the draft entries from the table drafts to the table final.

If someone closes the window and reopens it later, I want the form to be prepopulated with what had been saved in the table drafts and any entries that are not found to be taking from the last known entries in the table final.

What I basically have a users creating a report. They want to know if something happens to the window and closes, that they can recover to the last known entered fields. The reports are all finally saved in the table final. So I need each to be a SELECT command for the last known entry.

Hope that makes some sort of sense. I thought maybe UNION ALL would help but any help would be appreciated.
Who is Participating?
Julian HansenCommented:
Can I ask why you are managing both a drafts and a final?

Why not put all the records in one table and just have a status field that has a value that says whether or not the record is in draft or final form - it will make your code a lot easier.

I assume you have also looked at the ON DUPLICATE KEY option of insert so you can do the insert / update in the same query.

I have not read the full thread properly so I might have missed something here - apologies to all if I have jumped on bases already covered.
Dave BaldwinFixer of ProblemsCommented:
Your problem is two-fold.  First, you must be able to identify the user and second, you must be able to retrieve their work from the table.  You need both some kind of login system and a method to identify their work in the drafts table to be able to SELECT it.
Ray PaseurCommented:
Possibly you want to learn about cookies.  Check the "remember me" functionality in this article.

Once you understand that part, you will be able to place a cookie on the client browser that can point to a row in the drafts table.  The client can leave the browser and return later, and your script will be able to use the returned cookie to find his draft.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

the-mizAuthor Commented:
I identify the user as the logged on windows user. Its on an intranet system. I am just wondering what mysql command will be used to quickly check if any drafts are available and if not get the old final copy.

It's basically a spreadsheet. Getting values for each specific row, if a row has a draft, pull its data otherwise get the most recent final numbers.
Ray PaseurCommented:
You would use a MySQL SELECT query.  The WHERE clause would be the user's identifier, which you would have stored in the data base at the time (and in the same row) you stored the draft document.  A good book that will tell you about how to use PHP and MySQL together is available from SitePoint.
the-mizAuthor Commented:
Wasn't looking for a book as I know PHP and MySQL basics. I was looking for someone to show me a simple SELECT query to do what I need.

I saw the following code somewhere online:

SELECT * FROM finals WHERE user='username' AND NOT EXISTS (SELECT * FROM drafts WHERE user='username') 

Open in new window

I don't think an altered code similar to the above will work. I want to get the most recently added entries from the drafts database but if none exist pull the most recent entry from the final database.
Ray PaseurCommented:
OK, please post the CREATE TABLE statement for the relevant tables.  You may want to give us details about the meaning of the column names, unless they are obvious.  It should be easy to get you a good set of queries.

Also, never use SELECT * without a LIMIT clause.  If you ever want to get a job as a professional programmer you don't want that on your résumé!
the-mizAuthor Commented:
CREATE TABLE IF NOT EXISTS `job_cost_drafts` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `job` varchar(10) NOT NULL,
  `datetime` datetime NOT NULL,
  `costcode` varchar(11) NOT NULL,
  `category` varchar(100) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `variance` decimal(15,2) DEFAULT NULL,
  `entered_by` varchar(255) DEFAULT NULL,
  `estimate` decimal(15,2) DEFAULT NULL,
  `jobcost` decimal(15,2) DEFAULT NULL,
  `commitment` decimal(15,2) DEFAULT NULL,
  `invoiced` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`id`)

Open in new window

I was looking for one code but went with two commands. I first check the first table (drafts) for any entry relating to the report matching the user (entered_by) and their job...  if nothing exists, I then check the finals table to see if there is any previously saved work.
Ray PaseurCommented:
I'm confused.  Your earlier query made reference to finals and drafts but the CREATE TABLE makes reference to job_cost_drafts.

If I were dealing with this issue, I think I would step back from this detailed problem and create the SSCCE to help me understand the essential moving parts.
the-mizAuthor Commented:
There are two tables, one called drafts and another final...  both setup the same with exact fields.

The status field idea is probably the best way to do it.  I had it setup like that before and ran into a problem...  which is why i tried to do it another way.  Don't ask what I came across, I can't remember now.

The insert/update in one query with ON DUPLICATE KEY sounds interesting, i have not looked into that.
Julian HansenCommented:
I had it setup like that before and ran into a problem...  which is why i tried to do it another way
I would consider re-opening that option - I believe (Based on what you have told us) that it is the best way to go. We use it all the time very successfully.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.