PDO: Using variable to SELECT specific fields

Question

I currently have the below construct to create a Member object:

class Member{   
private $member_id = 0;
private $name, $surname, $email;

public function __construct(){} 
    
public function construct($conn, $param_select = '*', $param_member_id){
    $query_rs_this = $conn->prepare('SELECT :select FROM members WHERE 
                                            member_id = :member_id
                                        ORDER BY members.surname');
                                        
    $query_rs_this->execute(array(':select' => $param_select, ':member_id' => $param_member_id));
    $rs_this = $query_rs_this->fetch(PDO::FETCH_ASSOC);
    $query_rs_this->closeCursor();
    
    foreach($rs_this as $key => $value){
        $this->$key = $value;
    }
    unset($key);
}

I would like to pass a string containing the columns (field names) which should be selected from the database. Currently I am using this:

$temp = new Member();
$select = "name, surname, email";
$temp->construct($db_conn, $select, 1);

Where $select is the string containing the fields names.

As it stands, I get the following output:

member_id: 0

name:

surname:

email:

?: name, surname, email

This is supposed to be:

member_id: 1

name: John

surname: Doe

email: [email protected]

Thank you in advance for any help.

For one thing, you can't bind tables SELECT :select <= that's a no-no as is execute(array(':select' => $param_select,. Either select a table or set a variable for it; that is allowed. — Funk Forty Niner
– Funk Forty Niner, Commented Jul 2, 2014 at 16:16
Add $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); right after the connection is opened and you will be alerted of errors. — Funk Forty Niner
– Funk Forty Niner, Commented Jul 2, 2014 at 16:17
I see... so I need to manually check and compile a list of columns to pull from my DB? — Narayan
– Narayan, Commented Jul 2, 2014 at 16:22

dave · Accepted Answer · 2014-07-02 17:21:02Z

1

To accomplish essentially the same thing, you could have a whitelist of fields to select, then build the string of fields yourself.

public function construct($conn, 
                          $param_select = array('name', 'surname', 'email'),
                          $param_member_id) 
{
    $available_fields = array('name', 'surname', 'email');
    $select_fields_array = array_intersect($param_select, $available_fields);
    $select_fields = implode(", ", $select_fields_array);
    $query_rs_this = $conn->prepare("SELECT $select_fields FROM members WHERE 
                                        member_id = :member_id
                                    ORDER BY members.surname");

    $query_rs_this->execute(array(':member_id' => $param_member_id));
    $rs_this = $query_rs_this->fetch(PDO::FETCH_ASSOC);
    $query_rs_this->closeCursor();

    foreach($rs_this as $key => $value){
        $this->$key = $value;
    }
    unset($key);
}

OR

public function construct($conn, 
                          $param_select = array('name', 'surname', 'email'),
                          $param_member_id) 
{

    $query_rs_this = $conn->prepare("SELECT * FROM members WHERE 
                                        member_id = :member_id
                                    ORDER BY members.surname");

    $query_rs_this->execute(array(':member_id' => $param_member_id));
    $rs_this = $query_rs_this->fetch(PDO::FETCH_ASSOC);
    $query_rs_this->closeCursor();

    foreach($rs_this as $item){
        foreach($param_select as $field) {
             $this->$field = $item[$field];
        }
    }
    unset($key);
}

edited Jul 2, 2014 at 17:21

answered Jul 2, 2014 at 16:26

dave

65.1k6 gold badges81 silver badges98 bronze badges

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

3 Comments

Narayan Over a year ago

Now there's an idea! Excellent. However, when I'm reading in several hundred rows, with lots of columns, isn't that going to cause unecessary memory usage (and therefor time delay)?

dave Over a year ago

Possible - just modified it to a safe way of building the fields to select yourself. A little more annoying as if you add a field you'll have to change the code to allow it, but performance-wise should be faster

Narayan Over a year ago

That, my dear Sir, is perfect. Hard-coding the available fields is fine by me. Thank you.

Collectives™ on Stack Overflow

PDO: Using variable to SELECT specific fields

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related