Jump to content
Killersites Forums

Form data to MySQL & Email


oohrogerpalmer
 Share

Recommended Posts

Hello

 

I've been working through Stefan's videos in order to upgrade my website and drag it into the new century. I'm intending to use CSS and php to make it compliant and more accessible to people with disabilities (and make my life easier too).

 

I haven't written anything yet because I want to get all the technical ideas and concepts in order first, but my current site is hamberie.com

 

I'm nearly ready to start the overhaul but I have a couple of questions about form data.

 

In my old site, the form data is sent to me via email and I manually enter it into my database.

 

I want clients to complete a booking form and then this data to be automatically entered into a MySQL database (I set this up using Stefans video tutorial yesterday).

I'm pretty confident I can write the php and sql for this now, but can I also send this data (or some of it) to my email as well. The idea is that I could just send the clients first name and email address and some very basic info sent to my email so that I am aware of the enquiry and can reply straight away, but the whole content of the form is sent to the database.

 

I intend to write the php individually and use includes as Stefan suggested. As such I'm going to have php script, probably called dbprocess.php or something, that process the data inputted via the form. Can I simply write a second php script that also sends the same inputted data to my email?If so, do I include this in the same php that sends the data to the database?

 

This seems like a really obvious thing but I can't seem to find any reference to it.

 

Also, Once this data is in the MySQL database what's the best way to get it onto my home computer? I can see lots of ways to do this, including actually exporting it using the phpadmin via my server provider, but this seems really clumsy. Is there an obvious, elegant way to get this information downloaded into my computers database from the MySQL database.

 

Thanks for any help.

Link to comment
Share on other sites

You can certainly send form data to a database and also send it to your email. Near the end of the php code you need something like

$result=MYSQL_QUERY("INSERT INTO your-table-name (id,title,message,who,email, date,time)".

"VALUES ('NULL', '$title', '$message', '$who', '$email', '$date', '$time')")or die

( "

Unable to select table

");

mysql_close();

 

and also something like

mail( $admin, "Feedback: $subject", "$name\n$message\n$component $size\n$delivery\n$confirmation", "From: $name " );

 

(I've copied from two different forms above so the fields are different).

 

You can export a database to Excel which may be the best way for you.

Link to comment
Share on other sites

Many thanks Wickham - I'll have to take a good look at the code but as long as I know it's possible in principle.

 

As for exporting, is there any way to export the data, or even just download the table directly onto my computer without having to physically access the MySQL section of my webhost.

 

As things stand now I enter the data into the database and then use it to create invoices and booking confirmations, and sometimes labels too. As such I really need access to it on my home computer.

 

Again this seems really obvious but how do I get the data fro the table in the MySQL section of my Server provider to my computer without having to login to my webserver, and then login to the MySQL section of the

 

I suppose I could just create a page that echos the data and copy and paste it into my home computers database, but this seems awkward and almost certainly risky.

 

There must be something more obvious than that, and I am probably just being a bit gormless. To be honest my head is swimming a little with new php, sql, css and other information so I'm probably not seeing the obvious solution to this.

Edited by oohrogerpalmer
Link to comment
Share on other sites

You can show the data on a webpage; set up a webpage filename that no one else can access, possibly with a login only known to you.

 

http://www.wickham43.net/formphptomysql.php

 

You should be able to code it so that only the recent data entries show, either the last 50 or only from the same date. I haven't done that with my example above.

 

Edit: each time you refresh the page it will include any new entries.

Edited by Wickham
Link to comment
Share on other sites

Hi again.

 

I've successfully created a php form that sends data to mySQL database (many thanks) but I'm not getting the email message.

 

I created the form_response.php and made sure it worked, but when I added the email bit I just got the same results with no email being sent. The database was populated ok, but I never got an email.

 

I assume I'm either putting the mail bit in the wrong place (perhaps I should add it directly to the booking_form.php), or I'm making a basic error.

 

 

The php is very basic at this point, but the response php is as follows. (I've put xxx's for my info)

 

<?php

 

$title=$_POST['title'];

$namefirst=$_POST['name_first'];

$surname=$_POST['surname'];

 

mysql_connect(localhost, "XXXXXXX", "XXXXXXX") or die(mysql_error());

mysql_select_db("xxxxxxx") or die(mysql_error());

mysql_query("INSERT INTO `Booking` (id, Title, Namefirst, Surname)".

"VALUES ('NULL','$title', '$namefirst', '$surname')")or die( "

Unable to select table

");

mysql_close();

Print "Your information has been successfully added to the database.";

 

$emailmessage="($title) ($surname) has sent a booking form";

 

mail('xxxxxx@gmail.com','Trial Form data',$emailmessage);

 

?>

 

 

Many thanks

Link to comment
Share on other sites

:D It's certainly important to code a subject for the email that you will recognise, so you know it's not spam, especially if the email is sent from a form input with someone's email address that you won't recognise, but of course if it's a confirmation from your own mail address then you should definitely recognise that!
Link to comment
Share on other sites

Is there a limit to the number of fields you can use.

 

My form isn't huge but I have just counted up and there wil be 71 fields which seems a lot but I don't see how to reduce this because of the nature of the form.

 

All very small pieces of information, and I hope the mySQL can handle it, but the php is going to start looking a bit busy. Does it affect the php if I breakup the lines just to make it easier to edit and understand.

 

eg.

 

"VALUES ('NULL','$title', '$namefirst', '$surname',

'$ad1','$ad2','$ad3','$ad4','$postcode','$telephone','$email','$ad1',

'$arrivaldate','$departuredate','$adults','$teens','$kids','$babies',

'$termsread')")

or die( "

Unable to select table

");

 

....or perhaps there is a more logical way of doing it?

 

Just while your here, I really want to put that info onto a secure webpage only I can access but I don't know how to password protect a page- is it done via php or by the server-provider? or something else?

 

Many thanks once again

Link to comment
Share on other sites

No, there isn't a limit to the number of fields, though as you say, the bigger the form the more complicated the PHP.

 

You should be fine breaking things up into a couple lines for readability. Your example above should work fine. PHP doesn't understand blank space, so for example,

 

<?php echo "test"; ?>

 

is the same as

 

<?php

echo

"test"

;

?>

 

Password protection can be done a variety of ways. Often your web host will provide you a simple way to password protect pages (I believe that may be done using .htaccess files, but I'm not sure.)

 

The way I usually do it:

 

-- create a form allowing the user to log in

-- when the form is submitted, the data gets sent to a PHP page. The PHP page grabs the form data using POST[], checks for errors (blank fields, etc) and then compares the form data against the username/password (for simplicity, you could compare it against username/password values set in the code. Alternatively, you could use a bit of MySQL to check the username/password against a table in the database)

-- if the login is incorrect, redirect back to the page and show an error message. If it is correct, set a session variable and redirect to the logged in page.

 

-- For security reasons, in any pages that require the user to be logged in, at the top of the code, check that the correct session variable is set (showing that they are logged in.) If incorrect, redirect them to the login page.

-- On any pages that require a user to be logged in, include a link that allows them to log out, destroying the "logged in" session variable.

Link to comment
Share on other sites

Much obliged falkencreative.

 

Didn't understand most of the password stuff but I'll give it another go tomorrow morning - this is France and we imbibe after five.

 

I am only wanting to see the latest info sent to the database, so when I get an email saying that someone has sent a booking form, I and only I will visit my password protected page and retrieve the new info and copy it to my home database/spreadsheet for further manipulation.

 

The way you describe above seems to be a little beyond my abilities right now, but having said that I want this info to be pretty secure. I'll have a look at .htaccess files.

 

Many thanks for taking the time.

Link to comment
Share on other sites

Thanks for that.

That looks exactly like what I'm after - still a bit scary but seems do-able.

Just one quick question - I didn't recognise this bit.

 

header('Location: main.php');

 

I've not seen this bit of code before - does it just take you to the page - in this case main.php, and in my case echo_of_my_database.php or is something else?

 

Many thanks again.

Link to comment
Share on other sites

Hello again.

 

I've managed to created the form that both sends data to my SQL and a quick email too, and got it in a working format - my webpage is www.hamberie.com, and I've managed to get the booking form working and populating the database really well.

 

I'm stuck on the last part of trying to publish/retrieve the data from the mySQL database onto my home pc. As the table is over 70 fields long, I think I need a solution that just downloads a csv file rather than publish's the data in a huge table on a web page. Then I can just import it directly into my spreadsheet.

 

I tried to find a solution and found this bit of code that apparently can export the table into a csv file, but I can't get it to work, and to be honest I don't know where I would put this bit of code either?

 

<?php

 

mysql_connect(localhost, "myusername", "password") or die(mysql_error());

mysql_select_db("mydatabase") or die(mysql_error());

 

// this bit obviously opens the database

 

$result = mysql_query('SELECT * FROM Booking') or die(mysql_error());

 

//this bit gets all (*) the data from the table, but the rest is just beyond me

 

 

$file = uniqid().'.csv';

 

$fh = fopen($file, 'w') or die("Unable to open file for writing");

while($row = mysql_fetch_row($result))

{

fputcsv($fh, $row);

}

fclose($fh);

header('Content-Type: text/csv');

readfile($file);

exit;

?>

 

I just saved it as a php page and ran it but the following error came up

 

Warning: fopen(4a7855ae56ebf.csv) [function.fopen]: failed to open stream: Permission denied in /home/www/hamberiO/privpage1.php on line 8

Unable to open file for writing

 

This is the first bit I've tried to do without really understanding it, so it's probably a stupid error on my part.

 

Any and all help greatly appreciated.

Edited by oohrogerpalmer
Link to comment
Share on other sites

I have to admit, I'm a bit stumped by this one. I've tried the code both on my local server and my live server, and in both cases the line that you are having the problem with works just fine.

 

A couple questions:

-- can you post the PHP code for the uniqid() function? (used to create the file name)

-- where are you trying this? On a server on your local computer? On a live server?

Link to comment
Share on other sites

Hello falkencreative

 

I'm sorry but I don't know what this bit means

 

- can you post the PHP code for the uniqid() function? (used to create the file name)

 

Are you asking me to send you something I created - I haven't done that and if I am meant to, that is probably why this is not working.

 

I use web-mania as my service provider. I can't even depend on electricity to stay on here, let alone broadband.

 

When you run it, does it save a file to your computer or does it do something else?

Link to comment
Share on other sites

1) This is a PHP function: "uniqid()" (http://www.tizag.com/phpT/phpfunctions.php)

 

It calls a portion of code that will looks something like this:

 

function uniqid()

{

code here...

}

 

Ignore my questions though -- you answered the second one (you are running the PHP file on a live server through Web-Mania).

 

Try this first. Replace this line in your code:

 

$file = uniqid().'.csv';

 

with this:

 

$file = 'export.csv';

 

Save the file, upload to the host, and try it again. If you still get an error, post it.

Link to comment
Share on other sites

Actually, I have a kitten on my desk at the moment. He likes to sit either on the mouse or on the keyboard. :)

 

Ummm... Can you check your host, and see if export.csv currently exists on the server? If so, it should be in the same folder as your privpage1.php file. It it doesn't, create it and check your PHP file again?

Link to comment
Share on other sites

I created the export.csv and uploaded it but got this response.

 

Warning: fopen(export.csv) [function.fopen]: failed to open stream: Permission denied in /home/www/hamberiO/privpage1.php on line 8

Unable to open file for writing

 

The part in [function.fopen] was a hyperlink that tried to take me to a page http://www.hamberie.com/function.fopen?

 

Does this help? cos I know a kitten who likes both the mouse and the sound the keyboard makes doesn't.

Link to comment
Share on other sites

Hmm. Perhaps the host has disabled the fopen command? Try one more thing for me... Create a new file on your hosting called "info.php", and the contents of the file should look like this:

 

<?php phpinfo(); ?>

 

Once you've uploaded the file, take a look in your browser. It should bring up a page showing you your PHP version and a long list of info. Scroll down the page. Under "Configuration" > "PHP Core" there should be an entry for "allow_url_fopen" Check and see what value it lists. Both the local and master values should be set to "on".

 

(Once you've checked this, remember to delete the info page)

 

What are you trying to do with this file you are creating anyway? Once it is created, download it to your desktop? I'm just trying to think of options in case we can't get this to work. As I said, on my machine, the code you are using works just fine.

Link to comment
Share on other sites

The version it comes back with is php version5.2.8

 

I've had a good look for the info you asked for and it is exactly as you said - both 'on'. Also deleted the page now too.

 

I need to put this information into a spreadsheet that will then use almost all of the info to generate an invoice. Most of the info is simply used as a mailmerge for the address but I have made a few functions that use the 'date of arrival' and 'date of departure' to quote a price. This is so that my parents can use the spreadsheet to generate invoices without having to type all the info and/or download it by accessing the server, the thought of which frankly terrifies me.

 

It's bound to be my ineptness, but I appreciate your endurance.

Link to comment
Share on other sites

Well, ok, sounds like the fopen function isn't disabled. I'm not sure what to suggest now, since the code you are using works just fine on my computer without errors. Perhaps someone else will take a look at the thread and have a suggestion? Could you perhaps do this a different way? For example, instead of generating a text file, generate a long string of text and have the user copy/paste it somewhere? If that would work, I should be able to help you with that, and hopefully it will bypass the issue.

 

In regards to the "allow_url_include" -- You'd need to test it to be sure, but yes, I believe that means that PHP includes are disabled. Shouldn't necessarily be a problem now, but it may come up in the future. PHP includes can be quite useful, especially for repeating sections of code. For example, you could use a PHP include to include a navigation bar in all pages of your site. That way, instead of having to edit all the pages of your site manually to update a link, you would just need to edit one file.

http://www.tizag.com/phpT/include.php

 

Who is providing this web space to you? An actual web hosting provider? Or simply your email or internet access provider?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...