PDO SQL Query returning fewer rows than expected

Question

I am attempting to build an API to return data from mySQL. I am still trying to get the hang of PDO. I have the below query that should return all rows from the DB, based on a single parameter but I have noticed it often return 1 less than should be returned. When there's 2 rows, it returns 1, when there's 1 row it returns nothing.

I've attached a screenshot of the database results.

Any suggestions would be appreciated.

[![<?php
// required header
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

// include database and object files
include_once '../config/database.php';
include_once '../objects/casualty.php';

// instantiate database and casualty object
$database = new Database();
$db = $database->getConnection();

// initialize object
$casualty = new casualty($db);

// set ID property of record to read
$casualty->id = isset($_GET\['id'\]) ? $_GET\['id'\] : die();

// query categorys
$stmt = $casualty->cemetery();
$num = $stmt->rowCount();

// check if more than 0 record found
if($num>0){

    // products array
    $casualtys_arr=array();
    $casualtys_arr\["records"\]=array();

    // retrieve our table contents
    // fetch() is faster than fetchAll()
    // http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        // extract row
        // this will make $row\['name'\] to
        // just $name only
        extract($row);

        $casualty_item=array(
            "ID" => $ID,
            "Filename" => $fldgraphicname,  
            "Surname" => $fldsurname,  
            "FirstNameInitial" => $fldfirstnameinitial  

        );

        array_push($casualtys_arr\["records"\], $casualty_item);
    }

    echo json_encode($casualtys_arr);
}

else{
    echo json_encode(
        array("message" => "No Casualties found.")
    );
}
?>
<?php
class casualty{

    // database connection and table name
    private $conn;
    private $table_name = "tblcasualty";

    // object properties
    public $id;
    public $fldgraphicname;
        public $fldsurname;
        public $fldfirstnameinitial;

    public function __construct($db){
        $this->conn = $db;
    }

    public function cemetery(){            
        // query to read single record
                    $query = "SELECT *
                        FROM 
                            tblnames 
                        WHERE
                            tblcemetery_ID = ?
                            ORDER BY ID
                         ";
        $stmt = $this->conn->prepare( $query );

        // bind id of product to be updated
        $stmt->bindParam(1, $this->id, PDO::PARAM_INT);

        // execute query
        $stmt->execute();

        // get retrieved row
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        // set values to object properties
        $this->fldgraphicname = $row\['fldgraphicname'\];
        $this->fldsurname = $row\['fldsurname'\];
        $this->fldfirstnameinitial = $row\['fldfirstnameinitial'\];    

            return $stmt;
    }        

}
?>][1]][1]

score 2 · Accepted Answer · answered Sep 22 '17 at 18:56

In your cemetery function you have a fetch. This is advancing the cursor one position so when you get back to your main script and use the fetch you are always on the second row.

Solutions:

Loop the fetch in the function and just return an array of all the values.

or

Don't fetch in the function and return the result set so you can fetch the full block.

PDO SQL Query returning fewer rows than expected

1 Answers1