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
– Your Common Sense, Commented Jan 6, 2011 at 18:13
Hmmmm... I thought I knew. I'll have to look at what binding does. Thanks for your input. — Stephane
– Stephane, Commented Jan 6, 2011 at 18:27
I had a similar approach once: stackoverflow.com/questions/3725879/… 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
– Your Common Sense, Commented Jan 6, 2011 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
– Stephane, Commented Jan 6, 2011 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
– Your Common Sense, Commented Jan 6, 2011 at 20:18

Community · Accepted Answer · 2017-05-23 11:55:28Z

2

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

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Jan 6, 2011 at 20:24

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephane Over a year ago

I understand your point and you've changed how I view the creation of functions and methods. Thank you.

Collectives™ on Stack Overflow

PHP PDO query function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related