PDA

View Full Version : Database Help...


kesowul
01-02-2008, 04:27 PM
I am trying to set up a form used to submit information into a database. I am completely new at this and any help would be great! Here is what I have so far...

On the form page...

<html>
<body>
<form action="insert.php" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>
</body>
</html>


On the page "insert.php" where the connection is being made...

<?php
$con = mysql_connect("localhost:3306","hostname_databasename","hostname_databaseusername","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("my_db", $con);
$sql="INSERT INTO person (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
mysql_close($con)
?>



Just curious if I am doing this right and if I might be able to consolidate this code into one page. Also, is there a specific folder I need to have these files in? Thanks in advance!
linFox
01-02-2008, 05:38 PM
It's almost right, there are a few things to point out though:
In the mysql_connect statement, the parameters are: hostname, username, password. There is no database parameter there, so that should be removed.

In the query string, the string indexes of the array must have quotes around them, otherwise they generate an error and could possibly conflict with PHP constants. Replacing the section:
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";
with
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['age']."')";
should fix that up.

It's quite easy to merge such things into one page, the simplest way is the check several variables to see if they have been sent. If they have, go to the processor. If they haven't, show the HTML.

With all of the following in mind (and some cleanup and just a little security added), the following should do it:


<?php

if ($_SERVER['REQUEST_METHOD'] == "POST" && isset($_POST['firstname'], $_POST['lastname'], $_POST['age']) && !empty($_POST['firstname']) && !empty($_POST['lastname']) && !empty($_POST['age'])) {
$con = mysql_connect("localhost","hostname_databaseusername","password");
if (!$con)
die("Could not connect: ".mysql_error());
mysql_select_db("my_db");
$sql = "INSERT INTO person(FirstName, LastName, Age) VALUES('".htmlentities($_POST['firstname'])."','".htmlentities($_POST['lastname'])."','".htmlentities($_POST['age'])."')";
if (!mysql_query($sql))
die("Error: ".mysql_error());
echo "1 record added";
mysql_close($con);
}
else { ?>
<html>
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>
</body>
</html>
<?php }

?>
kesowul
01-02-2008, 09:56 PM
Thanks for your help! It works fine and helped me figure a bit more about how it all works. Just curious what the line below means specifically ".htmlentities"

VALUES('".htmlentities($_POST['firstname'])."','".htmlentities($_POST['lastname'])."','".htmlentities($_POST['age'])."')"
linFox
01-02-2008, 11:50 PM
The htmlentities function converts all applicable HTML characters (examples: < > ' " ) into their entity codes ( < becomes &lt; etc). This stops any HTML, PHP, etc code from being imported into your database.

This is a simple security measure, because without anything like this, someone could easily come along and inject whatever code they wanted, potentially opening up your site to all sorts of security problems.
kesowul
01-03-2008, 12:53 AM
Thank you for all of your help so far!

Ok, so I've worked on the code a bit and added a few more form fields. I am wondering if I can add a confirmation page to display the user's order...but I would like for the confirmation page to display their total amount of money owed.

ex.
the form field -
Adult Tickets: <input type="text" name="adutix" />
Will be how many adult tickets the user would like to order, but on the confirmation page I would like it to display that number multiplied by the price per ticket...

[adutix]*15.00=

So the if the user ordered 2 adult tickets, the confirmation page would look something like...

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

John Smith
Student Tickets - 0 at 10.00/ticket = $0.00
Adult Tickets - 2 at $15.00/ticket = $30.00

Total = $30.00

Thank you for your purchase!

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

Also the form doesn't work if a field is left blank, any way to fix that?

<?php

if ($_SERVER['REQUEST_METHOD'] == "POST" && isset(

$_POST['firstname'],
$_POST['lastname'],
$_POST['address'],
$_POST['city'],
$_POST['state'],
$_POST['zip'],
$_POST['phone'],
$_POST['email'],
$_POST['thursday'],
$_POST['friday'],
$_POST['saturday'],
$_POST['sunday'],
$_POST['stutix'],
$_POST['adutix']

)

&& !empty($_POST['firstname'])
&& !empty($_POST['lastname'])
&& !empty($_POST['address'])
&& !empty($_POST['city'])
&& !empty($_POST['state'])
&& !empty($_POST['zip'])
&& !empty($_POST['phone'])
&& !empty($_POST['email'])
&& !empty($_POST['thursday'])
&& !empty($_POST['friday'])
&& !empty($_POST['saturday'])
&& !empty($_POST['sunday'])
&& !empty($_POST['stutix'])
&& !empty($_POST['adutix'])

)

{
$con = mysql_connect("localhost","xxxxxxxx","xxxxxxxxxx");
if (!$con)
die("Could not connect: ".mysql_error());
mysql_select_db("mhsdrama_new");
$sql = "INSERT INTO person(FirstName, LastName, Address, City, State, Zip, Phone, Email, Thursday, Friday, Saturday, Sunday, Stutix, Adutix)

VALUES(

'".htmlentities($_POST['firstname'])."',
'".htmlentities($_POST['lastname'])."',
'".htmlentities($_POST['address'])."',
'".htmlentities($_POST['city'])."',
'".htmlentities($_POST['state'])."',
'".htmlentities($_POST['zip'])."',
'".htmlentities($_POST['phone'])."',
'".htmlentities($_POST['email'])."',
'".htmlentities($_POST['thursday'])."',
'".htmlentities($_POST['friday'])."',
'".htmlentities($_POST['saturday'])."',
'".htmlentities($_POST['sunday'])."',
'".htmlentities($_POST['stutix'])."',
'".htmlentities($_POST['adutix'])."'

)";

if (!mysql_query($sql))
die("Error: ".mysql_error());
echo "1 record added";
mysql_close($con);
}
else { ?>
<html>
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Firstname: <input type="text" name="firstname" />
<br>
Lastname: <input type="text" name="lastname" />
<br>
Address: <input type="text" name="address" />
<br>
City: <input type="text" name="city" />
<br>
State: <input type="text" name="state" />
<br>
Zip: <input type="text" name="zip" />
<br>
Phone: <input type="text" name="phone" />
<br>
Email: <input type="text" name="email" />
<br>
Thursday: <input name="thursday" type="text" />
<br>
Friday: <input name="friday" type="text" />
<br>
Saturday: <input name="saturday" type="text" />
<br>
Sunday: <input name="sunday" type="text" />
<br>
Student Tickets: <input type="text" name="stutix" />
<br>
Adult Tickets: <input type="text" name="adutix" />
<br>

<input type="submit" />
</form>
</body>
</html>
<?php }

?>
linFox
01-03-2008, 04:41 AM
If you want to allow any blank field through, just remove the two lines referring to it under the isset( and !empty() sections.

For the confirmation page, it's as simple as placing the html after the query has been completed (as any confirmation would). Right where the line echo "1 record added"; is is the spot (that is a confirmation in itself, ie. after the query suceeds, that gets output).
To add a block of HTML to PHP, just close the PHP tags first ( ?> ), add in your html, then after it, open PHP again (<?php).

For the costs, just open a small block of PHP inside the HTML, to do the calculation (the sprintf() function formats strings, I have used it in the following example to format the number into a currency-type string. Hopefully I did it right...).


[...]
if (!mysql_query($sql))
die("Error: ".mysql_error());
?>
<html>
<head><title>confirm</title></head>
<body>
<p>...confirmation page...</p>
<p>Adult Tickets - <?php echo htmlentities($_POST['adutix']); ?> at $15.00/ticket = $<?php echo sprintf("%01.2f", intval($_POST['adutix'])*15); ?>
</p>...confirmation page...</p>
</body>
</html>
<?php
mysql_close($con);
}
else { ?>
<html>
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
[...]
kesowul
01-03-2008, 11:26 AM
Amazing...it works wonderfully.

Just thought of something else...is there a way to insert that calculated result into my database so I don't have to do the calculations myself? So that when they hit submit it calculates the total and adds that information into a field of my table my database?

Something like...

<p>Total Amount Owed - <?php echo sprintf("%01.2f", intval(($_POST['adutix']*15) + ($_POST['stutix'])*10)); ?>


That works, but then how do I take that equation and turn it into a new field "total" ... and then have it inserted into my database?


Thanks again for the help, I've been using Access Databases for years and had to program very little except for the HTML of the site. I am finally starting to get a bit more familiar with how PHP and SQL works.
linFox
01-03-2008, 07:50 PM
To add something new to the db table, just add the new column to the table (you can do that via phpMyAdmin or whatever you have used to create it in the first place), then add it to the SQL query.

Each value (VALUES('value', 'value')) in the SQL query string can be constructed from any amount of code you want, they are not limited to a single value or expression. Eg:

INSERT INTO person([...], Adutix, totaltixcost) VALUES([...], '".htmlentities($_POST['adutix'])."', ".(intval($_POST['adutix'])*15+intval($_POST['stutix'])*10).")

You'll learn quick if you stick with it. I mean, I've gone from absolute zero to coding several near commercial-grade apps in the space of about a year (Although that might just be me :D)
shadmego
01-03-2008, 09:04 PM
I would like to highlight this thread as a perfect example of the kind of help we are trying to promote here at the forums. I don't work for HM, and am just a simple user like everyone else, but I wanted to give my appreciation to linFox for his professional and complete help during the course of this thread.

I encourage everyone to try to emulate linFox's dedication to helping others learn as much as they can about web sites.

My deepest gratitude to you linFox. If we had awards to hand out, I would definately place one on your account.

~regards
griff671
01-03-2008, 09:28 PM
Three Thumbs-up!
linFox
01-03-2008, 10:38 PM
Wow, thanks. I really appreciate that.
kesowul
01-04-2008, 01:52 AM
Yes, this help has been great. I truly appreciate the time spent giving this noob the resources to get my site up and running. Thanks again!