convert mysql to pdo

Question

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;  
}

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  }  
}

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.

In your case, you don't have anything to bind as your query has no parameters. — Brad, Sep 14 '13 at 16:26

score 0 · Accepted Answer · edited May 23 '17 at 11:51

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.

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']));

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.

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.

using your code above,if i were using an insert statement, is there a way i can perform a mass action, like inserting multiple values at the sane time with the best performance? — , Sep 17 '13 at 02:38
@user1850720 No, I think what you're describing there would need you to store or return the prepared statement (`$sth`) so that you could call `execute` on it multiple times with different parameters. — IMSoP, Sep 17 '13 at 09:14
@user1850720 ...or I suppose you could pass an array of arrays to the function, and loop over calling `execute` with each inner array. — IMSoP, Sep 17 '13 at 11:55

convert mysql to pdo

1 Answers1

Linked