Taking form information and putting it in a text file or MySQL

[jamey5k]

Hello,

I have a web site set up with a form already written, with about 15 different fields. I want to retrieve the information from this form so that I can read user responses.

I tried using mailto, and would prefer not to use that, mainly because I prefer to keep my e-mail address out of the html source code to prevent spiders/robots from accessing this information...

...so, I am playing around with php to do this... either to write to a SQL database (and I have set up a SQL database in my account now), or even just to append to a text file (which I would put in a password protected directory, so that users cannot access it themsevles). Some of the examples I have seen have php code which has the site name, user name, and password all right there in the code - this seems very insecure to me - I imagine just any user can see my php code and then retrieve my passwords, correct?

Once I have the proper php code to do this, do I just create a text file with the code, save it (ie, process.php), put the line in my form .html file (ie, <form name="myform" action="process.php" method="post">) and upload those to my site?

I am not trying to do anything fancy, just use my form!

I tried using phpformgenerator from Fantastico, but has issues getting it to create an SQL database, which appeared to be due to a login error. I tried doing it without SQL and still got errors.

THANKS!

[Seb]

I'm not 110% sure but I don't think anyone can get a hold of the database account info inside your php scripts unless they are sniffing your webserver traffic or something. For further protection you can also create an external script that handles the database connection and then just include it in the PHP file. PHP scripts are run on the server before the page is sent to the client, which means the executed PHP code won't be visible when they "view sources".
If you don't want want to use a formmail application, a PHP script is the way to go. You can setup an easy database table to gather the information sent from the form and have a simple page that displays the record.

This is not very advanced, maybe someone on here can be so nice to write such a script for you, otherwise I'll return in the next days (probably) and give you some guidelines. :)

For quicker response you might write here how your database table is set up (fields).

[jamey5k]

Wow, thanks for a great reply - that is very helpful to understand that. Basically, I am a master at using Excel as a database, and can program Matlab, but don't know much about any SQL stuff or php language - it's all new to me!

I own Adobe GoLive and have done the rest of the site in there, including the layout of the form page and all the content. All I am using forms for right now is just gathering some optional information from potential customers who want to use the form (ie, name, city, state, how they found the website, some other relevant info) - nothing fancy or advanced. Basically, it is about 20 fields, as of right now laid out in 2 columns with one submit button for everything, and each field is uniquely named, in case that helps.

Thanks again!

[McCoy]

Seb is right, no-one can see the source code of your PHP scripts unless they break into the server itself and manage to download the original PHP files. PHP files accessed through a URL are always proccessed by the web server and the final user never gets the original sourcecode, so they are secure.

Not even by sniffing they could get the password, as the username and password for the mysql database are only transmitted inside the server itself, it never uses the internet, unless the mysql server and the web server are in different datacenters, which is highly unlikely. The only way would be to put a sniffer INSIDE the server, and you need access to the server for that, so once you get access it's just easier to grab the PHP file and that's all.

So all in all, don't worry too much about having your passwords inside the PHP code, they are quite secure.

If you are still worried I think you can use encrypted passwords to login to a MYSQL server but I've never tried it myself, so I can't tell you.

I have recently made a very simple contact form PHP script for one of my clients, which sends the data inputted by the user to an e-mail addres of choice, nicely formatted. Of course the e-mail is hidden to robots because it is inside the PHP code and thus can't be accessed by someone external to the server, as I explained.

If you tell me exactly the fields you need I can extend it and give it to you, wouldn't take me more than 5 minutes. Of course I would not charge you, don't worry :).

It doesn't store any of the data, it only sends it to the chosen e-mail address, so it doesn't need a database. That means no passwords ;)

[jamey5k]

Great - that helps a lot then and gives me a better feeling knowing it is secure. I will play around with it and see if I can get it to work. Thanks so much for the help everybody!

[Seb]

Good post McCoy, although I was fairly sure it was good to have it confirmed. Seeing as I have programmed in PHP for over 2 years now I should have found this out earlier haha.

Still if they sniff the traffic they should be able to collect the information from the form fields on the page, isn't that correct? (@ POST-data) :cool:

[McCoy]

Well, the data you input into a form can be sniffed, so if you input a password it can be taken while it goes from your computer to the destination server (if the connection is unencrypted, that's it), but as we were talking about passwords already hard-coded within the PHP code that doesn't matter, as everything happens inside the server.

[dinaglen]

If you don't want want to use a formmail application, a PHP script is the way to go. You can setup an easy database table to gather the information sent from the form and have a simple page that displays the record.



please, please help me! this is exactly what I need, but I am new to php and databases, so I need some help. I need to let people enter some information about themselves (8 fields alltogether) and when they click submit, this information would be displayed on the page, with all the other people. It should be displayed in a table with 2 or 3 sortable columns.

I don't know where to start with this. I will be gratefull for any advise.
Thank you
Dina

[justafriend]

Hi Dina, Ive just come to terms with the exact same issue, taking information from users in a form, submitting it to a sql database, and having that informatino displayed automaticcaly on another page. Heres how I did it, with as much detail as I can think of:
first create your sqldatabase, and a table in it, with all the columns ( using hostmonster's option in the control panel will let you do that pretty easily)

lets take as example that your form page is called submit.html. You have a page thankyou.php which is the page users will see once they click on submit. and you have for example, a page output.php, on which the form information is displayed.

in your <form> tag on the submit page, you need to add the action tag, and the method tag. in this case it would be:
< form action="thankyou.php" method="post">
<input >
<input> etc
</form>

This will send all the information from your form to the page thankyou.php and redirect users to that page once the form is submitted

then

on thankyou.php, that is where you will write your php code. php works more or less like html..you have opening tags, and closing tags etc. but more advanced functions
start your code with" <?php " and you close it with " ?> "

here is the code i used to :
1-connect to your database
2-input information in the correct fields
---------------------------------
<?php
$con = mysql_connect("localhost","yourdomainname_username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}mysql_select_db("urdomainname_yourdatabasename", $con);$sql="INSERT INTO domainname_databasename.tablename (1,2,3,4, etc)
VALUES
('$_POST[1]','$_POST[2]','$_POST[3]','$_POST[4]','$_POST[etc]' )";if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "";mysql_close($con)
?>

--------------------------------


read it carefully, its fairly simple to understand once youve spent some time on it
the mysql_connect connects to the database by using "localhost" (dont change that) then your domainname at hostmonster, then your username which you created for the database, then the password you chose, in the same format as the code
further down youll see INSERT INTO..as it says, it will input the form info into the fields that you have to create in your database table..again follow the format, shouldt be too hard..make sure to have the 'name="xxxx" ' tag in your <input> in the form to match the (1, 2, 3, )-rename that- in the above code then you have VALUES_POST[] etc...again, replace 1,2,3 etc, by the input names from your form, which will then be inputted into your table, under the columns that match those names.

Now, youre information is sent to the database, but you want to retrieve it.
Heres the code i used for that,:

-------------------
<?php

$con = mysql_connect("localhost","domnm_usernm","passwrd");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}mysql_select_db("urdomainnam_urdatabasenm", $con);



$query = "SELECT * FROM justafri_entry.entries2 WHERE sex = 'male' AND cat= 'break' ;
$result = mysql_query($query) or die('Error, query failed');

while($row = mysql_fetch_array($result))

{

echo $row['comment'] . " <br /><br />" . $row['name'];
echo '<br />';



}

?>
--------------------------------------------------------

At first, youll recognise the connect function at start of code. then you have the query function, which will ...query!...the * means ALL from the database...the WHERE indicates you want a match, in this case, i wanted to have stories where the column sex="female" AND cat="break"...you can name those as you wish, as long as everything matches..under that you have the fucntion echo, which prints something on the screen..in that case, echo $row [nameofyourcolumnhere]

you could also have in the query line, * FROM, and no WHERE after, that will have everything taken out from your database, and then you can print it out with echo [nameofyourcolumnhere] etc

There are many more options you can use, I personnaly wanted to have only one entry showing per page, and to have that entry match two fields from the form, and that that 1 entry always be the most recent post. I also needed to browse thru all the entries on a given page, that matched the above criteria, and to have a random button to go thru those pages.
Check it out, try the form and go read your story posted in the right section, youll see what i mean www.just-a-friend.com

There are many things you might want to do with this, and I suggest you read around on this page, they have lots of helpfull information on PHP and SQL: http://www.php-mysql-tutorial.com/

Hope this helped! dont hesitate to ask more questions if somethings missing!

Sherif

[dinaglen]

justafriend thanks alot for your reply! I will be trying to create my submit form soon, and will let you know if I succeed or not! :)

[justafriend]

yep definitly let me know if this helped you!

[dinaglen]

Is it possible to create a field in the databe to store a small photograph (like an avatar size picture)? I am not sure what datatype it would be...

I have followed all your steps and I am not sure if it's working or not. When I click submit on the submit.html page it goes to a blank thankyou.php page.

When you say "yourdomainname", do you mean yourdomainname.com or the first 8 letters?

I have got 3 files uploaded to my website:
submit.html (contains a form)
thankyou.php (contains the first piece of code)
output.php (contains the second piece of code)

but shouldn't there be another .html page, which displays the result?

also, when I was creating a database I wasn't sure which data type and settings to use, so I left them all VARCHAR, NULL.

the fisrt field in the form is a file field for a small picture
all the other fields are either text or textarea fields.

please, can you tell me where I go wrong?
thanks
Dina

[justafriend]

I have to admit I am not sure about the putting a picture in the database, although i am pretty sure it is doable. Unless im totally wrong, you can upload pretty much anything into a database. As for datatypes, you should click the "?" on top of the datatype column when you create a new column in your database, using phpadmin, because I am not too sure what they are all for lol!

The your domain name was the 8 first letters, i should have precised. In my case justafriend is shortened down to justafri_sherif(username)

Using the 3 pages you mentioned, heres what I would do :

submit.html - make sure all the name tags in your form match the proper fields in the database, as well as in the first code-which goes on thankyou.php

Php can be seen as a basic html page, with added php code, put between the <?php ?> opening and closing brackets. So thankyou.php could be any page you want, that you could design in basic html coding, and that code can be inserted anywhere on that page, i would put it right after the opening <body> tag.

Replace the numbers [1] [2] [3] in the POST command by the name of each of your fields, so they are sent into the proper database column.

Then, output.php could be, again, any page you like, on which you add the second bit of code.

In that segment:
-----------------
$query = "SELECT * FROM justafri_entry.entries2 WHERE sex = 'male' AND cat= 'break' ;
$result = mysql_query($query) or die('Error, query failed');

while($row = mysql_fetch_array($result))

{
---------------------

Replace justafri by your 8firstlettersofdomainname_nameofyourdatabaseinfull .nameofyourtableinfull

Depending on what you want to output in your output.php page, you can alter the rest of that line slightly. SELECT * FROM, takes all the information from your database, WHERE and the following can be totally removed if you want all the info to be seen on one page. If as I wanted, you only want to output specific columns on a given output.php use WHERE and write the column name as in my example : WHERE sex = 'male' AND cat = 'break' ;
dont forget the semi-colon at the end. In that example the sex is the column in which the exact value 'male' was chosen from the radio button on my page, same for cat ='break' .This works best if youre sure the values are gonna be precisely what you write in there. If not, you can use the sign % in front of after a word to have just that part of the word taken in consideration. for example %ale would output both values male, and female, from the column sex.


-------------
echo $row['comment'] ;

---------
the function echo prints stuff on the screen, in that case it will print the content of 'comment'. If you want to write actual words in between your database comments use " ", for example

echo $row ['age'] . " years old " ;

would print 19 years old, for example.

I am not sure of the kind of output you want from your form, how many entries you want to be placing on a single page, and how you want to order them. If you have something precise you want to do, let me know, Ill try my best to help.

One last thing, if thankyou.php is totally blank, its probably a good sign. if there was something wrong with the code, or it couldnt connect, you have a messag elike "query failed" ..again, you can dress up thankyou.php as you want with html, and just slide the first code anywhere in it. Did you verify if the info you typed in the field went to your directory?

Hope this works!

Sherif
www.just-a-friend.com

[dinaglen]

I've done it and it works with text!!! can't see the picture though :(

How can I make it displayed in a table, at the moment it is displayed the same way I entered the information in the form and the second person is just added underneath after one blank line.

also, when filling the form, I entered text in the textarea field, pressing the enter button inside the field, thus creating another line of text. But on the display page (output.php) all the text from that textarea is displayed in one line. How can I fix this?

[Seb]

I've done it and it works with text!!! can't see the picture though :(

How can I make it displayed in a table, at the moment it is displayed the same way I entered the information in the form and the second person is just added underneath after one blank line.

also, when filling the form, I entered text in the textarea field, pressing the enter button inside the field, thus creating another line of text. But on the display page (output.php) all the text from that textarea is displayed in one line. How can I fix this?

1. You can store images in a database with the blob fields. It's more complicated than just displaying some text though. You will need to create a script to show those images. See http://www.php-mysql-tutorial.com/php-mysql-upload.php

2. The line breaks needs to be converted to HTML line breaks. You can do this with the function nl2br() like so
echo nl2br($row['message']);Of course depending on how you output the text.

[justafriend]

Hi Dina, just a quick reply here, Im in a real rush these days. Basically, when you want to output something from your database in a specific way or place you just put the echo line where you want that information example:

If you want to have a table with 2 columns you could do something like this:

<table>
<tr>
<td>
<?php
echo $row ['1'];
?>
</td>
<td>
<?php
echo $row ['2'];
?>
</td>
</tr>
</table>


just build your table as usual, and in each field, where you normally put text, just open a php with this <?php, put : echo $row['nameofthecolumnfromyourdatabasethatyouwanttoappea rinthatspecificplace']; and then you close the php with ?>

dont forget the ; after each echo line, and to always open and close the php code for each table cell. Hope this helps!

Sherif

[serversn]

Hi JustAFriend, good job helping them! If you would like to make it a little easier on them, check out my free script called RDATABASE . Just as you said, it basically works as an include file which creates a database object (the code looks simple). This is not an advertisement, this is free and I use it for my websites! http://www.risharde.com/rdpage.php?page=rdatabase

The link above will carry you directly to the instructions on how to use it and it has a direct download on that page.

Remember that this is a framework and this version doesn't do security on the input checks so all you have to do is cater for any security issues before using the query function in this provided PHP implementation that I've coded. If you need help with anything, give me a buzz!

-- Risharde

[dinaglen]

serversn

I have downloaded and installed script you have made for displaying the
database. It seems to work! But when it finds no data in the database,
I get an error in that cell. Please, can you advise me how to fix this
problem?

here's the testing page with form:

http://littleone.bonnieglen.com/submit.html

I still didn't work out how to put picture in, so ignore the first
field. Just enter anything and click Submit.

The page that displays te result is:

http://littleone.bonnieglen.com/file1.php

I have made the table to accomodate 100 people in it, but you can see
the warning messages... because there's no data.

Ideally I would like the rows to be added and displayed automaticaly
once a new person adds their information.

I would very grateful if you could help with this.

[justafriend]

Hi Dina, could you post the php code for the page file1.php? we might be able to fix your problem by changing something in the way the code calls the information from the database.
-------------------------
www.just-a-friend.com

[dinaglen]

here's the code for file1.php including the first 2 rows of the table:

<?php
require_once("rinit.php");
$db = new rdatabase();
$db->connect();
$db->selectDatabase();


$results = $db->query("SELECT * FROM bonniegl_littleone.abroad");

?>


</p>
<table width="799" border="1" bordercolor="#999999">
<tr>
<th scope="col">username</th>
<th scope="col">place</th>
<th scope="col">lived</th>
<th scope="col">parents</th>
<th scope="col">children</th>
<th scope="col">email</th>
<th scope="col">website</th>
</tr>
<tr>
<td><?php echo $results->getFieldData(0,1); ?></td>
<td><?php echo $results->getFieldData(0,2); ?></td>
<td><?php echo $results->getFieldData(0,3); ?></td>
<td><?php echo $results->getFieldData(0,4); ?></td>
<td><?php echo $results->getFieldData(0,5); ?></td>
<td><?php echo $results->getFieldData(0,6); ?></td>
<td><?php echo $results->getFieldData(0,7); ?></td>
</tr>
<tr>
<td><?php echo $results->getFieldData(1,1); ?></td>
<td><?php echo $results->getFieldData(1,2); ?></td>
<td><?php echo $results->getFieldData(1,3); ?></td>
<td><?php echo $results->getFieldData(1,4); ?></td>
<td><?php echo $results->getFieldData(1,5); ?></td>
<td><?php echo $results->getFieldData(1,6); ?></td>
<td><?php echo $results->getFieldData(1,7); ?></td>
</tr>

[craftgirl]

Dina, I'm also trying to get a form to write to a SQL dbase, and I'm wondering if all the info (domain name, user name and password) are written in the thankyou.php file can't this been seen by everyone by "view source"?

[sjlplat]

Dina, I'm also trying to get a form to write to a SQL dbase, and I'm wondering if all the info (domain name, user name and password) are written in the thankyou.php file can't this been seen by everyone by "view source"?

Variables in PHP can't be seen by looking at the page source.

PHP displays the HTML source to the browser, but all variables are maintained on the server side.

[serversn]

Hi Dinaglen, I'm sorry that I didn't see this post earlier... okay, I checked your submission form... and the results and I'm noticing that as you said, some rows aren't showing... I believe somewhere along the line, your loop is going over for some reason and I won't be able to tell you why unless you show me the UPDATED code for your file1.php . Please private message me with the code if you want me to solve your problem. I'm certain there aren't any errors with retrieval via the rdatabase object since I've tested this database object on all my websites and it works fine.. but I'm definitely willing to help you fix the issue so please contact me back! :)

[serversn]

Hi, I just read the part of the message where you said you catered for 100 people... okay... this is how I would do it

Say for example, the query to retrieve the data was something like

$results = $db->query("SELECT * FROM table");

Try this instead,

$results = $db->query("SELECT * FROM table LIMIT 100");

Then...

$results_rows = $results->rows();

The above line would tell you how much rows you have if for some reason, less than 100 rows are in the table

hence you would then do a for loop such as the one below to go through each row of results

for ($i=0; $i < $results_rows; $i++) {
echo $results->getFieldData($i, 1); // col 1 of row $i
echo $results->getFieldData($i, 2); // col 2 of row $i

//OR CHECK THE LINE BELOW (you can do this if you know the fieldname)
echo $results->getFieldData($i, "whatever-the-fieldname-is");

}

Does this help or just make it more complicated?