Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions


Posted on 2013-06-27
Last Modified: 2013-09-05
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.
Question by:the-miz
  • 4
  • 4
  • 2
  • +1
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39282467
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.
LVL 109

Expert Comment

by:Ray Paseur
ID: 39282488
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.

Author Comment

ID: 39282670
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.
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 109

Expert Comment

by:Ray Paseur
ID: 39282882
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.

Author Comment

ID: 39284019
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.
LVL 109

Expert Comment

by:Ray Paseur
ID: 39284124
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é!

Author Comment

ID: 39326167
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.
LVL 109

Expert Comment

by:Ray Paseur
ID: 39326503
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.
LVL 55

Accepted Solution

Julian Hansen earned 500 total points
ID: 39327172
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.

Author Comment

ID: 39327200
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.
LVL 55

Expert Comment

by:Julian Hansen
ID: 39327233
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.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php help 34 58
Trouble with <> 2 20
Ajax and PHP 9 29
How to structure query with count aggregate 4 14
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question