Asked  8 Months ago    Answers:  5   Viewed   35 times

I'm trying to retrieve data from my table using PDO, only I can't seem to output anything to my browser, I just get a plain white page.

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

        $lastIndex = 2;

        $sql = "SELECT * FROM directory WHERE id > :lastIndex AND user_active != '' LIMIT 20"
        $sth = $conn->prepare($sql);
        $sth->execute(array(':lastIndex' => $lastIndex));

        $c = 1;
        while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
            echo 'ALL STYLING ETC RESULTS HERE';
        $c++;
        }

  $conn = null;        // Disconnect
}

 Answers

87

EXAMPLE. This is your dbc class

<?php

class dbc {

    public $dbserver = 'server';
    public $dbusername = 'user';
    public $dbpassword = 'pass';
    public $dbname = 'db';

    function openDb() {    
        try {
            $db = new PDO('mysql:host=' . $this->dbserver . ';dbname=' . $this->dbname . ';charset=utf8', '' . $this->dbusername . '', '' . $this->dbpassword . '');
        } catch (PDOException $e) {
            die("error, please try again");
        }        
        return $db;
    }

    function getAllData($qty) {
        //prepared query to prevent SQL injections
        $query = "select * from TABLE where qty = ?";
        $stmt = $this->openDb()->prepare($query);
        $stmt->bindValue(1, $qty, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $rows;
    }    
?>

your PHP page:

<?php 
require "dbc.php";

$getList = $db->getAllData(25);

foreach ($getList as $key=> $row) {
         echo $row['columnName'] .' key: '. $key;
    }
Wednesday, March 31, 2021
 
Precastic
answered 8 Months ago
47

I don't think having "numbers" can be done in PHP 5.2 :-(

In PHP 5.3, it becomes possible, if I remember correctly, when you are using the new (new as in PHP >= 5.3) mysqlnd (MySQL Native Driver) driver.

Well, after more digging through my bookmarks I found this article about mysqlnd : PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3

It says this (quote) :

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally.

But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not old libmysql)), and seems to only be the case for prepared statements :-(

Sorry...

A solution would be to have, on the PHP-side, a mapping-system (like an ORM -- see Doctrine ; just as an example of ORM : I don't know if it does what you're asking) to convert results coming from the DB to PHP datatypes...

And yes, this is bad if you want to use operators like === and !==, which are type-sensitive...

Wednesday, March 31, 2021
 
sholsinger
answered 8 Months ago
64

It seems that PDO::MYSQL_ATTR_FOUND_ROWS is a mysql connection option. Thus, it works only as PDO connection option as well. So, set it up this way

$opt  = array(
    PDO::MYSQL_ATTR_FOUND_ROWS   => TRUE,
    // you may wish to set other options as well
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
);
$this->_db = new PDO($dsn,DB_USER,DB_PASS,$opt);
Wednesday, March 31, 2021
 
shivam
answered 8 Months ago
29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 3 Months ago
75

When using a SQL statement such as INSERT, UPDATE or DELETE with a PreparedStatement, you must use executeUpdate, which will return the number of affeted rows. In this case there is simply no ResultSet produced by the sql operation and thus calling executeQuery will throw a SQLException.

If you actually need a ResultSet you must make another statement with a SELECT SQL operation.

See the javadoc for PreparedStatement#executeQuery and PreparedStatement#executeUpdate

Thursday, August 19, 2021
 
Navaneeth K N
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :