Problem in ORM framework

[kney]

Dit is de code van een zoekfunctie in mijn project.

De reden waarom ik GET ipv POST gebruikt heb, is omdat het makkelijker werken was in het framework.

Het probleem dat ik nu ondervind is dat ik altijd maar 5 records terugkrijg als ik een count doe (wat natuurlijk komt door de LIMIT), en door het framework maakt het niet uit of je eerst de count of limit doet, het geeft allebei niet het correcte resultaat.

Ik zou een manier willen hebben om die count correct te doen zodat ook mijn next en previous knoppen goed gaan werken want nu krijgt hij altijd 1 als resultaat (kijk naar variabele $pages)

 

This is the code from my search function in my project.

The reason I use GET instead of POST is because it's easier to manage in the framework.

The problem that i'm getting right now is that i only get 5 records when i perform a count (obviously because of the LIMIT function) and because of the framework it doesn't matter if you perform the count first and then the limit or the other way around, it doesn't give the correct result.

I would like to find a way to correctly do the count so my next and previous buttons will work correctly because my $pages variable always gets 1 as result.

 

<?php
if(isset($_GET['submit'])){
    $firstname = $_GET['txtFirstname'];
    $lastname = $_GET['txtLastname'];  
    $rol = $_GET['rollen'];
    $partnerID = $_GET['partners'];
    $searchUser = new User();

    if (!empty($firstname)) {
        $searchUser->whereAdd("`user`.firstname like '%" . $firstname . "%'");
    }
    if (!empty($lastname)) {
        $searchUser->whereAdd("lastname like '%" . $lastname . "%'");
    }
    $joinPartner = new Partner();
    if (!empty($partnerID)) {
        $joinPartner->id = $partnerID;
    }
    $searchUser->joinAdd($joinPartner, 'LEFT');
    $searchUser->selectAs($joinPartner, 'p_%s');
    $searchUser->selectAs($searchUser, 'u_%s');

    $joinRole = new Role();
    if (!empty($rol)) {
        $joinRole->id = $rol;
    }
    $user_role = new User_role();
    $user_role->joinAdd($joinRole);
    $searchUser->joinAdd($user_role);
    $searchUser->selectAs($joinRole, 'r_%s');

    $results_per_page = 5;
                
    if(!isset($_GET['screen'])){
       $_GET['screen'] = 0;
    }
    $start = $_GET['screen'] * $results_per_page;
    $searchUser->limit($start, $results_per_page);
    $interface->assign('screen', $_GET['screen']);

    $searchUser->find();
    $searchResult = array();
    
    while($searchUser->fetch()) {
        $searchResult[] = clone($searchUser);
    }
    $interface->assign('searchUsers', $searchResult);

    $pages = ceil(COUNT($searchResult) /$results_per_page);
    $interface->assign('pages', $pages);

    /* Hier gaat het mis, door de limit krijg ik maar 5 resultaten terug
       Terwijl ik alle gevonden resultaten wil weergeven
    */
    $interface->assign('numberOfUsersFound', count($searchResult));
?> 

[TeNDoLLA]

Apparently you cant use the LIMIT in the query that defines the total pages math. You use two queries, one to get all records and count the total pages. Then you run another query with the search results and LIMIT it and print out the results for the current page.

[kney]

Without the framework you can do it with

SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 5;

SELECT FOUND_ROWS();

 

I need something similar because 2 queries just look awful

[TeNDoLLA]

Why not just make

SELECT COUNT(id) AS total FROM table; // Gets the count of rows, this if where you count the total pages.

 

Then when user clicks page say for example page 5 you would define start and offset for the result query using the LIMIT to get the results for page 5 based on the first query. As I understood you think I meant two queries for counting the rows, that is not what I meant. One query for counting rows total and one query to get the results based on page the user is on. You just CAN'T use one query only, it is impossible to get total pages and search results for LIMIT 5 in same query.

 

And as mentioned, you can't use the LIMIT in the query where you get the total row count. And if you need to get the paging for some search results, you just add the search conditions in the query I provided above. Then it gets the total pages for the current search results.

[kney]

OK, now i know what you mean..

but i don't want all the rows in my table.. i only want the rows with the results i searched for..

 

so if i type "test" i only want to get 6 as count (cuz there are 6 with "test" in db but i have a total of 10 users)

[TeNDoLLA]

Well then you just add the search terms in the queries, BOTH queries. The one where you get the total rows for counting total pages AND the one where you get the actual results for the current page (with LIMIT 5).

 

Example for gettin the total results

$search = mysql_real_escape_string($_POST['search']);
$sql = "SELECT COUNT(id) AS total FROM table WHERE someField LIKE '%$search%'";

// Or depending the situation what you want to achieve
$sql = "SELECT COUNT(id) AS total FROM table WHERE someField ='$search'";

[ignace]

Without the framework you can do it with

SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 5;

SELECT FOUND_ROWS();

 

I need something similar because 2 queries just look awful

 

This is the way to go, not SELECT count