[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now



Posted on 2013-06-27
Medium Priority
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
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
  • 4
  • 4
  • 2
  • +1
LVL 84

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 111

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.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 111

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 111

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 111

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 59

Accepted Solution

Julian Hansen earned 2000 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 59

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 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