Help with SQL


#1

I am not sure this is a beginner question but, not really sure what other section it would fit.

Normally I search and search till I find the answer myself but this time it seems the answers are just confusing.

I am trying to setup a form mail (which isn’t a problem that I think I can do with lil effort) I have setup one before on another website. My question is…we are wanting the answers that are submitted from the form mail to go into a database and be stored somewhere. I was told this could be done with a SQL. Could someone please point me to a place where I could find information on how to set this up if it is truley possible. Always before I just entered the information into a Access database that I created myself but it would be easier if it was all done automatically.

Thank you in advance for your time.

One Smile can make a difference


#2

Given a form like this one:

[code]<?php
session_start();
$title = ($_SESSION[‘title’]) ? $_SESSION[‘title’] : “”;
$first_name = ($_SESSION[‘first_name’]) ? $_SESSION[‘first_name’] : “”;
$last_name = ($_SESSION[‘last_name’]) ? $_SESSION[‘last_name’] : “”;
$email = ($_SESSION[‘email’]) ? $_SESSION[‘email’] : “”;
$day_phone = ($_SESSION[‘day_phone’]) ? $_SESSION[‘day_phone’] : “”;
$night_phone = ($_SESSION[‘night_phone’]) ? $_SESSION[‘night_phone’] : “”;
$cell_phone = ($_SESSION[‘cell_phone’]) ? $_SESSION[‘cell_phone’] : “”;
$street1 = ($_SESSION[‘street1’]) ? $_SESSION[‘street1’] : “”;
$street2 = ($_SESSION[‘street2’]) ? $_SESSION[‘street2’] : “”;
$city = ($_SESSION[‘city’]) ? $_SESSION[‘city’] : “”;
$state = ($_SESSION[‘state’]) ? $_SESSION[‘state’] : “”;
$zip = ($_SESSION[‘zip’]) ? $_SESSION[‘zip’] : “”;
$comments = ($_SESSION[‘comments’]) ? $_SESSION[‘comments’] : “”;
$error = ($_SESSION[‘error’]) ? $_SESSION[‘error’] : “”;

header(“Content-Type: text/html;charset=UTF-8”);

?>

Form

Contact Form

<?php if($error != "") { print "

$error

\n"; } ?>

Please note: Required information is marked with an asterisk (*).

Title*:
First Name*:
Last Name*:
Email Address*:
Daytime Phone Number*:
Evening Phone Number*:
Cell Phone Number:
Street Address 1*:
Street Address 2:
City*:
State*:
Zip*:
Comments:
<?php print $comments ?>

[/code]You could feed the data into a database with this:

[code]<?php

session_start();
session_register(“title”);
session_register(“first_name”);
session_register(“last_name”);
session_register(“email”);
session_register(“day_phone”);
session_register(“night_phone”);
session_register(“cell_phone”);
session_register(“street1”);
session_register(“street2”);
session_register(“city”);
session_register(“state”);
session_register(“zip”);
session_register(“comments”);
session_register(“error”);

// check if form was submitted
if(!$_POST) {
header(“Location: /”);
exit;
}

header(“Content-Type: text/html;charset=UTF-8”);

// convert POST variables into local variables
$title = $_POST[‘title’];
$first_name = $_POST[‘first_name’];
$last_name = $_POST[‘last_name’];
$email = $_POST[‘email’];
$day_phone = $_POST[‘day_phone’];
$night_phone = $_POST[‘night_phone’];
$cell_phone = $_POST[‘cell_phone’];
$street1 = $_POST[‘street1’];
$street2 = $_POST[‘street2’];
$city = $_POST[‘city’];
$state = $_POST[‘state’];
$zip = $_POST[‘zip’];
$comments = $_POST[‘comments’];
$error = $_POST[‘error’];

// create session variables
$_SESSION[‘title’] = $title;
$_SESSION[‘first_name’] = $first_name;
$_SESSION[‘last_name’] = $last_name;
$_SESSION[‘email’] = $email;
$_SESSION[‘day_phone’] = $day_phone;
$_SESSION[‘night_phone’] = $night_phone;
$_SESSION[‘cell_phone’] = $cell_phone;
$_SESSION[‘street1’] = $street1;
$_SESSION[‘street2’] = $street2;
$_SESSION[‘city’] = $city;
$_SESSION[‘state’] = $state;
$_SESSION[‘zip’] = $zip;
$_SESSION[‘comments’] = $comments;
$_SESSION[‘error’] = $error;

$regexp = “^([_a-z0-9-] )(.[_a-z0-9-] )@([a-z0-9-] )(.[a-z0-9-] )(.[a-z]{2,4})$”;

// check required fields
if($title == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a title.”;
header(“Location: /contact/”);
exit;
}
if($first_name == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a first name.”;
header(“Location: /contact/”);
exit;
}
if($last_name == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a last name.”;
header(“Location: /contact/”);
exit;
}
if($email == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered an email address.”;
header(“Location: /contact/”);
exit;
}
// check if email address is valid
if(!eregi($regexp,$email)) {
$_SESSION[‘error’] = “The email address you have entered appears to be invalid. Please enter a valid email address.”;
header(“Location: /contact/”);
exit;
}
if($day_phone == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a daytime phone number.”;
header(“Location: /contact/”);
exit;
}
if($night_phone == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered an evening phone number.”;
header(“Location: /contact/”);
exit;
}
if($street1 == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a street address.”;
header(“Location: /contact/”);
exit;
}
if($city == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a city.”;
header(“Location: /contact/”);
exit;
}
if($state == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a state.”;
header(“Location: /contact/”);
exit;
}
if($zip == “”) {
$_SESSION[‘error’] = “You must fill in all required information. You have not entered a zip code.”;
header(“Location: /contact/”);
exit;
}

// If the script gets down to here, the user has entered everything correctly!

// get IP address
$ip = $_SERVER[‘REMOTE_ADDR’];

// make timestamp
$now = time();
$timestamp = date(“YmdHis”,$now);

// initialize database variables
$hostname = “”; // your hostname
$username = “”; // your database username
$password = “”; // your database password
$database = “”; // the name of your database

// connect to database
$link = mysql_connect($hostname,$username,$password);
mysql_select_db($database);

// insert information into database
$sql = “insert into contacts (timestamp, ip, title, first_name, last_name, email, day_phone, night_phone, cell_phone,
street1, street2, city, state, zip, comments) values (’$timestamp’, ‘$ip’, ‘$title’, ‘$first_name’, ‘$last_name’,
’$email’, ‘$day_phone’, ‘$night_phone’, ‘$cell_phone’, ‘$street1’, ‘$street2’, ‘$city’, ‘$state’, ‘$zip’, ‘$comments’)”;
$query = mysql_query($sql,$link) or die("Could not insert data: ".mysql_error());

// compose email
$message = “Thank you for submitting comments. Here is the information you sent to us:\n\n”;
$message = $message.“Title: $title\n”;
$message = $message.“First Name: “.stripslashes($first_name).”\n”;
$message = $message.“Last Name: “.stripslashes($last_name).”\n”;
$message = $message.“Email: $email\n”;
$message = $message.“Daytime Phone Number: $day_phone\n”;
$message = $message.“Evening Phone Number: $night_phone\n”;
$message = $message.“Cellular Phone: $cell_phone\n”;
$message = $message.“Street Address 1: “.stripslashes($street1).”\n”;
$message = $message.“Street Address 2: “.stripslashes($street2).”\n”;
$message = $message.“City: “.stripslashes($city).”\n”;
$message = $message.“State: $state\n”;
$message = $message.“Zip Code: $zip\n”;
$message = $message.“Comments:\n”.stripslashes($comments);

// send mail
$to = “$email”;
$from = “”; // your email address
$subject = “Contact Repsonse”;
mail ( “$to”, “$subject”, “$message”, “$from” );

// redirect to home page once finished
header(“Location: /”);
?>[/code]That script will add the information into a database, and then send a confirmation email back to the sender. It’ll also send the user back to the form if required fields are missing (which is why I’ve made use of session variables).

You could access the information from a simple table:

[code]<?php

// initialize database variables
$hostname = “”; // your hostname
$username = “”; // your database username
$password = “”; // your database password
$database = “”; // the name of your database

// connect to database
$link = mysql_connect($hostname,$username,$password);
mysql_select_db($database);

// get contacts data
$sql = “select * from contacts order by timestamp desc”;
$query = mysql_query($sql,$link) or die("Could not retrieve contacts: ".mysql_error());

function make_date($timestamp) {
$yr = substr($timestamp,0,4);
$mo = substr($timestamp,4,2);
$dy = substr($timestamp,6,2);
$hr = substr($timestamp,8,2);
$mn = substr($timestamp,10,2);
$sc = substr($timestamp,12,2);
$timestamp = date(“l, F d, Y”,mktime($hr,$mn,$sc,$mo,$dy,$yr));
return $timestamp;
}

header(“Content-Type: text/html;charset=UTF-8”);

?>

List of contacts

List of Contacts

<?php while($row = mysql_fetch_assoc($query)) { $title = $row['title']; $first_name = $row['first_name']; $last_name = $row['last_name']; $email = $row['email']; $day_phone = $row['day_phone']; $night_phone = $row['night_phone']; $cell_phone = $row['cell_phone']; $street1 = $row['street1']; $street2 = $row['street2']; $city = $row['city']; $state = $row['state']; $zip = $row['zip']; $comments = $row['comments']; $timestamp = $row['timestamp']; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; print " \n"; } ?>
Title First Name Last Name Email Address Daytime Phone Evening Phone Cell Phone Street Address 1 Street Address 2 City State Zip Comments Date submitted
$title$first_name$last_name$email$day_phone$night_phone$cell_phone$street1$street2$city$state$zip".nl2br($comments)."".make_date($timestamp)."
[/code]I hope you find that useful.

Simon Jessey
Keystone Websites | si-blog


#3

Thank you so much that will definately help LOTS.

Is there a site somewhere that I can read up on the how to’s on developing SQL databases (for website builders/designers?)

I am someone that loves to learn new things and this is something that has me stumped.

One Smile can make a difference


#4

For basic information about relational databases, take a look at Marc-Jason Dominus’ Short Guide to DBI http:// http://www.perl.com/pub/a/1999/10/DBI.html. It is oriented toward Perl rather then PHP, but gives you a lot of basics about relational databases.

For learning more about MySQL, the searchable documentation available on their site is quite helpful: http://dev.mysql.com/doc/mysql/en/index.html


#5

Thank you both very much.

I have printed out a lot of the materials and going to go find a nice spot and read hopefully I will figure it out (crosses fingers.)

You have both been very helpful and kind.

One Smile can make a difference


#6

suggestion; add ‘isset’ in front of all them session checks on the initial lines. That way it’ll pass PHP’s ~E_NOTICE error checking.

W/out that, you’ll get ‘Notice: using undefined index’ for each line.


#7

Actually, you don’t get that error. Each of those lines is making use of the ternary operator, which is checking for the existence of the existence of the session variable. If the variable does not exist, an empty string is substituted.


Simon Jessey
Keystone Websites | si-blog


#8

You could always put @ at the start of the declaration. But I prefer to use the other method of
isset($_SESSION) or $_POST… that way I can give it a default value if it doesn’t exist. Useful for things like carrying page numbers when a user has just entered a multi-page website and doesn’t have a variable in the address bar.


#9

That is what the script is doing. For example:$title = ($_SESSION['title']) ? $_SESSION['title'] : "";In English, that says “if the session variable ‘title’ exists, assign that value to $title. Otherwise, give $title a default value of “” (empty string).” There is no need to supress any error message, because no error message is given.


Simon Jessey
Keystone Websites | si-blog