Forums

Cannot Access DB Query Result Rows

Permalink 1 user found helpful
Hi There,

I am working on upgrading a custom package which has a lot of database logic. Since the whole database Code changed in the new Versions of C5, I have to rewrite all my database calls.

Doing that, I just cannot figure out how to acces my query Results. I have read this page (https://documentation.concrete5.org/developers/appendix/concrete5-version-8-coding-styles/database) but there is not much about accessing the result of a db query.

My current code looks like this:

// values from the user
$email = 'test@test.com'
$password = '123'
$query = "SELECT * FROM jwSignupKontaktperson kp natural join jwSignupPerson p where kp.mail= ? AND kp.passwort= ?";
      $parameters = [$emailAddress, $password];
$app = \Concrete\Core\Support\Facade\Application::getFacadeApplication();
$db = $app->make('database')->connection();
$result = $db->Execute($query, $parameters);
if (!$result){
   // error handling
} else if (count($result->FetchRow()) == 0) {
   // zero results
} else {
   while ($row = $result->FetchRow()) {
      // access row data
Viewing 15 lines of 18 lines. View entire code block.


I just cannot manage to go inside the while loop of "FetchRow" I have logged the $result and the count of the results is 1, so the correct db row has been found.

I also have searched the C5 Codebase and they seem to use exact this code.

Hint: It seems that, as soon as I add a WHERE clause to the query, it stops from going inside my while loop and the $result->FetchRow() gives an array back which just seems to contain "1"

Any idea, what I am doing wrong?

Best Regards
Jan
janwidmer
 
ConcreteOwl replied on at Permalink Reply
ConcreteOwl
Is kp.passwort a typo? should it not be kp.password?
janwidmer replied on at Permalink Reply
janwidmer
the database tables are legacy and the column names are in german. Thats why it's called passwort. So it's actually correct.

In the meantime I also tried to filter for other columns but it's always the same. As soon as I add a where clause, the returned result gives me the number of rows, but I cannot loop over theme.

I also tried with Tables from C5 itself and it behaves the same..
janwidmer replied on at Permalink Reply
janwidmer
I tracked it down:

The problem was, that I used the following logic to check, if the db query result is empty:

// ...
else if(count($result->FetchRow()) == 0){
// ..


That seems to "destroy" the $result and it cannot be looped over afterwards again.
For now, I switched to "$result->numRows() == 0". I know that this method got deprecated.

Any idea for a better way to check if it's an empty result?
Parasek replied on at Permalink Reply
Parasek
Why don't you just use $db->fetchAll() to get rows?
janwidmer replied on at Permalink Reply
janwidmer
Hi Parasek,

Mmmh good question, I don't really know.. There are so many different methods and I haven't found a clear documentation to use either one of them..

As I understood, the methods fetchAll / fetchRow /fetchColumn are supposed to use on the dbResult after executing the query.

But then again, they seem to use the methods to make the db call itself here:https://documentation.concrete5.org/developers/appendix/concrete5-ve... /https://documentation.concrete5.org/developers/database-management/a...

I have used the combination of $db->Execute / $result->fetchRow because I used in my 5.6.x Version of the package also..

Any pro or contras or advice which combination I should use?
Parasek replied on at Permalink Reply
Parasek
1. Use executeQuery() instead execute() (it works, but it's deprecated)

2. fetchAll() is just a "wrapper" for
public function fetchAll($sql, array $params = array(), $types = array())
{
   return $this->executeQuery($sql, $params, $types)->fetchAll();
}

3. To get number of rows try:
$count = $result->rowCount();

If you use 
$rows = $result->fetchAll();

you can get number of rows by
$count = count($rows);
janwidmer replied on at Permalink Reply
janwidmer
Thanks for lighting up the confusion.. but :-)

When you say, fetchAll is just a wrapper which calls executeQuery in the background, is it a good idea to use it on a query result?

$result = $db->executeQuery()
$result->fetchAll() // that would lead to a duplicate query, right?


or is it smart enough to check if fetchAll() is being called on a $db object (run executeQuery / fetchAll) or on a $result (just run fetchAll)?
Parasek replied on at Permalink Best Answer Reply
Parasek
This code
$result = $db->executeQuery('SELECT * FROM xxxx etc.');
$rows = $result->fetchAll();

is the same as:
$rows = $db->fetchAll('SELECT * FROM xxxx etc.');

There won't be a duplicated query.
First fetchAll() is without parameters - it will just "fetch" results.
Second fetchAll() is with parameters - it will execute query and then "fetch" results.

Second example is just shorter version.

No need to check anything, both fetchAll() are methods from different classes (they just have the same name).
- fetchAll() is used on query result object ($result)
- fetchAll('Example sql here') is used on $db object, sql query string is required
janwidmer replied on at Permalink Reply
janwidmer
ah ok, now it makes sense.
Thanks for the patience and your explanations

****************************

for others, here is an overview of 3 different ways to get data:

// 1. use executeQuery / fetchAll
$result = $db->executeQuery($query, $parameters);
if ($result->rowCount() == 0) {
   // no results
} else {
   $rows = $result->fetchAll();
   foreach ($rows as $row) {
      // access data
   }
}
// 2. use executeQuery / fetchRow
$result = $db->executeQuery($query, $parameters);
if ($result->rowCount() == 0) {
   // no results
} else {
Viewing 15 lines of 28 lines. View entire code block.