I posted this earlier tonight in another forum on PHPBuilder... perhaps this could help.
Kerry Kobashi
Kobashi Computing
Let's start off with a very simple example. Granted, I'm writing this on-the-fly at the top of my head so check for errors. However, you should get the idea.
Let's do a simple example of inserting a firstname and lastname into a mySQL database from a HTML form for sake of brevity.
mySchema.sql
mySchema.sql is a SQL text file (just create it in a text editor and save it). It creates a database named "myUser" with a single table in it called "UserInfo". The database contains three fields:
a) userId - this is a primary key that is generated by mySQL to maintain uniqueness and maintain data integrity among records
b) firstname - this is a variable length character field
c) lastname - this is a variable length character field
create database myUser;
use myUser;
create table UserInfo
(
userId int unsigned NOT NULL auto_increment primary key,
firstname varchar(64) NOT NULL,
lastname varchar(64) NOT NULL
);
I am assuming you are using mySQL as your database. If so, simply redirect this from your command shell into mySQL as follows (assuming Windows platform):
c:/mySql/bin/mySQL < mySchema.sql
Now, you have a database named "myUser" with one table in it called "UserInfo". The table has no records in it.
myForm.php
Next, lets create a form that will allow new users to be added to the database. myForm.php is all HTML presentation layer markup for the form. It is best to keep presentation and business logic separately. (note: although there is no PHP code in this file, I gave it a php extension anyways - html would be just as good).
Three things to be aware of:
1) Upon pressing the submit button, control transfers to myInsert.php
2) We are using an HTTP POST to submit our data
3) Pay attention to the names of the form fields as these will be the names of the post variables
<html>
<head><title>myForm</title></head>
<body>
<form method="POST" action="myInsert.php">
<table cellpadding="0" cellspacing="0" width="600">
<tr>
<td width="50%">
First name
</td>
<td width="50%">
<input type="text" name="firstname" size="20">
</td>
</tr>
<tr>
<td width="50%">
Last name
</td>
<td width="50%">
<input type="text" name="lastname" size="20">
</td>
</tr>
<tr>
<td width="50%">
</td>
<td width="50%">
<input type="submit" value="Submit" name="btnSubmit">
</td>
</tr>
</table>
</form>
</body>
</html>
myInsert.php
When the user hits the submit button, control is passed here. This is all business logic and contains no html markup presentation. The purpose of this file is to accomplish these things:
1) Validate the data and check for variable existence
2) Insert the data into the database
3) Redirect to an error page in case of errors
4) Redirect to a success page if no errors
<?php
// check to see if firstname is passed as part of the query string. If it doesn't exist, redirect to an error page and exit immediately
This could happen if some hacker or a user bookmarked this page and came into it by accident. You want to check to see if the variables exist
if (!array_key_exists("firstname", $_POST)) {
$loc = "http://www.yoursite.com/myError.php?e=1";
header("Location: " . $loc);
exit(1);
}
// check to see if firstname is passed as part of the query string. If it doesn't exist, redirect to an error page and exit immediately
if (!array_key_exists("lastname", $_POST)) {
$loc = "http://www.yoursite.com/myError.php?e=2";
header("Location: " . $loc);
exit(1);
}
// At this point we know firstname and lastname exist so store them in variables
$firstname = $POST['firstname'];
$lastname = $POST['lastname'];
// Here you would do some validation and checking (like bad names, too long of a name, duplicate names, etc.). Assume DoValidation() accomplishes this and returns true if data is ok, false if it is bad. It is up to you to write this function.
if (DoValidation($firstname, $lastname) == false) {
$loc = "http://www.yoursite.com/myError.php"?e=3;
header("Location: " . $loc);
exit(1);
}
// ok now you have the variables stored and its time to persist the data in your mySQL database.
// First lets connect to mySQL. Here you will put in the host name of the server/IP address to the database, the administrator username and password
$dbLink = mysql_connect("localhost", "username", "password");
if ($dbLink == false) {
$loc = "http://www.yoursite.com/myError.php?e=4";
header("Location: " . $loc);
exit(1);
}
// Second, you must select a database you want to work with. This is "myUser" database as we setup above in mySchema.sql
$result = mysql_select_db("userInfo");
if ($result == false) {
$loc = "http://www.yoursite.com/myError.php?e=5";
header("Location: " . $loc);
exit(1);
}
// Third, we prepare the SQL statement to insert into the database. Notice we do not specify the userId column as this is automatically generated by mySQL
$sql = "insert into userinfo (firstname, lastname) values ('$firstname', '$lastname')";
// Fourth, we execute the query
$result = mysql_query($sql, $dbLink);
if ($result == false) {
$loc = "http://www.yoursite.com/myError.php?e=6";
header("Location: " . $loc);
exit(1);
}
// Fifth, close the database connection
$result = mysql_close($dbLink);
if ($dbLink == false) {
$loc = "http://www.yoursite.com/myError.php?e=7";
header("Location: " . $loc);
exit(1);
}
// Finally, redirect to a success page
$loc = "http://www.yoursite.com/mySuccess.php";
header("Location: " . $loc);
exit(0);
?>
Good luck!
Kerry Kobashi
Kobashi Computing
