Select data from database with PDO

Question

I have some data in a mysql database.The database every time consists of a different number of rows.

An example of my table would be :

id  team1  team2  home  away  tip
----------------------------------
12   xxx    yyy   zzz   kkk
43   xxx    yyy   zzz   kkk
.     .      .     .     .
.     .      .     .     .

So what i need to do is loop through every row of the table and be able to get the data from that row so i can create the tip.

I am a bit confused on how i could do that with pdo.

What i ve tried so far is try to get a column of data like this:

$stmt = $db->prepare("SELECT id FROM tablename");
    $stmt->execute();
    $result=$stmt->fetchColumn();
    echo $result[0];

But even that , that i would expect to give me back the first id , which is 12 in this case, just returns me 1. Any ideas here?

echo $result[0]; returns the first character of the scalar $result, as you are using ->fetchColumn to only fetch 1 column, not the whole row. So, either echo $result, or use $result=$stmt->fetch();. Currently, what's happening can be compared to this: $result="12"; echo $result[0]; (which, as you can test, echoes '1' indeed. — Wrikken
– Wrikken, Commented Feb 18, 2014 at 14:49
$result = $stmt->fetch(PDO::FETCH_ASSOC); if($result){echo $result['id'];} should work. $result is going to either be boolean FALSE or an array so a simple check will avoid undesireable errors — MonkeyZeus
– MonkeyZeus, Commented Feb 18, 2014 at 14:50
@wrikken, when i use $result=$stmt->fetch(); does indeed work. but when i try to echo $result[1] gives me an error. How can i get all the column in an array so i can loop through the results? — user3287726
– user3287726, Commented Feb 18, 2014 at 14:54
If you SELECT id FROM tablename, you only have one column, so, $result[1] will not be set indeed. Alter your query to return the other columns, and they'll appear in $result. — Wrikken
– Wrikken, Commented Feb 18, 2014 at 14:58

user849001 · Accepted Answer · 2014-02-18 14:58:07Z

2

$stmt = $db->prepare("SELECT id FROM tablename");
try{
    $stmt->execute();
}catch(PDOException $err){
    //some logging function
}
//loop through each row
while($result=$stmt->fetch(PDO::FETCH_ASSOC)){
    //select column by key and use
    echo $result['id'];
}

alternatively you could use fetchAll to select whole dataset into a variable. Then you wouldn't need to loop

Heres a useful resource for PDO:

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

answered Feb 18, 2014 at 14:58

user849001

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

1 Comment

Humphrey Over a year ago

This is relevant to me

Ferrakkem Bhuiyan · Accepted Answer · 2014-02-18 14:52:16Z

0

$getUsers = $DBH->prepare("SELECT * FROM tablename ORDER BY id ASC");
$getUsers->execute();
$users = $getUsers->fetchAll();

May be helps you

answered Feb 18, 2014 at 14:52

Ferrakkem Bhuiyan

2,7932 gold badges25 silver badges39 bronze badges

Comments

iubema · Accepted Answer · 2014-02-18 14:55:00Z

0

Try to print the result without index

$stmt = $db->prepare("SELECT id FROM tablename ORDER BY id ASC");
$stmt->execute();
$result = $stmt->fetchColumn();
echo $result;

answered Feb 18, 2014 at 14:55

iubema

3492 silver badges12 bronze badges

Comments

Marc Kline · Accepted Answer · 2014-02-18 15:00:08Z

0

Use fetch (with FETCH_NUM) instead of fetchColumn

$stmt = $db->prepare("SELECT id FROM tablename");
    $stmt->execute();
    $result=$stmt->fetch(PDO::FETCH_NUM);
    echo $result[0];

answered Feb 18, 2014 at 15:00

Marc Kline

9,4091 gold badge35 silver badges36 bronze badges

Comments

Humphrey · Accepted Answer · 2014-02-18 15:01:30Z

0

if you want to loop throught you can use the while loop and below is the example

<?php 

$stmt =("SELECT id FROM tablename");
$result=$db->prepare($stmt );
$result->execute();
while($row = $result->fetch(PDO::FETCH_ASSOC))
{
$id = $row['id'];
 echo $id;
}
?>

answered Feb 18, 2014 at 15:01

Humphrey

2,8273 gold badges30 silver badges39 bronze badges

Collectives™ on Stack Overflow

Select data from database with PDO

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related