How To Properly Migrate From mysql_ to PDO

Question

UPDATE: This question has been answered I have a simple form where users can update their mysql db. I was using mysql_ to connect to the database but have learned that pdo is a better way to do this since mysql_ is depreciated.

Included below is the complete form the OLD way followed by the complete form the new way. The new way produces an error

OLD WAY:

<?php

$host = 'ip_address';
$user = 'user_name';
$password = 'password';

$link = mysql_connect($host, $user, $password);

$selected = mysql_select_db('db_name', $link);

if(!isset($_POST['text-input']))

?>

<form method="post">
    %slice%
    <input type="submit" value="Submit" />
</form>
%[if !edit]%

<?php
%[repeat items]%
$form_input%id=repeatIndex% =  $_POST['element-%id=repeatIndex%'] ;
%[endrepeat]%

$query = 'INSERT INTO `table_name` (%[repeat items]%%[endif]%%html="Edit Me"%%[if !edit]%,%[endrepeat]%) VALUES (%[repeat items]%"' . $form_input%id=repeatIndex% . '",%[endrepeat]%);';
$query = preg_replace('/,\);/',');',$query);
$query = preg_replace('/,\) /',')',$query);
mysql_query($query);

?>
%[endif]%

The NEW Way:

<?php
db = new PDO('mysql:host=ip_address;dbname=db_name;', 'user_name', 'password');
?> 
    <form method="post">
        %slice%
        <input type="submit" value="Submit" />
    </form>
    %[if !edit]%

<?php
%[repeat items]%
$form_input%id=repeatIndex% =  $_POST['element-%id=repeatIndex%'] ;

%[endrepeat]%

$query = 'INSERT INTO `table_name` (%[repeat items]%%[endif]%%html="Edit Me"%%[if !edit]%,%[endrepeat]%) VALUES (%[repeat items]%"' . $form_input%id=repeatIndex% . '",%[endrepeat]%);';
$query = preg_replace('/,\);/',');',$query);
$query = preg_replace('/,\) /',')',$query);
mysql_query($query);


?>
%[endif]%

The errors that gets throw is this:

Warning: mysql_query() [function.mysql-query]: Access denied for user
'kuler'@'localhost' (using password: NO) in
/home/path_to/index.php on line 125

Warning: mysql_query() [function.mysql-query]: A link to the server could not be
established in /home/path_to/index.php on line 125

I hope I have provided enough information.

You are still calling `mysql_query()` with PDO. The two are _not_ compatible. Review the [PDO manual](http://php.net/manual/en/book.pdo.php) for many examples using `prepare()` `execute()` or `query()`. — Michael Berkowski, Aug 17 '12 at 14:58
Please have a look at [this](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) even though it's about SQL injection but it gives you the basic information you need about PDO. — Adi, Aug 17 '12 at 15:00

score 1 · Answer 1 · answered Aug 17 '12 at 22:44

The solution is as follows:

<?php

//Connection vars
$host = '%id=server%';
$user = '%id=username%';
$password = '%id=password%';
$databasename = '%id=database%';

//This block establishes the connection
try{
    //
    $connectiondetails = "mysql:host={$host};dbname={$databasename}";
    $db = new PDO($connectiondetails , $user , $password);
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
}
catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

//Check if all the input fields of the form have been submitted
if(
    %[repeat items]%
    isset($_POST['element-%id=repeatIndex%'])&&
    %[endrepeat]%
    (true===true)
){


    //The variables to read the output
    %[repeat items]%
    $form_input%id=repeatIndex% = $_POST["element-%id=repeatIndex%"] ;
    %[endrepeat]%

    //DB
    global $db;     //must set this, otherwise can't access the db  
    $query =  'INSERT INTO `%id=table%` (%[endif]%%[repeat items]%%html="Edit Me"%%[if !edit]%,%[endif]%%[endrepeat]%%[if !edit]%)%[endif]% %[if !edit]%VALUES (%[repeat items]%"' . $form_input%id=repeatIndex% . '",%[endrepeat]%);';
    $query = preg_replace('/,\);/',');',$query);
    $query = preg_replace('/,\) /',')',$query);
    $db->query($query);
    //[DB]
}

?>

How To Properly Migrate From mysql_ to PDO

1 Answers1