I have quite an issue I can not seem to solve. I am trying to get a row count from a select statement.
I should start by saying I have tried most all methods resulting from google searches on this issue.
I am using the result set so I would prefer not to make a second query.
The query uses a prepared select statement which seems to be a main issue if I understand it correctly.
I decided to try a simple approach using PHP's native count() function. Which lead me here because I finally reached the end of the rope on this.
On to the details...within a class of mine, I make the query like this.
// Create database connection
$database = DatabaseFactory::getFactory()->getConnection();
// Set and execute database query
$sql = "SELECT * FROM `service_orders` WHERE `agency_id` = :agency_id $filter ORDER BY $sort $order $per_page";
$query = $database->prepare($sql);
$query->execute($query_array);
// If results
if ($query->rowCount() > 0) {
$results = $query->fetchAll();
self::$order_count = "Count: " . count($results);
return $results;
}
// Default, return false
return false;
Findings
count($results) like I did above, I get the total rows in the database (Let's say 50).print_r($results), it shows the array with the proper number of entries (Let's say 10) that of course differs from the total rows in the database.How can these two differ? It's as if the count($results) is misreading the result array.
More Findings
Within my actual php page, I call the class to retrieve the data like this.
$results = OrderModel::getServiceOrders();
echo count($results);
Strangely enough, if I then perform count($results) it gives me the correct reading of the result array (which in my example here would be 10).
I am perplexed by this as the count function is being performed on the exact same array. The only difference is one is called on the array within the class, and the other is called on the array returned from the class.
Does anyone have any ideas on how to solve this or why there is the discrepancy when using count() in this instance?
Thank you all in advance! James
Additional Info
This is another mind numbing scenario. If I return the count along with the actual results, I can access it on the page with the correct value (10 rows). Yet, if I set it into a session variable, and access it that way, the count is the whole data set (50 rows). How is it even possible these two values are not the same?!
$results = $query->fetchAll();
Session::set("order_count", $total[0]); // Yields 50 (incorrect)
return [
"results"=> $results,
"count"=> $total[0], // Yields 10 (correct)
];