Solved

MySQL PHP

Posted on 2013-06-27
11
382 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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 51

Accepted Solution

by:
Julian Hansen earned 500 total points
Comment Utility
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
Comment Utility
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 51

Expert Comment

by:Julian Hansen
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now