How to fetch and display an array using PDO?

Question

I understand there are MANY ways to do all of this, but trying to do it the best way.

I have created the db parameters, dns, dbh, sth, sql and generally quite happy with the result up to ... well ... the result part.

<?php

// db parameters
$dbhost = "localhost";
$dbname = "x";
$dbuser = "y";
$dbpass = "z";

// driver invocation (dsn is short for data source name)
$dsn = "mysql:host=$dbhost;dbname=$dbname";

// create db object (dbh is short for database handle)
$dbh = new PDO($dsn, $dbuser, $dbpass);

// execution of database query (sth is short for statement handle)
$sql = "SELECT * FROM a_aif_remaining";
$sth = $dbh->prepare($sql);
$sth->execute();

NOT SURE WHAT TO PUT BELOW.... (A) or (B)

I just want to present a simple array of the data. One row from the table per line.

Option A

echo $_POST['fieldname1'];
echo $_POST['fieldname2'];
echo $_POST['fieldname3'];

Option B

while ($rows = $sth->fetch(PDO::FETCH_ASSOC)) {
    echo $row[fieldname1],'<br>';
    }

AND I AM CONFIDENT WITH THE ENDING

    $dbh = NULL;

?>

Any advise would be GREATLY appreciated.

UPDATED CODE: (Produces nothing on the page)

<?php

    // db parameters
    $dbhost = "localhost";
    $dbname = "theaudit_db1";
    $dbuser = "theaudit_user";
    $dbpass = "audit1999";

    $dsn = "mysql:host=$dbhost;dbname=$dbname"; // driver invocation (dsn is short for Data Source Name)

try {
    $dbh = new PDO($dsn, $dbuser, $dbpass); // connect to new db object (dbh is short for Database Handle)
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the PDO error mode to enable exceptions
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // set the PDO emulate prepares to false

    // execute query to database (sth is short for Statement Handle)
    $sql = "SELECT * FROM a_aif_remaining";
    $sth = $dbh->prepare($sql);
    $sth->execute();

    $data = $sth->fetchAll(PDO::FETCH_ASSOC);

    $dbh = NULL;
}

catch(PDOException $e)
    {
    echo $e->getMessage();
}
?>

This code doesn't seem to intend to produce anything on the page. The word "array" has a special meaning in computer languages, it's a variable of complex type, contains other variables - so I took it. If you meant "array of strings on my page" - either use your B or foreach over $data variable from my code. — Your Common Sense
– Your Common Sense, Commented Dec 28, 2012 at 6:39
I mean like a CSV file basically... One sql result to show up on one row in the browser. — Ben
– Ben, Commented Dec 28, 2012 at 6:41

Your Common Sense · Accepted Answer · 2012-12-28 06:14:08Z

1

Though I can't get what's the connection between A anb B, I can answer the

I just want to present a simple array of the data. One row from the table per line.

question.

$sql = "SELECT * FROM a_aif_remaining";
$sth = $dbh->prepare($sql);
$sth->execute();
$data = $sth->fetchAll(PDO::FETCH_ASSOC);

where $data is a sought-for array.

answered Dec 28, 2012 at 6:14

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

2 Comments

Ben Over a year ago

I have tried what you suggest and it is not working. I've even wrapped it in an exception clause, but still not working.

Ben Over a year ago

the connection between A and B is that both are an associative array, no?

Mike Miller · Accepted Answer · 2014-08-19 09:18:57Z

The problem with your updated code is simple - you arent echo'ing your data out. You need to add something like..

   foreach($data as $arKey=>$dataRow){
        foreach($dataRow as $arKey=>$rowField){
             echo $rowField.','; //concat with a ',' to give csv like output 
        }
        echo '<br>'; //to get to next line for each row (may want to trim the last ','
   }

I am also confused by the reference to $_POST. It is true both are associate arrays but that does not mean that the $_POST option is viable - the data would only be available in the $_POST if you put it there (eg $_POST = $data) which would be pointless. Or if you had posted the data from somewhere else. Neither seem to fit what you are asking so I would forget about the $_POST and just figure out how you access your multi dimensional $data array. There is endless tut's on this subject. Try using

    var_dump($data)

to see whats inside that should help you visualise what is going on.

NOTE: in option B you are not correctly concatenating or referencing your array it should be:

    while ($rows = $sth->fetch(PDO::FETCH_ASSOC)) {
        echo $rows[fieldname1].'<br>'; //$row doesnt exist its $rows and you use . to concat not ,
        }

Ah yes and probably better to use unset rather than setting $dbh to equal null

unset($dbh);

Collectives™ on Stack Overflow

How to fetch and display an array using PDO?

UPDATED CODE: (Produces nothing on the page)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATED CODE: (Produces nothing on the page)

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related