Why won't this PHP script insert form data in MySQL db?

Question

On form submit, I'm getting a blank page (insert.php) with no error and no success message.

This is the form:

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

This is the script:

mysql_select_db("my_db", $con);


$stmt = $db->prepare('INSERT INTO my_table (first_name) VALUES (:first_name)');

$stmt->execute(':first_name', $first_name);


if (!mysql_query($stmt,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>

is that actually all of the code? where are you getting $first_name from? — thescientist, Mar 09 '12 at 16:58

prodigitalson · Answer 1 · 2012-03-09T17:12:38.543

3

Youre trying to use 2 different MySQL interfaces at the same time. The mysql_* family of functions use the ext/mysql extension... The prepared statement stuff is PDO. You need to choose one or the other. Since PDO is really the way to go ill give you an example with that:

$db = new PDO($dsn, $user, $password);

try {
   $stmt = $db->prepare('INSERT INTO my_table (first_name) VALUES (:first_name)');
   if($stmt->execute(array(':first_name' => $first_name))) {
      echo "1 record added";
   }

} catch (PDOException $e) {
  die('Error: ' . $e->getMessage());

}

The docs on the Mysql DSN (the first argument to the PDO constructor) can be found here.

edited Mar 09 '12 at 17:12

answered Mar 09 '12 at 16:59

prodigitalson

60,050
10
100
114

Thanks. I'm on my way again. I guess this wasn't mentioned (or it was assumed I'd know) in [my post yesterday](http://stackoverflow.com/questions/9624292/is-this-a-secure-method-to-insert-form-data-into-a-mysql-database). – Miles Pfefferle Mar 09 '12 at 17:08
Yeah it was probably assumed youd not just copy and paste, but rather go and investigate the Docs for PDO and then realize its a completely different extension with a different interface from the examples found there. – prodigitalson Mar 09 '12 at 17:19

Michael Berkowski · Accepted Answer · 2012-03-09T17:03:46.070

You need to create a PDO object to be able to use prepared statements. Instead you have opened a connection with mysql_connect(). The two do not mix, and PDO is preferred between them as it is more easily secured through the use of prepared statements (among other reasons).

From the PDO docs:

// This establishes your connection using PDO.
// The PDO connection object is $db

/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $db = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Pass an associative array to execute(), rather than a list of arguments representing your placeholders. The

// Now that the PDO object is successfully created, prepare your statement
$stmt = $db->prepare('INSERT INTO my_table (first_name) VALUES (:first_name)');

// Arg to execute() should be an associative array
$stmt->execute(array(':first_name' => $first_name));

The following call to mysql_query() is unnecessary, as you have already executed the prepared statement with PDO.

// Don't do this
// mysql_select_db("my_db", $con);

// Or this...
//if (!mysql_query($stmt,$con))
//{
//  die('Error: ' . mysql_error());
//}

// Or this...
// mysql_close($con)

Why won't this PHP script insert form data in MySQL db?

2 Answers2