将mysql转换为pdo [英] convert mysql to pdo

问题描述

所以我有一个应该处理所有数据执行操作的函数:sql

So i have a function thats supposed to handle all data execute operations: sql

function loadResult($sql)  
{      
    $this->connect();    
    $sth = mysql_query($sql);  
    $rows = array();        
    while($r = mysql_fetch_object($sth)) {$rows[] = $r;}        
    $this->disconnect();  
    return $rows;  
}

我想将其转换为pdo，这就是我到目前为止所拥有的:pdo

I want to convert it to pdo and this is what i have so far: pdo

function loadResult($sql)  
{      
    $this->connect();    
    $sth = $this->con->prepare($sql);  
    //execute bind values here  
    $sth->execute();  
    $rows = array();        
    while ( $r = $sth->fetch(PDO::FETCH_OBJ) ) {$rows[] = $r;}      
    $this->disconnect();  
    return $rows;  
}

这是一个函数示例，说明如何使用它来查看数据库中的数据:

Here is an example of a function on how am using it to view data from the database:

function viewtodolist()
{           
    $db=$this->getDbo(); //connect to database 
    $sql="SELECT * FROM mcms_todolist_tasks";  
            //maybe the bind values are pushed into an array and sent to the function below together with the sql statement
    $rows=$db->loadResult($sql);  
    foreach($rows as $row){echo $row->title; //echo some data here  }  
}    

我刚刚提取了重要的代码段，因此某些变量和方法来自其他php类.某种程度上，mysql查询工作正常，但是PDO查询让我头疼的是，如何将bindValue参数包括在 viewtodolist()函数中以使其可重用.欢迎任何建议/建议.

I have just pulled out the important snippets so some variables and methods are from other php classes. Somehow, the mysql query works fine, but the PDO query is giving me headaches on how to include bindValue paremeters most probably in the viewtodolist() function to make it reusable. Any suggestions/recommendations are welcome.

推荐答案

由于您现有的函数接受格式完整的SQL字符串，没有占位符，因此您无需使用prepare + bind.您编写的代码应该可以正常工作，或者您可以使用 PDO::query() 一步执行SQL.

Since your existing function accepts a fully-formed SQL string, with no placeholders, you don't need to use prepare + bind. Your code as written should work fine, or you could use PDO::query() to execute the SQL in one step.

如果要使用参数化查询，那么您的loadResult函数将不得不进行一些更改，就像编写SQL的方式一样.您提供的示例SQL实际上没有任何内容可以转换为参数(列名和表名不能作为参数如此处所述)，但我将使用一种虚构的变体:

If you want to use parameterised queries, then your loadResult function is going to have to change a bit, as is the way you write your SQL. The example SQL you give doesn't actually have anything in that could be turned into a parameter (column names and table names can't be parameters as discussed here), but I'll use an imaginary variation:

// Get the todo tasks for a particular user; the actual user ID is a parameter of the SQL
$sql = "SELECT * FROM mcms_todolist_tasks WHERE user_id = :current_user_id"; 
// Execute that SQL, with the :current_user_id parameter pulled from user input
$rows = $db->loadResult($sql, array(':current_user_id' => $_GET['user']));

这是将用户输入放入查询的一种很好的安全方式，因为MySQL知道哪些部分是参数，哪些部分是SQL本身的一部分，并且SQL部分没有任何人可以干预的变量.

This is a nice secure way of putting the user input into the query, as MySQL knows which parts are parameters and which are part of the SQL itself, and the SQL part has no variables that anyone can interfere with.

与现有的loadResult函数配合使用的最简单方法是:

The simplest way of making this work with your existing loadResult function would be something like this:

// Function now takes an optional second argument
// if not passed, it will default to an empty array, so existing code won't cause errors
function loadResult($sql, $params=array())  
{      
    $this->connect();    
    $sth = $this->con->prepare($sql);  
    // pass the parameters straight to the execute call
    $sth->execute($params); 
    // rest of function remains the same...

您可以通过参数化查询执行更巧妙的操作-例如将变量绑定到输出参数，一次准备查询，然后使用不同的参数多次执行-但是，这将需要对调用代码的工作方式进行更多更改.

There are cleverer things you can do with parameterised queries - e.g. binding variables to output parameters, preparing a query once and executing it multiple times with different parameters - but those will require more changes to the way your calling code works.

这篇关于将mysql转换为pdo的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！