PHP PDO query function

Question

EDIT:

I'm marking the answer below correct because of our conversation below. Feel free to post an answer to the unanswered parts and I'll be sure to check back here. Thanks.

=-=-=-=-=-=-=-=-

I'm trying to have one function handle all prepared statements. In my older code, I was mysql_fetch_assoc-ing through select queries. I'd like to just return an array if I'm doing a select and maybe lastInsertId or affectedRows or something if I'm doing an Insert Update or Delete.

My question is, aside from parsing the sql for 'select' or 'insert' or adding another parameter to the function like $type = 'Select' is there something in the PDO class that lets you know if there's data?

I saw somewhere that you can test if the value returned by fetch or fetchAll is true. Is that what I'm supposed to do?

I'm open to any feedback on the function, too.

function pdo_query($sql,$data)
{
$stmt = $dbh->prepare($sql);
$first_row = true;

if(is_array($data)){
    foreach($data as $row){
        if(is_array($row)){
            foreach($row as $param => $value){
                if($first_row){
                    //http://bugs.php.net/43130 parameters can't have dashes
                    $stmt->bindParam($param, ${substr(str_replace('-','_',$param),1)});
                }
                ${substr(str_replace('-','_',$param),1)} = $value;
            }
            $stmt->execute();
            $first_row = false;
        }
    }
    while ($row = $stmt->fetch()) {
        $return[] = $row;
    }
}
return $return;
}

Edit: I haven't tried this yet, but is there any problem testing for $stmnt->fetch()? Also, if I did want to automate getting lastInsertId() or affected rows, it doesn't seem like I can figure out what type of query I'm doing after the fact. Here's where I'm at:

if ($rows = $stmt->fetchAll()) {
        return $rows;
    } else if (some_way_of_determining_if_an_insert_happened) {
        return $dbh->lastInsertId();
    } else {
        return some_way_of_returning_affected_rows
    }
}

why bother with manual binding? why not just execute($data)? — Your Common Sense, Jan 06 '11 at 18:13
Hmmmm... I thought I knew. I'll have to look at what binding does. Thanks for your input. — Stephane, Jan 06 '11 at 18:27
I had a similar approach once: http://stackoverflow.com/questions/3725879/fetchall-helper-function-using-pdo however it takes parameters inline, not in array. like `$db->fetchAll("SELECT * FROM table WHERE a = ? AND b = ?",$a,$b)` dunno which is better. may be it should test a parameter if it's of array type and add it's items to the resulting array of values — Your Common Sense, Jan 06 '11 at 18:30
I really don't like positional placeholders. They are faster to type, but they seem so error-prone I can't justify using them. Since I'm only going to write this part of the code once, I don't think it's that big of a deal to iterate through. Although, I can see that maybe I'm going to hate creating and passing an array every time I want to interact with the database. — Stephane, Jan 06 '11 at 19:43
I think you are overthinked this. Why not to just call separate methods to get these values? More magic you put in the method, more difficult it would be handled in the future. — Your Common Sense, Jan 06 '11 at 20:18
Do people really create a method for each type? I just assumed people passed their queries to a single function. I don't mind making functions pdo_insert pdo_update pdo_select pdo_delete, but it seems like there should be a better way. — Stephane, Jan 06 '11 at 20:38
Well, I think people do. It costs you nothing but helps a lot. I see nothing bad in this way and see no reason to look for better one. However, I am strictly against pdo_select. — Your Common Sense, Jan 06 '11 at 20:43
you know, I changed my mind. I am against them all. Just because it's hard to encapsulate even delete query into some magic method, not to mention complex select queries. So, I am back to mysql_query() equivalent that returns a resource which can be used by other methods — Your Common Sense, Jan 06 '11 at 20:53
Ah, I see. You're thinking something like: $result = $dbh->method; $item = $class->process($result); I'll think about that. — Stephane, Jan 06 '11 at 21:17
not process() but insert_id(). for the item it's still `$item = $dbh->method("query");` — Your Common Sense, Jan 06 '11 at 21:26

score 2 · Accepted Answer · edited May 23 '17 at 11:55

Don't feel too smart.

Make a set of methods.

A general of them - called query, that returns a resource type.
A set of helpers, which by using this query method returns a scalar value, a row, a column and a set of rows.
Utility methods, to get number of affected rows, returned rows, insert id and such from given resource.

Also a method to produce SET statement would be useful for use with insert and update methods. See this one for the example

Examples:

//SELECT helpers:
$username = $db->getOne("SELECT name FROM users WHERE id=?",$id); //getting scalar
$userinfo = $db->getRow("SELECT * FROM users WHERE id=?",$id); //getting row
$newsdata = $db->getAll("SELECT * FROM news LIMIT ?,?",$start,$rows); //getting array

//Select for mass emailing
$res = $db->query("SELECT * FROM notify WHERE topic_id = ?",$topic);
$num = $db->numRows($res);
while ($row = $db->next()){
  mail(...);
}

//insert
$res = $db->query("INSERT INTO users SET name = ?",$name);
$id  = $db->lastId();

//delete
$res  = $db->query("DELETE FROM users WHERE id = ?",$id);
$rows = $db->affectedRows();

However, I am not sure about affectedRows() and lastId methods, as them takes link identifier as a parameter, not resource identifier....

I understand your point and you've changed how I view the creation of functions and methods. Thank you. — Stephane, Jan 08 '11 at 05:41

PHP PDO query function

1 Answers1

Linked