Querying with PDOStatement

[arenaninja]

Hey all. I'm using this sort of thing to query with my application:

        try
        {
            $stmt = $this->db->prepare($sql);
            /* Bind parameter if id was passed, ensure it's of integer type */
            if(!empty($id))
            {
                $stmt->bindParam(":id", $id, PDO::PARAM_INT);
            }
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $stmt->closeCursor();

            return $results;           
        }
        catch(Exception $e)
        {
            die($e->getMessage());
        }

which has been working out great, with the only annoyance that sometimes, I need to run a query and I know I'm supposed to get only one row. However, I have to access associated elements via $results[0]['fieldName']. How do I run a query to return exactly one result so I can just use $results['fieldName']?

[kicken]

Use ->fetch(), not ->fetchAll().

 

->fetch() returns a single row at a time (call it multiple times to get multiple rows).

->fetchAll() returns all rows in a big array at once.

[marcbraulio]

Or try the object oriented approach:

 

try
{
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetch(PDO::FETCH_OBJ);
$stmt->closeCursor(); 
}
catch(Exception $e)
{
die($e->getMessage());
}

You can easily access it like so:

 

echo $results->field_name;

[arenaninja]

oooo much appreciated. Any upside to using the object oriented approach? So far I like having to differ between an array and an object in my application, it reminds me I'm still dealing with MySQL. It seems like less of a pain to keep track of quotation marks, but honestly I think I'll stick to fetch() for now. Many thanks to you both!

[marcbraulio]

Well object oriented programming is where the future is headed, so mines well start now =]. But yeah there are some upsides to it, besides it being easier to type, you can do some cool things with it like load data directly into a class for direct manipulation. If you ever get curious, take a look here: http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/