Converting my apps mysql to PDO

Question

I have been recently made aware that mysql ext is being removed in pHP in the future and so i have to convert to mysqli or PDO ext…

It seems like converting to PDO will be easier and so I am first trying that. I have a simple mysql app here that I am trying to convert to PDO; here it is:

$hostname='localhost';
$username='user11111';
$password='gpassword1';
$dbname='gman_school_db';
$usertable='your_tablename';
$yourfield = 'your_field';

$db = new PDO("mysql:host=$hostname;dbname=$dbname", "$username", "$password");

$sql = "INSERT INTO members (user_id, user_name, email)
VALUES (680, 'GMAN678BABY333333', 'email_ok_man')";

mysql_query($sql);

$db = null;

I pieced this together finding some conversion code but it is not working, I do not know if my connect stuff is wrong in some way or if my "insert statement" cannot work with this PDO connection; so I am hoping someone can tell me what is wrong with this…

Thanks!

*******UPDATED ATTEMPT 2:22am EST ***************

here is my attempt using info from both Fred and Hankypanky:

/* Connect to a mysql database using driver invocation */
$dsn = 'mysql:dbname=gman_db1;host=localhost';
$user = 'gman_user1';
$password = 'gman54678';

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

/* Execute a prepared statement by binding PHP variables */

$user_id=699;
$user_name="wayne_sax";
$email="wayne@saxman.com";

$sth = $dbh->prepare('INSERT INTO members (user_id, user_name,  email) VALUES (:user_id, :user_name, :email)');

$sth->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$sth->bindParam(':user_name', $user_name, PDO::PARAM_STR, 12);
$sth->bindParam(':email', $email, PDO::PARAM_STR, 12);
$sth->execute();

I am not getting any errors when I run it; but it does not insert the record properly so something is not right; so if anyone can see anything wrong with this please let me know…

All the best, G

** update 3:34am set * using hanky pankys code - I got the following errors:

Array ( [0] => 00000 [1] => [2] => )
Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the  manual that corresponds to your MySQL server version for the right syntax to use near   'INSERT INTO members (user_id, user_name, email) VALUES (699,Â' at line 1 ) 

I also ran Meda's suggestion below and it got a "fatal error" on the execute statement; which makes me think it is connecting OK;

so it looks like my insert statement is messed up; i know how to do it in mysql; looking at this trying to see what is wrong...

Answer 1

Example taken straight from PHP Manual. Which part of it do you not understand specifically?

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?> 

Obviously that assumes you have created a PDO object first, but even that can be seen on the manual.

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

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

?> 

Answer 2

It sound to me like you are not able to get the errors try connecting like this:

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

This way you will find out about the error.