Writing PDO prepared statements in Modx to import CSV files

Posted on June 12th, 2011 by thiswayup.
Categories: Development.

Here's a small note for myself (and anyone who just wants to knock up a quick query using XPdo and Modx Revolution). Xpdo is the ORB (Object-relational bridge) or ORM that Modx Revolution has adopted as its standard way to abstract all the data access. It also provides a thin wrapper around the standard php PDO class.

I myself like writing SQL, especially when you have to write slightly more complicated sql which an ORM just becomes a hindrance in certain situations. Recently I've had to deal with uploads of CSV files directly into Modx, here is my code for the csv file read and using the handy PDO prepared statements bit.

Some points to note when using PDO :

• Outputting the actual query used is not possible by normal methods (ie you cant dump the string value) , you have to log the queries used on mysql. This is because the actual query is prepared in mysql with values being passed to mysql after.
• I recommend using the colon place holders eg ":dataValue" as this aids in readability.
• You do not need to escape the query as prepared statements doe this for you.
• Remember in the bindValue call to add the third and possible forth parameter. This defines the data_type and length of the datatype, otherwise mysql will not know how to type the value during the query being run. I always forget this after an extended period of not writing prepared statements and its a pain in the ass to debug, ref : "what the hell is wrong with this insert statement !?!?!?"

Here is some sample code for :

$file ="var/someplace/file.txt";
$handle = fopen($file, "r");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $stmt = $modx->prepare(
                "INSERT INTO modx_pews_buffer (event_group, month,event_date,event_descrption,event_start_time)
                VALUES (:grp, :month,:date,:des,:time)");
        $stmt->bindParam(':grp', $data[0],PDO::PARAM_STR,200 );
        $stmt->bindParam(':month', $data[1],PDO::PARAM_STR,50);
        $stmt->bindParam(':date', $data[2],PDO::PARAM_STR,200);
        $stmt->bindParam(':des', $data[3],PDO::PARAM_STR,30);
        $stmt->bindParam(':time', $data[4],PDO::PARAM_STR,11);
        $stmt->execute();
    }

Also read up :

• Php manual page on PDO
• Modx Revolution manual on Xpdo
  

Was listening to this whilst writing this post.

0 comments.