Can't select data with PDO

Question

I want to select data (at all) with PDO (always used mysqli) from an external database. It connects, and the query works on the server directly with mysql. With php, it doesn't. Here's my code:

<?php
$hostname = 'localhost';
$username = 'user';
$password = 'pass';

function testdb_connect ($hostname, $username, $password){
    $dbh = new PDO("mysql:host=$hostname;dbname=database", $username, $password);
    return $dbh;
}

try {
    $dbh = testdb_connect ($hostname, $username, $password);
    echo 'Connected to database';
} catch(PDOException $e) {
    echo $e->getMessage();
}

$sql= "select * from table limit 10;"; 
echo "<br/>";
echo $sql;
$stmt = $pdo->prepare($sql);
$stmt->execute();
$row = $stmt->fetchObject();
echo $row->id;

It shows "connected to database", and the "echo $sql" part, but doesn't display any information.

change `$pdo->` to `$dbh->`. Because you connection variable is `$dbh->` — Saty, Apr 07 '17 at 09:11
first `$stmt = $dbh->prepare($sql);`then check `echo "
";print_r($row);` — Alive to die - Anant, Apr 07 '17 at 09:11
**Fatal error: Uncaught Error: Call to a member function prepare() on null in** — Masivuye Cokile, Apr 07 '17 at 09:14
It works now! @ChetanAmeta and Saty fixed it, thank you very much! I now want to print the 10 rows instead of just the first one. How do I do it? — Newwt, Apr 07 '17 at 09:16
*I now want to print the 10 rows instead of just the first one. How do I do it?* You loop through the results then print them — Masivuye Cokile, Apr 07 '17 at 09:17
Possible duplicate of [Fatal error Call to a member function prepare() on null](http://stackoverflow.com/questions/30627027/fatal-error-call-to-a-member-function-prepare-on-null) — Masivuye Cokile, Apr 07 '17 at 09:17
1.`$stmt = $dbh->prepare($sql);`2. `while ($row = $stmt->fetchObject()) { echo $row->id; }` — Alive to die - Anant, Apr 07 '17 at 09:27
@AlivetoDie It works, but the ; after fetchObject() has to be removed! — Newwt, Apr 07 '17 at 09:31

Masivuye Cokile · Accepted Answer · 2017-04-07T09:52:03.177

Your first part of the question have been solved.

now this

I now want to print the 10 rows instead of just the first one. How do I do it?

The are many ways you can do that, but you need to loop through your results and display the desired Rows

Option 1

 $sql = $dbh->query("SELECT * from table limit 10")->fetchall(PDO::FETCH_ASSOC);

     foreach($sql as $row){

        // print_r($row); // see them all

        echo $row['desiredRow']; //print them one by one
     }

Option 2

 $sql = $dbh->query("SELECT * from table limit 10");

     while($row=$sql->fetch()){

        // print_r($row);
        echo $row['desiredRow'];
     }

Option 3

<?php

    $sql = "SELECT * from table limit 10";

    $stmt = $dbh->prepare($sql);
    $results = $stmt->fetchall(PDO::FETCH_ASSOC);

    if(count($results) > 0){//check results

        foreach($results as $row){

            print_r($row);
        }
    }else{

        echo "no results found";
    }
?>

Can't select data with PDO

1 Answers1