SQL INSERT INTO Table

Hello everyone,

I have learned from W3 School how to create a basic PHP code to store data from HTML Form which is as following:

<?php
$con = mysql_connect("localhost", "databaseuser", "password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("databasename", $con);
 
date_default_timezone_set('GMT');
$date = date("Y-m-d G:i:s") ;
$sql="INSERT INTO collect (submitdate, first_name, last_name, email)
VALUES
('$date','$_POST[first_name]','$_POST[last_name]','$_POST[email]')";
 
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "Thank You!";
 
mysql_close($con);
?>

PHP:

I was wondering if there any other way to achieve the same purpose. Could you please suggest whether or not it is possible to create it differently?

Thanks in advance!

 

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers
You should use PDO. See some examples on the link.

 

Mysql_connect ... are the old method, and now from php5 because they work with OOP, it's preferable to use PDO

 

Use this instead and always stick to current standards. The old style queries are outdated and incredibly insecure. Using PDO is my preference, alternatively you could use mySQLi. Using this method adds a layer of security to your queries and prevents injections.

databaseconnect.php

 
$db_myHost = "hostname";
$dbmyDatabase = "database";
$db_myUser = "username";
$db_myPassword = "password";
try
   {
   $dbPDO = new PDO('mysql:host='.$db_myHost.';dbname='.$db_myDatabase, $db_myUser, $db_myPassword);
   $dbPDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   }
catch  (PDOException $e)
   {
    echo "Error!: " . $e->getMessage() . "
";
    die();
   }
 

PHP:

yourpage.php

$sth = $dbconn->prepare("INSERT INTO collect (submitdate, first_name, last_name, email) VALUES (:date,:first_name,:last_name,:email)");
 
$params = array("date" => date(Y-m-d G:i:s), "first_name" => $_POST[first_name], "last_name" => $_POST[last_name], "email" => $_POST[email]);
$sth->execute($params);
 
   if ($row = $sth->fetch()) {
   
   echo "Thank you";
 
   }

PHP:

 

Uhm... no... not only is that ugly code it's also broken. Execute doesn't return a fetch set on insert other than an empty array.... so that test would ALWAYS return true... no reason to waste and extra variable on the params, and of course don't give the poor guy actually LEGIBLE code, no, stuff it all into one line -- that makes everything easier... as do the needlessly cryptic names . (gentle ribbing on the latter part)

Of course the re-re use of double quotes and extra echo statement for nothing

Database connection

/*
	LEARN about DSN's -- they're important when you want to connect via
	sockets instead of ports, use SQL systems other than PDO, set up a
	non-standard port, etc, etc.
*/

$dbDSN = 'mysql:host=localhost;dbname=database';
$dbUser     = 'username';
$dbPassword = 'password';

try {
	$db = new PDO($dbDSN,$dbUser,$dbPassword);
} catch  (PDOException $e) {
	die('PDO Error: '.$e->getMessage());
}

Code (markup):

doing the work.

$statement = $db->prepare('
	INSERT INTO collect (
		submitdate, first_name, last_name, email
	) VALUES (
		:date, :first_name, :last_name, :email
	)
');

if ($statement->execute(array(
	'date' => date(Y-m-d G:i:s),
	'first_name' => $_POST[first_name],
	'last_name' => $_POST[last_name],
	'email' => $_POST[email]
))) {
	echo 'Thank you';
} else {
	/* put an error handler here, something went wrong! */
}

Code (markup):

 

Oh, sidenote, since you're plugging in the PHP date... I'd be asking why isn't that field of type DATETIME? Then you could just plug in NOW() into the query instead of screwing around with PHP dates.

 

This is interesting. can you explain further what this can do better? Thanks

 

Thanks for your inputs to this. Well, I don't see this is working with date i.e. 'date' => date(Y-m-d G:i:s), can you explain what does execute(array( mean? Thanks

 

Oops, even more stuff that was broken in Scott's I failed to correct for -- like it missing the colons in the array indexes and the lack of proper quotes around string values.

It should be:

if ($statement->execute(array(
   ':date' => date('Y-m-d G:i:s'),
   ':first_name' => $_POST['first_name'],
   ':last_name' => $_POST['last_name'],
   ':email' => $_POST['email']
))) {

Code (markup):

What I get for not looking too close and doing a drive-by post.

PDO's execute parameter can be passed an array to plug in values to a prepared query. This auto-sanitizes the values in the array so you can do things like plug-in $_POST directly without any extra escaping of values or worrying about script injections.

Pretty much in the prepare where you see :date, the value indexed as :date in the array is plugged in there.

One of the really nice features of this (that a LOT of people using prepared queries don't realize) is that you can actually perform more than one $statement->execute on a prepared query... so if for example you had ten or twelve records you were inserting with the same basic query, you could just prepare once and execute the array many times. You can see that in action in a demo I wrote for another user where I made a routine to just plug in 200 dummy records:

http://www.cutcodedown.com/for_others/trenttdogg/sqlToMarkup/populate.phps

I prepare once, but execute 200 times.

Prepare/exec is how it SHOULD be done using PDO or mySQLi, if for no other reason than the auto-sanitizing of values. PDO makes it more convenient since you can use an array on it's execute method and can actually name your values-- though it can also use a method called 'bindParam' similar to how mySQLi handles it.

 

Fascinates me the depth and range of your knowledge. Normally you'd have somebody who's really good at programming and another person who's really good at designing but you seem to do both really well.

Even minor things like not creating an extra variable using $params (where I see all the time when I've researched PDO, my bad I suppose for going for tutorial websites). Very minor thing but speeds things up that little bit extra.

Comes with experience I suppose.

 

You've got a problem with superglobals vars.

$_POST["first_name"]','$_POST["last_name"]','$_POST["email"]

Try it...