PHP PDO returning 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, Jul 25 '15 at 10:06

score 247 · Accepted Answer · edited Mar 09 '21 at 07:02

247

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();

edited Mar 09 '21 at 07:02

Your Common Sense

156,878
40
214
345

answered Mar 28 '11 at 08:56

mjspier

6,386
5
33
43

13

Most optimized if you use "LIMIT 1" in your prepare statement. – mjspier Mar 28 '11 at 09:06
2

superbly, please give complete example. Not just a single line that has to go somewhere in the example above. – andrebruton Jun 25 '14 at 06:28
3

@andrebruton `$dbh = new PDO(" --- connection string --- "); $stmt = $dbh->prepare("SELECT name FROM mytable WHERE id=4 LIMIT 1"); $stmt->execute(); $row = $stmt->fetch();` – low_rents Jun 25 '14 at 09:16
3

`fetch(PDO::FETCH_ASSOC)` will cut the returned array in half if you only need a key-value array. – rybo111 Aug 29 '15 at 16:31
1

Do you really need to `LIMIT 1` all the time though? Knowing that you're selecting a `UNIQUE` value comes to mind. – Super Cat Feb 01 '17 at 03:10
In MySQL LIMIT 1 prevents a full table scan. Use only when you are >100% sure that no duplicate values can exists! So only use with UNIQUE fields (the only proper way no duplicate values are present) Other values then 1 can be used together with offset. Usefull for pagination when showing parts of data. – Terradon Jan 07 '23 at 21:59

strauberry · Answer 2 · 2011-03-28T09:07:19.460

$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.

score 14 · Answer 3 · answered Feb 13 '15 at 11:14

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 '15 at 11:14

Bé Khỏe Bé Pro

313
1
3
9

4

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. – kael Nov 01 '16 at 17:40

user3162468 · Answer 4 · 2014-06-12T05:52:10.577

11

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 '14 at 05:52

answered Jun 12 '14 at 05:16

user3162468

425
6
14

3

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). – user3162468 Jun 12 '14 at 05:51

score 11 · Answer 5 · answered Mar 28 '11 at 09:50

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 '11 at 09:50

Stephen

18,597
4
32
33

KoolKabin · Answer 6 · 2011-03-28T09:02:28.830

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 '11 at 09:02

answered Mar 28 '11 at 08:55

KoolKabin

17,157
35
107
145

score 4 · Answer 7 · edited Dec 22 '19 at 07:24

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 '19 at 07:24

N'Bayramberdiyev

5,936
7
27
47

answered Sep 02 '13 at 04:36

jhloke

155
1
2
6

PHP PDO returning single row

7 Answers7

Linked

Related