Solved

MySQL PHP

Posted on 2013-06-27
11
402 Views
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.
0
Comment
Question by:the-miz
[X]
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
11 Comments
 
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.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39282488
Possibly you want to learn about cookies.  Check the "remember me" functionality in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2391-PHP-login-logout-and-easy-access-control.html

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.
0
 

Author Comment

by:the-miz
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.
0
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
LVL 110

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.
http://www.sitepoint.com/books/phpmysql5/
0
 

Author Comment

by:the-miz
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.
0
 
LVL 110

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é!
0
 

Author Comment

by:the-miz
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`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=926 ;

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.
0
 
LVL 110

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.
0
 
LVL 57

Accepted Solution

by:
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.
0
 

Author Comment

by:the-miz
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.
0
 
LVL 57

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.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

695 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