Getting database results from Array of IDs

Question

Apologies for the newbishness of this question. I'm just starting a project and I have an array of IDs to grab certain records from a table. What's the best way to go about this?

Will the following work?

public function getWork($slideIDs) {
    $params = array();
    $params = $slideIDs;

    $count = count($params);

    for($i=0;$i<$count;$i++) {
        $STH = $this->_db->prepare("SELECT * 
            FROM slides
            WHERE slideID = :slideID;");

        $STH->execute($params[$i]);
    }

    $result = $STH->fetchAll(PDO::FETCH_ASSOC);

    return $result;       
}

Thanks.

Answer 1

Better to execute just one SQL statement using an IN clause

SELECT * FROM slides WHERE slideID IN (1,2,3,4,5, ...) 

Since fewer connections and trips to the database is generally better for performance.

So just build that SQL string in your loop, then move the Execute to the bottom (outside your loop.)

Answer 2

Adjust your PHP to create a single query string with this format:

SELECT * 
FROM slides
WHERE slideID IN (2,5,11)

Do not execute multiple queries. :)

Answer 3

Same as others ;)

Use implode($array, ",") to get the part which should go into the IN() statement

Answer 4

The following should work. Used implode to create the required string containing the IDs separated by comma to pass to SQL.

    function getWork($slideIDs) {
    $params = implode(",",$slideIDs);
    $STH = $this->_db->prepare("SELECT * 
            FROM slides
            WHERE slideID in ( :slideID );");

    $STH->execute($params);

    $result = $STH->fetchAll(PDO::FETCH_ASSOC);

    return $result;   
    }