search

oscarotero / simple-crud

PHP library to provide magic CRUD in MySQL/Sqlite databases with zero configuration
MIT License
242 stars 58 forks source link

MySQL SQL_CALC_FOUND_ROWS & FOUND_ROWS() #29

Open PhantomArt opened 6 years ago

PhantomArt commented 6 years ago

How to implement? Some ideas?

oscarotero commented 6 years ago

This is not implemented currently, but it's a good thing to have. Meanwhile, you can execute the query by yourself:

$statement = $simpleCrud->execute('SELECT SQL_CALC_FOUND_ROWS ...');
oscarotero commented 6 years ago

Hi. I've implemented this. Now, you can use SQL_CALC_FOUND_ROWS passing true as the second argument of the limit function. For example:

$result = $db->items->select()
    ->limit(50, true) // <-- The second argument "true" use this option
    ->run();

$total = $result->getTotal(); //Returns the result of FOUND_ROWS();

Can you test whether it works propertly? Do you know if there's a alternative to sqlite? After a quick search, seems like the only way to emulate it is performing a second query with a SELECT COUNT(*).

PhantomArt commented 6 years ago

Apparently, this is the only way out. But this is bad, because between the SELECT * and SELECT COUNT(*) calls a string can be inserted that will affect the result.