Script php PDO with myslq

PHP
1

Hi, i’m new here and i’m trying to complete this script. It’s something “simple”…newsletter subscribing.
So i’ve a database and i was using mysql with php and i was having a injection problem, than i read that mysql is obsolute, so i tried PDO connection.

I want to know if this script is correct, if i’ve some newbie errors or if i can rock on with this one.

<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try {
    //connect as appropriate as above
    $db->query('hi'); //invalid query!
} catch(PDOException $ex) {
    echo "An Error occured!"; //user friendly message
    some_logging_function($ex->getMessage());
}

if (isset($_POST['nome']) && isset($_POST['email'])){
        if(mysql_query("INSERT INTO email_list (nome, email) VALUES ('".$_POST['nome']."', '".$_POST['email']."')")) 
    {
        echo "O seu email foi adicionado! Obrigada. Your email has been added to our list! Thank You.";
    }else {
        echo "Houve um erro ao adicionar o seu email. Por favor tente novamente. There was an error adding your email to our list. Please try again.";
    }
}
else {
    echo "Input all required field";
}
    
?>
2

First of all, does your code work at all? Second, why not use prepared statements that pdo provides. I think even pdo filters data I like to sanitize them before I process 'em.

3

You just executed mysql_query after making a PDO connection? mysql_query is not part of the pdo family, its deprecated.

Actually after googling to find you a biginners place, I see your following this: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

You want to use this to query: $db->query(‘select foo from bar’)->fetchAll(); But thats only the beginning of what you need to start understanding. Start reading some of the different objects here: http://www.php.net/manual/en/book.pdo.php

4

First of all thank you for the reply =)

ok…so this part is ok right?

 <?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try {
    //connect as appropriate as above
    $db->query('hi'); //invalid query!
} catch(PDOException $ex) {
    echo "An Error occured!"; //user friendly message
    some_logging_function($ex->getMessage());
}

…and the rest i need to work better.
I read a lot of things and the connection + the error part it’s fine i guess, i just take the steps they spoke too.
What do you think?

5

People i need help…probably i didn’t explain well!

So i have this script

<?php

$con = mysql_connect("HOST_NAME","USERNAME","PASSWORD") or die('Could not connect: ' . mysql_error());

mysql_select_db("DATABASE_NAME", $con);

if(mysql_query("INSERT INTO email_list (fullname, email) VALUES ('".$_POST['fullname']."', '".$_POST['email']."')")) {

echo "Your email has been added to our list!";

} else {

echo "There was an error adding your email to our list. Please try again.";

}

mysql_close($con);

?>

Than i tried to put in PDO but i can’t…i put every code that i tried in this post.
The Last was that:

&lt;?php
$db = new PDO('mysql:host=localhost;dbname=XXX;charset=utf8', 'XXX', 'XX');
$db-&gt;setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try {
    //connect as appropriate as above
    $db-&gt;query('hi'); //invalid query!
} catch(PDOException $ex) {
    echo "An Error occured!"; //user friendly message
}

$stmt = $pdo-&gt;query("INSERT INTO `email_list` (nome, email) VALUES (:nome, :email)");
$stmt-&gt;execute

?&gt;

and i received this error “ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘hi’ at line 1”

I can’t understand why, someone can help me?

6 
&lt;?php
try {

$db = new PDO('mysql:host=localhost;dbname=XXX;charset=utf8', 'XXX', 'XX');
$db-&gt;setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    //connect as appropriate as above
    $db-&gt;query("hi"); //invalid query!
$db = null;
} catch(PDOException $e) {
     echo 'ERROR: ' . $e-&gt;getMessage(); }

$stmt = $pdo-&gt;query("INSERT INTO `email_list` (nome, email) VALUES (:nome, :email)");
$stmt-&gt;bindParam(':nome', $name);
$stmt-&gt;bindParam(':email', $email);
$stmt-&gt;execute();

?&gt;
7

Hey, i’ve tried that code but i’ve the same error.

ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘hi’ at line 1
Fatal error: Call to a member function query() on a non-object in /home/xxx/xxx/xxx on line 14

I can’t find the answer anywhere. I google a lot for this subject but i can’t find an answer.
I just want that my clients can subscribe to my newsletter, they put their name and email than they submit and after that i can see their information in mysql.

What i’m doing wrong?!

8

extension=php_pdo_mysql.dll

Have a look through your phi.ini file for the above line, if there is a ; at the start of the line, remove the ; from the start of the line, save and then restart the server

9

I spoke with my host company and they said that the extension=php_pdo_mysql.dll is enable. But i received the same error…

If you had the code

<?php

$con = mysql_connect("HOST_NAME","USERNAME","PASSWORD") or die('Could not connect: ' . mysql_error());

mysql_select_db("DATABASE_NAME", $con);

if(mysql_query("INSERT INTO email_list (fullname, email) VALUES ('".$_POST['fullname']."', '".$_POST['email']."')")) {

echo "Your email has been added to our list!";

} else {

echo "There was an error adding your email to our list. Please try again.";

}

mysql_close($con);

?>

How do you put if PHP PDO? It isn’t the way i’m doing?

10

The error you were getting was because you were calling $pdo->query() but $pdo doesn’t exist. The pdo instance was assigned $db. Try the following.


&lt;?php

$db = new PDO('mysql:host=localhost;dbname=XXX;charset=utf8', 'XXX', 'XX');
$db-&gt;setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try {
    // insert values into database
    $stmt = $db-&gt;query("INSERT INTO `email_list` (nome, email) VALUES (:nome, :email)");
    $stmt-&gt;bindValue(':nome', 'first last');
    $stmt-&gt;bindValue(':email', 'person@example.com');
    $stmt-&gt;execute();

    // query successful
    echo 'Your email has been added to our list!';
} catch(PDOException $ex) {
    echo 'There was an error adding your email to our list. Please try again.'; //user friendly message
}

?&gt;
11

THANK YOU!!! I was thinking right but in the wrong path…

Now i just need to see/learn how to avoid duplicate.

This script was killing my mind =P