PHP-PDO fetch data using loop structure

Question

I am practicing using PDO fetch methods to retrieve data from the table. I would like to a counter in a while loop to retrieve data one row at a time. Please give me some advise how to accomplish this. Thanks!

Here are my 2 code samples using PDO::Query() and PDO::fetch() method. code sample 1 using PDO::Query() Method

$sql = 'select first_name, last_name, pd, b_month, b_day, b_year from reg_data';
$birth_date = '';
try
{
    foreach($con->query($sql) as $row)
    {
       print $row['first_name'] . "  ";
       print $row['last_name']. "  ";
       print $row['pd'] . "  ";
       $birth_date =  $row['b_month'] . "-". $row['b_day'] . "-". $row['b_year'];
       print "$birth_date";
    } 

}
catch(PDOException $e)
{
    echo " There is a problem with you db connection";
    echo $e->getMessage();
}

sample 2 using PDO::fetch() method

try {
    $con = new PDO ($dns, $db_uid, $db_pd, $option);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "select * from reg_data";
    $stmt = $con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();

    //using cursor to interate through array
    while($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT))
    {
        $data = $row[0].$row[1]. $row[2] .$row[3].$row[4].$row[5];
        print $data;
    }
   $stmt = null; //close the handle
}
catch(PDOException $e)
{
    echo " There is a problem with you db connection";
    print $get->getMessage();
}

I think this is the question.. but I can't make heads or tails of it: "I would like to a counter in a while loop to retrieve data one row at a time" — Mike B
– Mike B, Commented Mar 5, 2013 at 13:31

Community · Accepted Answer · 2017-05-23 11:50:38Z

2

I think you need PDO::fetchAll.
A code example straight from the tag wiki:

//connect
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);

//retrieval
$stm = $pdo->prepare("select * from reg_data");
$stm->execute();
$data = $stm->fetchAll();
$cnt  = count($data); //in case you need to count all rows
//output
?>
<table>
<? foreach ($data as $i => $row): ?>
  <tr>
    <td><?=$i+1?></td> <!-- in case you need a counter for each row -->
    <td><?=htmlspecialchars($row['first_name'])?></td>
  </tr>
<? endforeach ?>
</table>

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Mar 5, 2013 at 13:36

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

Comments

acutesoftware · Accepted Answer · 2013-03-05 13:37:07Z

0

You can use a variable and increment it in the loop

$counter++ ;

answered Mar 5, 2013 at 13:37

acutesoftware

1,0913 gold badges15 silver badges33 bronze badges

1 Comment

N.B. Over a year ago

Or just fetch everything into array and count the array, which is 2 lines of code and requires no loops.

Collectives™ on Stack Overflow

PHP-PDO fetch data using loop structure

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related