Conversion to PDO

[jdadwilson]

I have read a lot about using PDO and would very much like to make the change. My questions is... is it an all or nothing change or can I make the change in the connection but then still use the current mySQL query structure within a module and the PDO structure within another? I have multiple sites, one of which has over 100 modules.

TIA for any assistance.
jdadwilson

[traq]

You can use both, sure - though I would recommend converting everything, just to avoid confusion. They **are not interoperable**, however: you can't use mysql_query(), for example, on a PDO connection.

as for converting, you'll be able to use the same SQL for the most part. The difference will be in how you use the functions. If you have an example, I can show you how it might be converted.

[jdadwilson]

Thanks for the reply. I am working my way through an example. Doing well so far but have come upon one problem. Is it possible to pass a parameter for a field name. I would think so, but am getting an error...

Here is the code...
$selectField = "mem_name_last";
$searchValue = trim($searchValue) . "%";
$querySQL = $db->prepare("SELECT * FROM members_data WHERE ? LIKE ? ORDER BY mem_name_last, mem_name_first");
$querySQL->bindValue(1, $selectField, PDO::PARAM_STR);
$querySQL->bindValue(2, $searchValue, PDO::PARAM_STR);
$querySQL->execute();

TIA
jdadwilson

[traq]

you mean, the WHERE ? part? NO, it is not possible. Only values can be parameterized. (This has to do with how MySQL prepares the statement, not with PDO specifically.) If you want dynamic columns/tables/etc., you have to create a new query each time.

[jdadwilson]

Not sure what you mean by 'create a new query each time'?

With straight mySQL I can do this...

$selectField = "mem_name_last";
$selectValue = "Wilson" . "%";
$querySQL = "SELECT * FROM member_data WHERE $selectField LIKE $searchName ORDER BY mem_name_last, mem_name_first;"

What is needed to do the same thing in PDO?

I am searching the member_data table based on input from the user. There are three ways the table can be searched...
1. Last Name
2. First Name
3. Maiden Name

I assume, based on your reply that I will need to make a separate query for each type.

Thanks Again,
jdadwilson

[traq]

Not sure what you mean by 'create a new query each time'?

With straight mySQL I can do this...

$selectField = "mem_name_last";
$selectValue = "Wilson" . "%";
$querySQL = "SELECT * FROM member_data WHERE $selectField LIKE $searchName ORDER BY mem_name_last, mem_name_first;"

What is needed to do the same thing in PDO?
That's a normal SQL query. Using variables in an SQL statement is completely different than using parameters. First off, variable interpolation happens entirely in PHP: (it looks like SELECT * FROM member_data WHERE mem_name_last LIKE 'Wilson%' ORDER BY mem_name_last, mem_name_first by the time mysql sees it). You can issue it from PDO using ->query() (http://php.net/pdo.query). MySQL prepares the statement, executes it, and then forgets about it; next time you send the query, it creates a new statement.

Using parameters ( ? ) requires a prepared statement (http://php.net/manual/en/class.pdostatement.php), because the processing happens inside MySQL. MySQL prepares the statement once, then remembers it and executes it as many times as you send new parameters (escaping the parameters as data each time - that's why you don't have to escape it, and also why you can't parameterize field names).