PHP PDO how to fetch a single row?

Question

I have the following script which is good IMO for returning many rows from the database because of the "foreach" section.

How do I optimize this, if I know I will always only get 1 row from the database. If I know I will only ever get 1 row from the database, I don't see why I need the foreach loop, but I don't know how to change the code.

$STH = $DBH -> prepare( "select figure from table1" );
$STH -> execute();
$result = $STH -> fetchAll();
foreach( $result as $row ) {
    echo $row["figure"];
}

$STH - $DBH -> prepare should be $STH = $DBH -> prepare several places on this page. — Rune
– Rune, Commented Jul 25, 2015 at 10:06

Your Common Sense · Accepted Answer · 2024-03-31 11:27:01Z

255

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

$row = $stmt->fetch();

example

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

edited Mar 31, 2024 at 11:27

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

answered Mar 28, 2011 at 8:56

mjspier

6,5565 gold badges35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mjspier Over a year ago

Most optimized if you use "LIMIT 1" in your prepare statement.

andrebruton Over a year ago

superbly, please give complete example. Not just a single line that has to go somewhere in the example above.

low_rents Over a year ago

@andrebruton

$dbh = new PDO(" --- connection string --- "); $stmt = $dbh->prepare("SELECT name FROM mytable WHERE id=4 LIMIT 1"); $stmt->execute(); $row = $stmt->fetch();

rybo111 Over a year ago

fetch(PDO::FETCH_ASSOC) will cut the returned array in half if you only need a key-value array.

Super Cat Over a year ago

Do you really need to LIMIT 1 all the time though? Knowing that you're selecting a UNIQUE value comes to mind.

|

strauberry · Accepted Answer · 2011-03-28 09:07:19Z

$DBH = new PDO( "connection string goes here" );
$STH - $DBH -> prepare( "select figure from table1 ORDER BY x LIMIT 1" );

$STH -> execute();
$result = $STH -> fetch();
echo $result ["figure"];

$DBH = null;

You can use fetch and LIMIT together. LIMIT has the effect that the database returns only one entry so PHP has to handle very less data. With fetch you get the first (and only) result entry from the database reponse.

You can do more optimizing by setting the fetching type, see http://www.php.net/manual/de/pdostatement.fetch.php. If you access it only via column names you need to numbered array.

Be aware of the ORDER clause. Use ORDER or WHERE to get the needed row. Otherwise you will get the first row in the table alle the time.

Bé Khỏe Bé Pro · Accepted Answer · 2015-02-13 11:14:53Z

14

Did you try:

$DBH = new PDO( "connection string goes here" );
$row = $DBH->query( "select figure from table1" )->fetch();
echo $row["figure"];
$DBH = null;

answered Feb 13, 2015 at 11:14

Bé Khỏe Bé Pro

3131 gold badge3 silver badges9 bronze badges

1 Comment

kael Over a year ago

You could actually go further and do $row = $DBH->query($query)->fetch()['figure']. If fetch returns false because there are no results, PHP will silently ignore the invalid key reference. If something goes wrong with query, depending on how you've got your error handling set up, it will either throw an Exception (desired, in my opinion) or you'll get an Invalid object method reference "fetch" on "false".... error, a form of which you would have gotten anyway because the query obviously failed.

user3162468 · Accepted Answer · 2014-06-12 05:52:10Z

12

You could try this for a database SELECT query based on user input using PDO:

$param = $_GET['username'];

$query=$dbh->prepare("SELECT secret FROM users WHERE username=:param");
$query->bindParam(':param', $param);
$query->execute();

$result = $query -> fetch();

print_r($result);

edited Jun 12, 2014 at 5:52

answered Jun 12, 2014 at 5:16

user3162468

4356 silver badges14 bronze badges

1 Comment

user3162468 Over a year ago

I think you are just having a bad day bro, or ur just looking to standout your profile. My answer corroborates with the same scenario using a variable for the SELECT function with a PDO query. I will delete the sql injection comment from my answer just to make your day (and mine).

Stephen · Accepted Answer · 2011-03-28 09:50:16Z

11

If you want just a single field, you could use fetchColumn instead of fetch - http://www.php.net/manual/en/pdostatement.fetchcolumn.php

answered Mar 28, 2011 at 9:50

Stephen

19k4 gold badges35 silver badges33 bronze badges

Comments

KoolKabin · Accepted Answer · 2011-03-28 09:02:28Z

9

how about using limit 0,1 for mysql optimisation

and about your code:

$DBH = new PDO( "connection string goes here" );

$STH - $DBH -> prepare( "select figure from table1" );

$STH -> execute();

$result = $STH ->fetch(PDO::FETCH_ASSOC)

echo $result["figure"];

$DBH = null;

edited Mar 28, 2011 at 9:02

answered Mar 28, 2011 at 8:55

KoolKabin

17.8k37 gold badges112 silver badges145 bronze badges

Comments

N'Bayramberdiyev · Accepted Answer · 2019-12-22 07:24:01Z

4

Thanks to Steven's suggestion to use fetchColumn, here's my recommendation to cut short one line from your code.

$DBH = new PDO( "connection string goes here" );
$STH = $DBH->query( "select figure from table1" );
$result = $STH->fetchColumn();
echo $result;
$DBH = null;

edited Dec 22, 2019 at 7:24

N'Bayramberdiyev

3,6509 gold badges32 silver badges47 bronze badges

answered Sep 2, 2013 at 4:36

jhloke

1551 gold badge2 silver badges6 bronze badges

Collectives™ on Stack Overflow

PHP PDO how to fetch a single row?

7 Answers 7

6 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

6 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Linked

Related