• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

PHP - Store Conversation Thread Allow Response Via Email or on Web

Hello Experts,

I'm working on a helpdesk/usermanagement tool for my boss and I'm trying to get my head around the best way to accomplish the following:

The help desk will allow users to ask a question. The question can either be asked after they have logged into their online account (think like a private message on a forum). Or they should have the option to send their question to a specific email address i.e. support@ourdomain.com.

Once the request comes in it should be stored in the mysql table with the associated question id. Then it will be assigned to an employee and they can reply to the person with the answer to their question.

In my head, all of the above is very easy to do if it is kept all online (i.e. they can only utilize the help desk if they are logged into their account on our site). However, we need to bring the conversation to their inbox.

So ideally, if one of the techs responds the response should show up in their online web message account AND send an email to their email address.

Then the user should have the choice to click "reply" in the email and send their response. That response should then be added to the mysql table and become part of the threaded conversation. If the user attaches an attachment then that attachment should be moved to a folder on the webserver and the attachment's link should be stored in an attachment column for that reply's row in the mysql table. Essentially the web messages and email conversations need to stay synchronized.

Any resources/code examples/or theory of how best this would be accomplished would be much appreciated.

As an aside, the current web app is written with CodeIgniter2 with a MySQL backend.

Thank you!
0
w00tw00t111
Asked:
w00tw00t111
  • 4
  • 2
1 Solution
 
MattCommented:
Hi,

I've never created something like this however I could put forward a couple of suggestions from systems that I've used at my employers.

Essentially, the online part is fairly straight forward and similar to private messages on a forum etc.  You could provide an upload function for the user to include a file with their response, and this could be emailed to the technician handling the case or simply left available online etc.

The tricky part comes with allowing the user and technician to continue the conversation via email.  The majority of system allocate a unique identified to the original message, like a case number or support ticket number or reference number whatever you want to call it.  I'll call it a ticket number.

What you would need to do is inform the user that they need to keep the ticket number inside the subject of the emails (which when replying to emails the subject is usually kept the same anyway but with RE: appended etc).  An example would be a subject of...

(ticket:001234) Unable to log onto my online banking account

Now, there's a couple of options for how to get the email to the MySQL database.  One would be to create an email reader script that you schedule to run at certain tiimes of the day.  This script connects to the POP3 server, logs in with the support@yoursite.com account and checks for any new email messages.  It then retrieves these message, checks who they are from, identifies the ticket number from the subject line, checks the database for a corresponding support ticket and updates accordingly.

you would want to schedule this script to run at regular intervals so the delay between a user emailing a response and the database being updated is minimal.

Alternatively, depending on your hosting provider and their features you may be able to redirect an email address to a .php script.

For example, I use hostgator (I wont get into their advantages and disadvantages, each to their own) however within cPanel I can create a new email address, such as support@mysite.com.  Then, I can access to Email Forwarding section and configure it so that any email received via support@mysite.com gets delivered to the /home/myaccount/public_html/email.php script.

This email.php script would then process the incoming email message (usually delivered via stdin) and then act accordingly.

Here are a couple of  pages that may help get you started:

Redirect email to .php script: http://www.evolt.org/incoming_mail_and_php
// read from stdin
$fd = fopen("php://stdin", "r");
$email = "";
while (!feof($fd)) {
    $email .= fread($fd, 1024);
}
fclose($fd);

Open in new window


After the above code is run the entire contents of the email will be available in the $email variable.  For debugging it may be worth while writing this back to a text file on the server for you to investigate and see how it can be parsed for the response.

Some links for connecting to a mail server etc...

http://php.net/manual/en/book.imap.php

http://stackoverflow.com/questions/114953/how-to-get-email-and-their-attachments-from-php

http://garrettstjohn.com/entry/reading-emails-with-php/

If I were in your position I would use the option of redirecting incoming emails to a .php script rather than scheduling a script to connect to the mail server and check for emails.

It will help keep the system more dynamic and live rather than waiting a couple of minutes for the script to run and check for new messages and help things move along more smoothly.

It's also probably slightly easier as either way you are going to have to parse the email message to find the email content, ticket number and attachments and by redirecting to a script you haven't got the extra hassle of managing a connection with the mail server and your script will be called as and when it's required.

Sorry I can't provide you with any exact example code, however hopefully this theory may help point you in the right direction.

Cheers,

Matt
0
 
johanntagleCommented:
I'd like to answer this from another angle - do you really need to build one from scratch?  Check this out: http://www.opensourcehelpdesklist.com/

The ability to receive responses via email is nothing new - I'm sure many of the those listed in that website has it.  They may not have the exact functionality you want, but they are open source - you can modify them then just link to your current web app.
0
 
w00tw00t111Author Commented:
Thank you both for your responses. Johann, I'll definitely be taking a look at some of those open source options. The thought was since the rest of the app is written with a mvc structure in code igniter it would be easiest (going forward and in the future) for the BI necessary for the help desk and the help desk to be written with the same coding structure.

Matt, that's essentially exactly what we were looking for with the email being sent to a php script. Do I presume correctly that the script could be written that if ticket is not found in the subject box that a new ticket would be created for that user (which could be determined based on their email address).

The HTML body of the email messages/instant messages should these just be stored as a text field in mysql?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
MattCommented:
Hi

Yes you could store the html body as a text field in the database. You may need to check the email to see if there is an attachment and strip that out first. I'm not sure how to do this but set the script up, send it an email that has an attachment and code the script so that it saves the email to a text file then look at the email structure in the text file to see how to parse it.

Yes you are thinking along the right lines about creating a new ticket. If the ticket id is not found in the subject, identify the user by their email address and create a new ticket.

Or if you can't identify them from their email send them an email back saying they don't appear to be registered etc. Of create them a new account it depends on your requirements.

Cheers

0
 
w00tw00t111Author Commented:
Got it! Thanks Matt.

One of the help desks that we were demo-ing used an interesting method for detecting current tickets vs new tickets. Unfortunately, this was the only interesting thing about the software, everything else didn't meet our needs :/

You could send an email to support@domain.com and a ticket would be created between you and the sender (whether they were registered or not). However, if a ticket was already created the email address was: support+t{x}@domain.com
Where {x} = the ticket number.
What this allowed to have happen is that the subject box could be changed, or whatever you wanted but it was still kept under the same ticket id.

What I'm not understanding is how do they establish the new email address? I.e. each new ticket has a new email.
Ex:
support+t1@domain.com for Ticket: 1
support+t102@domain.com for Ticket 102
Etc..

Can a script be loaded on the mail server to strip out the + and whatever comes after it and deliver the message to everything to the left of the "+"?
From what I know of domain servers and mail servers, the email should never even reach the domain should it?
0
 
MattCommented:
Hi

I'm not too sure about this but on one of my previous hosts I could set up a default email. Then any incoming email on my domain that didn't exactly match a mailbox was delivered to the default address.

Then you could possibly configure the script at this default address to read the emails, chech the original to address and identify the ticket number.

I would check with your host if it is possible to configure your email system in this way as I wouldn't have a clue how it is done.

Sounds an interesting way to do it though.

Cheers

Matt
0
 
MattCommented:
Hi w00t,

Thanks for the points.

Cheers

Matt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now