Fetching single row, single column with PDO

Question

I have a mysql query that targets a single column in a single row

"SELECT some_col_name FROM table_name WHERE user=:user"

After I execute the statement $stmt->execute(); how do I get this single cell directly placed into a variable with no loops? In other words how to get

from $stmt->execute();

to $col_value = 100;

I tried the 2 below, but neither worked.. The column is number 4 in the original table, but I'm assuming since in my select statement I'm selecting it only, it should be 1 when I specify the parameter for fetchColumn.

$col_value = $stmt->fetchColumn();
$col_value = $stmt->fetchColumn(0);

As you can see, I'm trying to do it in as few lines as possible.

Solution 1

Are you sure it's returning any rows?

$stmt->fetchColumn()

is correct way to fetch a single value, so either you probably didn't bind the :user parameter or it simply returned no rows.

Solution 2

$sql='SELECT some_col_name FROM table_name WHERE user=?';

$sth=$pdo_dbh->prepare($sql);
$data=array($user);

$sth->execute($data);

$result=$sth->fetchColumn();

Solution 3

I'm not sure why so many people mess this up:

$stmt = $dbh->prepare("SELECT `column` FROM `table` WHERE `where`=:where"); 
$stmt->bindValue(':where', $MyWhere);
$stmt->execute();
$SingleVar = $stmt->fetchColumn();

Make sure that you are selecting a specific column in the query and not * or you will need to specify the column order number in fetchColumn(), example: $stmt->fetchColumn(2); That usually isn't a good idea because the columns in the database may be reorganized by, someone...

This will only work properly with unique 'wheres'; fetchColumn() will not return an array.

Solution 4

When you want to get the last insert you add the DESC Limit 1 to the sql statement.

$sql = "SELECT `some_col_name` FROM table_name\n"

    . "ORDER BY `some_col_name` DESC\n"

    . "LIMIT 1";

$stmt = $conn->prepare($sql);
$result = $stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
//convert the array content to string and store in variable 
$col = implode(" ", $row);
echo $col;

View more solutions