CODEWITHSUNDEEP
phpmysqldatabasepdo
Chris
Chris

Reputation: 8826

Fetching single row, single column with PDO

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.

Upvotes: 28

Views: 45838

Answers (6)

reko_t
reko_t

Reputation: 56430

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.

Upvotes: 41

Cold Pen
Cold Pen

Reputation: 21

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;

Upvotes: 1

Matthew
Matthew

Reputation: 131

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.

Upvotes: 1

1nstinct
1nstinct

Reputation: 1775

You could use this:

$stmt->fetch(PDO::FETCH_COLUMN, $number_of_column);

Upvotes: 1

JAL
JAL

Reputation: 21563

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

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

$sth->execute($data);

$result=$sth->fetchColumn();

Upvotes: 13

True Soft
True Soft

Reputation: 8786

Have you prepared the statement first? (Before $stmt->execute())

$stmt = $db->prepare("SELECT some_col_name FROM table_name WHERE user=:user");

Upvotes: 0

Related Questions