[ Pobierz całość w formacie PDF ]
.All the information willbe coming from an HTML form.Other FilesIf you understand the functions in the preceding section, the other files will be apiece of cake.In fact, they re so easy we ll only look at one.index.phpAs you can see, this file contains almost nothing.The display_topic() functiondoes all the heavy lifting.SummaryIf you would like to see how the rest of the code comes together, take a look at theaccompanying CD.The other files are well-commented and should be relativelyeasy to follow.You should come away from this chapter with the understanding of two con-cepts.The first is recursion.Recursion is a nifty tool that can be very helpful attimes.If you d like to see another example of recursion at work, check out therecurse_directory() function in Appendix G, which will display all of the con-tents on a Web server.The other key thing is the way we went about organizing the data.We didn t fol-low a strict normalization procedure, like the one described in Chapter 1.Here wewere more concerned with what gets the job done.In the end that s what all usapplication developers are trying to do, right? 3537-4 ch13.f.qc 12/15/00 15:25 Page 331Chapter 13Problem Tracking SystemIN THIS CHAPTERDesigning a tracking systemProtecting yourself from redundant data.Creating a site that has both publicly available and private portions.GOT PROBLEMS? Don t worry, we ve all got problems.Relationships falter, bossesmake capricious demands, and family oh, we all know about family.Sadly, in thecrazy lives that we all live, PHP and MySQL can do nothing to make yourgirl/boyfriend love you more or make your dealings with your parents or in-lawsany easier.But it must be said that no scripting language or relational database isbetter equipped in these areas.But if you re working for a company that sells or otherwise dispenses goods, it isa virtual guarantee that someone somewhere is going to be unhappy with what heor she has received.When that person complains you are going to want to have aplace where you can record the problems and the steps required for resolution.The problem-tracking application in this chapter can be used for that purpose.What we have here is fairly generic, and depending on the goods involved with yourbusiness, it is likely that you are going to want some fields that apply to your specificproducts.Anyhow, this application should get you moving in the right direction.Determining the Scope andGoals of the ApplicationThis problem-tracking system should have aspects that are publicly available andothers that only someone with the proper authorization can view.It makes sense tohave a form that users can access over the Web to report their problems.Alternatively, someone on the support staff should be able to report problems forexample, while taking a phone call from a dissatisfied customer.331 3537-4 ch13.f.qc 12/15/00 15:25 Page 332332 Part IV: Not So Simple ApplicationsOnce the problem is entered, it should be tracked by the staff.Each action madein the attempt to solve the problem should be noted.And the tracking should havea public and a private realm actions that you want the user to see must be differ-entiated from those that you do not want the user to see.Those with issues should be able to keep track of their problems in two ways.They should be e-mailed whenever an update is made to their case that should bepublicly viewable, and it should go without saying a Web page detailing theirproblem should be available.What do you need?The first thing you need is a form into which people can enter their complaints.What we present in Figure 13-1 is fairly generic; remember that for your own appli-cations you will probably want to add information regarding specific products.Figure 13-1: Problem entry form 3537-4 ch13.f.qc 12/15/00 15:25 Page 333Chapter 13: Problem Tracking System 333Once a problem is entered, there must be a place for the staff to work on thecomplaint.It should include all of the information about the user, the history of thecomplaint, and a place to enter new information.This problem update form wouldlook something like the one in Figure 13-2.Figure 13-2: Problem update formThe support staff members need a home, a place where they can log in and seeboth the unassigned tasks and those that are assigned to them and are still open.The staff page would look something like the one in Figure 13-3.If you want to see if any of your users are hypochondriacs, you can use the userhistory page in Figure 13-4, which lists all problems associated with a user. 3537-4 ch13.f.qc 12/15/00 15:25 Page 334334 Part IV: Not So Simple ApplicationsFigure 13-3: Staff pageFigure 13-4: User history page 3537-4 ch13.f.qc 12/15/00 15:25 Page 335Chapter 13: Problem Tracking System 335What do you need to prevent?There is nothing terribly unique about what you need to prevent here.In fact, if youdecide to put an application like this into production, you might want to take moresafeguards than we show here.We ll make some suggestions along the way.Designing the DatabaseAs you can see from Figure 13-5 the problems table is at the center of the schema.customers problems history entry_typescustomer_id problem_id entry_id entry_type_idfirstname customer_id problem_id entry_typelastname status_id entry_type_idaddress owner entered_byaddress2 summary source_idcity problem entry_dtstate entered_by noteszip source_idzip4 entry_dtusername_callsemail modify_dtday_areaproblem_idday_prefixentry_dtsourcesday_suffixday_extsource_idday_startsourceday_endeve_areaeve_prefixchar(3),eve_exttracking_admin staffeve_starteve_endusername usernamepassword passwordstaff_name staff_namestatusactive activestatus_idstatusFigure 13-5: Tracking system schemaEach customer can have one or many problems.The history table records thesteps taken to remedy the problem or problems. 3537-4 ch13.f.qc 12/15/00 15:25 Page 336336 Part IV: Not So Simple ApplicationsThe status table is a lookup table, containing the possible states of a problem;notably open, closed, in processing, etc.The sources table is another lookuptable that records where the problem was originally recorded.If a user enters acomplaint over the Web, the sources table will record that; complaints received bythe support staff might originate from a phone call, e-mail, or flaming arrow.The entry_types table notes whether a specific item in the history table should bepublic or private.If it is private it will not be available on the Web page when theuser comes to view the progress of the problem and an e-mail will not be sent to theuser when there is an update.The public updates will be viewable and the userwill receive e-mail notification.There is something a bit strange in this schema; namely, the calls table.The pur-pose of the calls table is to store the most recent entry for each call that has anowner.If you look at this schema, you might wonder why this is necessary.Afterall, there are relationships between staff, problems, and history.Therefore, thereshould be a way to join these three tables to get the information you are after.Normally you would use sub-selects for something like this.select problem.*, history.*, staff.*from problem , history, staffwhere problem
[ Pobierz całość w formacie PDF ]