PHP Pdo Returning Single Row

PHP PDO returning single row

Just fetch. only gets one row. So no foreach loop needed :D

$row  = $STH -> fetch();

example (ty northkildonan):

$id = 4;
$stmt = $dbh->prepare("SELECT name FROM mytable WHERE id=? LIMIT 1"); 
$stmt->execute([$id]); 
$row = $stmt->fetch();

Why does PDO fetch() return only the first row?

To select all rows using PDO, you need to use fetchAll() instead of fetch().

$e = $db->query("SELECT `username`, `membership` FROM `users` WHERE `expire` != ''")->fetchAll();

In PDO, fetch() returns "the next row from a result set".

fetchAll() returns "an array containing all of the result set rows".

fetch single record using PHP PDO and return result

Let me suggest you to change this approach a bit, to make it A LOT more flexible.

• First, you definitely have to make this function accept an array with data for execute(). Otherwise there will be no sense in using prepare or PDO at all.
• Then, make your function return the statement. It will make it enormously flexible

So, change the code to this

function getdata($dbh, $sql, $params = NULL)
{
    $stmt = $dbh->prepare($sql);
    $stmt->execute($params)
    return $stmt; 
}

this way you'll be able to fetch either single record.

$row = getdata($dbh, $sql)->fetch();

or multiple rows

$row = getdata($dbh, $sql)->fetchAll();

or even run insert or update queries from which you cannot fetch at all.

Fetching single row, single column with PDO

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.

My PHP PDO fetchAll() code returns one row instead of all results on MySql column table

The fetchAll() is fine. If you var_dump($result) you'll see all the rows are there.

This is the problem:

<?php foreach( $result as $row ) ?>
<option value="<?php echo $row['category_id'];?>"><?php echo $row['category'];?>

Without using curly braces or alternate syntax to group the statements after foreach, only the first statement after the foreach will be repeated. In this case, the first statement after the foreach is nothing. The closing PHP tag implies an instruction separator, so you're effectively saying:

foreach( $result as $row );

i.e. for each row, do nothing. $row is still defined after the loop as the last row from the array, which is the one option you end up with.

Here's an example of alternate syntax to enclose the two statements:

<?php foreach( $result as $row ): ?>
<option value="<?php echo $row['category_id'];?>"><?php echo $row['category'];?></option>
<?php endforeach; ?>

PDO fetch returns only first row

Add a while loop,

while($row= $sth->fetch( PDO::FETCH_ASSOC )){ 
   echo $row['your_field_name'];
}

Or you can use fetchAll

$rows = $sth->fetchAll();
print_r($rows);

MySQL WHERE IN () + AND , PDO returns only one row

it is not PDO's fetchAll() of course, but your query.

Which is not

IN (1007,1011,1012,1013,1014)

but

IN ('1007,1011,1012,1013,1014')

and of course it will find only first value as this string will be cast to the first number

One have to create a query with placeholders representing every array member, and then bind this array values for execution:

$ids = array(1,2,3);
$stm = $pdo->prepare("SELECT * FROM t WHERE id IN (?,?,?)");
$stm->execute($ids);

To make this query more flexible, it's better to create a string with ?s dynamically:

$ids = array(1,2,3);
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($ids);
$data = $stm->fetchAll();

---

Related Topics