Trying to translate old mysql_query to new PDO method, how to create variable element

Question

I am only having my very first attempt to use the new method, since I was advised to begin using PDO method to instead of the old one for querying the DB.

I cannot figure out how to place the variable into it as I was doing previously with my old one.

here is an old version of my script

$file_code = filter_input( INPUT_GET, 'fileid' );
$res = mysql_query("SELECT * FROM `Files` WHERE `fileID`='".$file_code."'") or die ( mysql_error() );
if(mysql_num_rows($res) == 0)
{
die(header("Location: error.php"));
} 
$info = mysql_fetch_assoc($res);

and here is my attempt to use the new way to achieve the same result

$file_code = filter_input( INPUT_GET, 'fileid' );
$db = new PDO($host,$db_name,$db_user,$db_pass);
$res = $db->prepare('SELECT * FROM Files WHERE fileID = :".$file_code."');

Can you please help me with it since I am not a PRO in php

Thanks in advance

Thanks guys for pointing me to the right direction, this is exactly why I love Stackoverflow — AlexB, Mar 06 '13 at 18:37

score 1 · Answer 1 · answered Mar 06 '13 at 18:36

$file_code = filter_input(INPUT_GET, 'fileid');
$db = new PDO("mysql:host=$host;dbname=$dbname;","login","pass");

$res = $db->prepare('SELECT * FROM Files WHERE fileID = ?')
                                          ->execute(array($file_code));

$result = $res->fetchAll();

score 1 · Accepted Answer · answered Mar 06 '13 at 18:37

$res = $db->prepare('SELECT * FROM Files WHERE fileID = :".$file_code."');

What you are doing here is really negating the whole concept of prepared statements.

This is what you should do:

$res = $db->prepare('SELECT * FROM Files WHERE fileID = ?');
$res->execute(array($file_code));
$data=$res->fetchAll();

The beauty of prepared statements is in the fact that you don't need to escape your variables and it's also more efficient if you run it more times.

score 1 · Answer 3 · answered Mar 06 '13 at 18:40

1

There is two ways:

$stmt = $db->prepare('SELECT * FROM Files WHERE fileID = :file_code');
$stmt->bindParam(':file_code', $file_code);
$stmt->execute();

Or

$stmt = $db->prepare('SELECT * FROM Files WHERE fileID = :file_code');
$stmt->execute(array(':file_code' => $file_code));

So you may run PDOStatement::bindParam to pass your values or pass them as an array right to the PDOStatement::execute. The first one is much more flexible.

answered Mar 06 '13 at 18:40

ozahorulia

9,798
8
48
72

Thanks, I was just about to ask about your first method, but you have posted it here already. The only difference I have had is $res->bindParam(':file_code', $file_code, PDO::PARAM_INT); this part PDO::PARAM_INT – AlexB Mar 06 '13 at 18:44

Trying to translate old mysql_query to new PDO method, how to create variable element

3 Answers3