[howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

rossharvey · 2012-05-11 11:55:31

Saving website enquiries (especially in business) is very useful indeed. So is having automated email subject titles that are great for organisation and automatic filtering. To do it:

1. First off, the form itself (zem_contact_reborn) on the contact page:

<txp:zem_contact to="hello@website.com" label="" subject_form="subject_form" thanks_form="thanks_form">
  <p><txp:zem_contact_select list="Wedding,Vintage Wedding,Lifestyle,General" selected="Wedding" name="type" label="type" required="1" /></p>
  <p><txp:zem_contact_text name="name" label="Name" required="1" /></p>
  <p><txp:zem_contact_email name="email" required="1" /></p>
  <p><txp:zem_contact_text name="phone" label="Phone" required="1" /></p>
  <p><txp:zem_contact_text name="datepicker" label="Wedding Date" required="0" /></p>
  <p><txp:zem_contact_textarea name="message" required="1" /></p>
  <txp:zem_contact_submit label="Send" />
</txp:zem_contact>

Using your own input names and types, of course. Note the calls for subject_form and thanks_form, we’ll get to those later.

2. The MySQL enquiry database

- Create a new MySQL DB (my host uses CPanelX, which makes this very easy) named enquiry

- Create a new user/password and add it to the DB with all privileges

- Open up myPhpAdmin and add a new table, name it enquiry

- Call the first column id, of type INT, and tick A_I (auto increment)

- Add one column per input form item (using the same name), I used VARCHAR (size 255) for all fields, bar the large message input, which is of type TEXT

- Save

3. Add a new MISC form, called subject_form, and add:

<txp:php>
  global $zem_contact_form;
  echo $zem_contact_form['Name'] . 's ' . $zem_contact_form['Type'] . ' Photography - ' . date("d/m/Y", strtotime($zem_contact_form['Wedding Date']));
</txp:php>

Subject titles then look like: Ross Harveys Vintage Wedding Photography – 22/08/2012

You can of course tweak this for your own purposes. You must use the label names, not the input names in this custom form. For example, the form element for Wedding Date is actually named datepicker.

3. Create a new MISC form named thanks_form

Here is where we capture the results from the submitted form and ping it to our newly created enquiry database. First off, a personalised thanks:

                <h2>Thanks for getting in touch <txp:php>echo $_REQUEST['name'] . "!";</txp:php></h1>
                <p>I will get back to you very soon.</p>

Now we add the custom DB call. First, set the variables (using your details from step 2):

 <txp:php>
  // Database entry
  $host = "localhost";
  $user = "username";
  $pass = "password";
  $db = "enquiry";

Then we connect to the MySQL server and select the DB:

 $link = mysql_connect($host, $user, $pass);
  $link = mysql_select_db($db);

Next we pull the data captured in the form into temporary variables, note the use of the form element name attribute:

 $temp_type =  $_REQUEST['type'];
  $temp_name =  $_REQUEST['name'];
  $temp_email = $_REQUEST['email'];
  $temp_phone = $_REQUEST['phone'];
  $temp_date =  date("d/m/Y", strtotime($_REQUEST['datepicker']));
  $temp_msg = $_REQUEST['message'];

Finally we sling it into the DB

 $result = mysql_query("INSERT INTO enquiry (type, name, email, phone, date, message) VALUES ('$temp_type', '$temp_name', '$temp_email', '$temp_phone', '$temp_date', '$temp_msg')");
  </txp:php>

Note how the INSERT call lists the input variables after the table name enquiry, then matches them to the input variables after the VALUES call. Whatever form elements you use, make sure these match and mirror the enquiry table definition created in Step 2.

That’s it – you new have awesome subject titles with automated database backup of all enquiries!

Testing and debugging

Before putting this live, I recommend making a new PHP script/file, and dumping it on your server (and accessing it via your browser) to test the DB connection. Here’s mine (with added die catches to see if there are any MySQL connection errors), change yours accordingly…

<?php

  // Database entry
  $host = "localhost";
  $user = "username";
  $pass = "password";
  $db = "enquiry";

  mysql_connect($host, $user, $pass) or die('Could not connect: ' . mysql_error());
  mysql_select_db($db) or die('Could not select database: ' . mysql_error());

  $result = mysql_query("INSERT INTO enquiry (type, name, email, phone, date, message) VALUES ('Test', 'Test Name', 'test@email.com', '000000000000', '00/00/0000', 'Hello, test message!')") or die('Could not insert data: ' . mysql_error());

  if($result) { echo 'Success'; }

?>

Last edited by rossharvey (2012-05-12 10:17:18)

colak · 2012-05-11 12:44:33

rossharvey wrote:

Essentially, I’d like to pump contact form submission details into a database (I have a feeling I’m losing some contact emails, so this is a direct backup of form submissions).

Why don’t you use mem_postmaster and do some trickery with it so as to backup the name and email automatically? there is already the option (in the form of a checkbox) to include it in the contact form.

The difference of this to what you are asking is that it does not save in another db (just in another table).

net-carver · 2012-05-11 14:41:03

Hello Ross,

Does your MySQL user ‘rossharv_txp’ have Insert rights on your new table?

rossharvey · 2012-05-11 14:54:34

A schoolboy error has cost me an entire afternoon!

Logged onto the old server, was shifted to a new one a couple of weeks ago – bookmark was outdated.

Sigh

At least it works, thanks to those for offering help!

colak · 2012-05-12 06:40:05

Hi Ross. Glad you solved it.
Can you post a tutorial describing how you did it as it might be of help to others here.

rossharvey · 2012-05-12 10:16:22

colak wrote:

Hi Ross. Glad you solved it. Can you post a tutorial describing how you did it as it might be of help to others here.

Done! You may want to move to the relevant forum :¬)

colak · 2012-05-12 10:35:37

rossharvey wrote:

You may want to move to the relevant forum :¬)

Done:)

admi · 2013-12-07 16:51:20

Thanks for the great method. It gotta be included on txptips.com.

I have a question as for IP and Browser. Zem_contact_reborn has tags for them:

<txp:zem_contact_serverinfo name="REMOTE_ADDR" label="IP number" />
<txp:zem_contact_serverinfo name="HTTP_USER_AGENT" label="Browser" />

I managed to populate all fields that i have on my contact form except the above data. I tried that through the test-debug .php file that has to be put in the site root and it works OK. But when I send a test message through the contact form the two fields for IP and Browser are empty.
Did I miss anything? Could anyone give me a hint as to where the mistake is?

Textpattern CMS

Textpattern CMS support forum

#1 2012-05-11 11:55:31

[howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

#2 2012-05-11 12:44:33

Re: [howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

#3 2012-05-11 14:41:03

Re: [howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

#4 2012-05-11 14:54:34

Re: [howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

#5 2012-05-12 06:40:05

Re: [howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

#6 2012-05-12 10:16:22

Re: [howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

#7 2012-05-12 10:35:37

Re: [howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

#8 2013-12-07 16:51:20

Re: [howto] [Guide] MySQL backup of email enquiries with sexy titles (zem)

Board footer