[Pagination ]Comment joindre BETWEEN et un autre critère dans une requête

**pierrot10** · 02/03/2019, 10h01

Bonjour à tous

Je rame pas mal sur un "grosse" requête MSSQL avec PHP.
J'ai beaucoup d'enregistrements et je dois faire de la pagination avec le retour de la requete.

Ma requête ne fonctionne pas au niveau de mon WHERE. JE dois jondre ceci

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

$where ="la.id_label > 0 AND (la.glaciers_id_glacier IN (2) AND la.locations_id_location IN (1))";

et

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

rows BETWEEN ? and ?

Voici ma requete complète

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
$tsql= 'SELECT rows, la.id_label, g_name, l_name, locations_id_location, t_name, r_name, b_name, boxes_id_box, isIn, isOut, isAnalized, comment FROM (SELECT ROW_NUMBER() OVER(ORDER BY id_label) AS rows, id_label, glaciers_id_glacier, locations_id_location, types_id_type, replicas_id_replica, boxes_id_box, isIn, isOut, isAnalized, comment FROM labels) AS la
          INNER JOIN glaciers AS gl ON gl.id_glacier = la.glaciers_id_glacier
          INNER JOIN locations AS lo ON lo.id_location = la.locations_id_location
          INNER JOIN types AS ty ON ty.id_type = la.types_id_type
          INNER JOIN replicas AS re ON re.id_replica = la.replicas_id_replica
          INNER JOIN boxes AS bo ON bo.id_box = la.boxes_id_box
            WHERE '.$where.'
            AND rows BETWEEN ? and ?
            ORDER BY la.id_label ASC;';
          $getResults= sqlsrv_query($conn, $tsql, array(), array("Scrollable"=>"buffered"));

Ceci ne me retorune rien
alors que si je supprime ceci ($where)

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
$tsql= 'SELECT rows, la.id_label, g_name, l_name, locations_id_location, t_name, r_name, b_name, boxes_id_box, isIn, isOut, isAnalized, comment FROM (SELECT ROW_NUMBER() OVER(ORDER BY id_label) AS rows, id_label, glaciers_id_glacier, locations_id_location, types_id_type, replicas_id_replica, boxes_id_box, isIn, isOut, isAnalized, comment FROM labels) AS la
          INNER JOIN glaciers AS gl ON gl.id_glacier = la.glaciers_id_glacier
          INNER JOIN locations AS lo ON lo.id_location = la.locations_id_location
          INNER JOIN types AS ty ON ty.id_type = la.types_id_type
          INNER JOIN replicas AS re ON re.id_replica = la.replicas_id_replica
          INNER JOIN boxes AS bo ON bo.id_box = la.boxes_id_box
            WHERE rows BETWEEN ? and ?
            ORDER BY la.id_label ASC;';
          $getResults= sqlsrv_query($conn, $tsql, array(), array("Scrollable"=>"buffered"));

il me retroune des résultats qui me semble OK au niveau de la pagination, mais évidemment, il n'y a aucun filtre.

Je pense donc que je suis dans le flou au niveau du WHERE, de manière a ce que ma requete me retourne un résultat selon le fitre (0 à 8 critères, tous des array) en tenant compte que le résulta doit être panigé. C'est a dire que je veux 20 lignes par pages et évidement autant de page pour parcourir tout le rsultat de ma requête.

Comment puis-je donc bien corrigé ma reqteuet MSSQL avec PHP?

Mille mercis pour vos lumières et belle journée

**rawsrc** · 02/03/2019, 11h09

Salut,

et avec ceci, ça donne quoi ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$sql = <<<sql
SELECT
    rows, la.id_label, g_name, l_name, locations_id_location,
    t_name, r_name, b_name, boxes_id_box, isIn, isOut, isAnalized, comment
FROM
    (SELECT
        ROW_NUMBER() OVER(ORDER BY id_label) AS rows, id_label, glaciers_id_glacier,
        locations_id_location, types_id_type, replicas_id_replica, boxes_id_box, isIn,
        isOut, isAnalized, comment FROM labels
    ) AS la
    INNER JOIN glaciers  AS gl ON gl.id_glacier  = la.glaciers_id_glacier
    INNER JOIN locations AS lo ON lo.id_location = la.locations_id_location
    INNER JOIN types     AS ty ON ty.id_type     = la.types_id_type
    INNER JOIN replicas  AS re ON re.id_replica  = la.replicas_id_replica
    INNER JOIN boxes     AS bo ON bo.id_box      = la.boxes_id_box
WHERE
    la.id_label                  > 0
    AND la.glaciers_id_glacier   = 2
    AND la.locations_id_location = 2
    AND rows BETWEEN ? AND ?
ORDER BY
    la.id_label ASC;
sql;

**pierrot10** · 02/03/2019, 12h24

Re-bonjour RAWSRC,

Je suis désolé car j'ai mal commencer ce post. Je le résume afin d'être plus précis dans la description de ma problématique. J'aurais du commencer comme cela

J'ai un reséultat intéressant mais ca ne fonctionne pas encore.
Je vais donc résumé et être plus précis

J'ai un filtre (je ne peux le montrer car l'application est derriere un VPN), qui me permet de sélectionner plusieurs glaciers, plusieurs locations, plusieurs positions, et encore.

Dans ma base de donnée j'ai plusieurs milliers d'entrées (lignes) qui correspondent à toutes ces solutions.

Je dois donc extraire les données (PHP/MSSQL) et affcihier les résultat pas pages (pagination). J'ai cherché et essayé plusieurs solutions, c'est pourquoi mon code doit être un peu du malpropore...

D'abord, je me renseigne sur le nombre de lignes que va m'extraire, ma base de donnée en fonction de mes filtres
et je défini le nombre de lignes que je veux par page

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
        $tsql = "SELECT COUNT(id_label) FROM labels AS la
        WHERE $where";
        /* Set the number of results to display on each page. */
        $rowsPerPage = 10;
        /* Define a query to get the number of rows on the server.
       This query doesn't actually retrieve rows, it just
       retrieves the number of rows that are available. */
 /* Execute the query. */
       $stmt = sqlsrv_query($conn, $tsql);
       if($stmt === false)
       {
         echo "Error in query execution.";
         die( print_r( sqlsrv_errors(), true));
       }
        /* Get the number of rows returned. */
        $rowsReturned = sqlsrv_fetch_array($stmt);
        echo "<p style='font-size=0.8em;'>".$rowsReturned[0]." found entries (checkit)</p>";
 
        if($rowsReturned === false)
        {
          echo "Error in retrieving number of rows.";
          die( print_r( sqlsrv_errors(), true));
        }
        elseif($rowsReturned[0] == 0)
        {
          echo "No rows returned.";
        }
        else
        {
/*Get URL parameters */
// Suite du code
}

Je peux donc faire mes liens qui me permettront de passer d'une page à une autre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
else{
          /*Get URL parameters */
//suite du code
          $query_sting = $_SERVER['QUERY_STRING'];
          if(isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on')
              $link = "https";
          else
              $link = "http";
          // Here append the common URL characters.
          $link .= "://";
          // Append the host(domain name, ip) to the URL.
          $link .= $_SERVER['HTTP_HOST'];
          // Append the requested resource location to the URL
          $link .= $_SERVER['REQUEST_URI'];
          // Print the link
          //echo "<br>Link: ".$link."<br>";
 
          if(isset($_GET['pageNum'])){
          $p = $_GET['pageNum'];
          unset($_GET['pageNum']);
          }
          else {
            $p=1;
          }
          $url_query = http_build_query($_GET);
 
          /* Display page links. */
          $numOfPages = ceil($rowsReturned[0]/$rowsPerPage);
 
// AFFICHAGE DES LIEN
          echo "<b>Page(s):</b> ";
          for($i = 1; $i<=$numOfPages; $i++)
          {
 
            $pageNum = "?pageNum=".$i;
            print('<a href="'.$pageNum.'&'.$url_query.'">');
 
            if($p == $i){
              echo "<b>[".$i."]</b>";
            }
            else {
              echo $i;
            }
            print('</a>&nbsp;&nbsp;');
          }
            echo "</br></br>";
}
echo "where:<pre>";
echo $where;
echo "</pre>";

Le $where, peut afficher ceci, par exemple

la.id_label > 0 AND (la.glaciers_id_glacier IN (1,2,4) AND la.isout IN (1,0))

Maintenant je veux extraire les informations de ma base de donnée, selon $where et la page sélectionnée et le nombre de lignes par page.
J'ai donc repris ton code, qui en fait est très similaire au mien, sauf que dans le tien, il semble qui ne va pas tenir compte de l'array envoyé en parametre ($_GET), n'est-ce pas?

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
$tsql2 = '
			SELECT
			    rows, la.id_label, g_name, l_name, locations_id_location,
			    t_name, r_name, b_name, boxes_id_box, isIn, isOut, isAnalized, comment
			FROM
			    (SELECT
			        ROW_NUMBER() OVER(ORDER BY id_label) AS rows, id_label, glaciers_id_glacier,
			        locations_id_location, types_id_type, replicas_id_replica, boxes_id_box, isIn,
			        isOut, isAnalized, comment FROM labels
			    ) AS la
			    INNER JOIN glaciers  AS gl ON gl.id_glacier  = la.glaciers_id_glacier
			    INNER JOIN locations AS lo ON lo.id_location = la.locations_id_location
			    INNER JOIN types     AS ty ON ty.id_type     = la.types_id_type
			    INNER JOIN replicas  AS re ON re.id_replica  = la.replicas_id_replica
			    INNER JOIN boxes     AS bo ON bo.id_box      = la.boxes_id_box
			WHERE
			    la.id_label                  > 0
			    AND la.glaciers_id_glacier   = 1,2,4 // Est-ce possible
			    AND la.locations_id_location = 2
			    AND rows BETWEEN ? AND ?
			ORDER BY,
			    la.id_label ASC;';
			$getResults2= sqlsrv_query($conn, $tsql2, array(), array("Scrollable"=>"buffered"));

Maintenant, je pense que la partie qui suit, n'est pas cohérente, du moins, il ne fonctionne pas parfaitement avec ma requete. Ne devrait-elle pas etre repenser

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
// Determine which row numbers to display.
          if(isset($_REQUEST['pageNum']))
          {
            $highRowNum = $_REQUEST['pageNum'] * $rowsPerPage;
            $lowRowNum = $highRowNum - $rowsPerPage;
          }
          else
          {
            $lowRowNum = 1;
            $highRowNum = $rowsPerPage;
          }
// Set query parameter values.
          $params = array($lowRowNum, $highRowNum);
          echo $lowRowNum.'-'.$highRowNum;
          // Execute the query.
          $stmt2 = sqlsrv_query($conn, $tsql2, $params);

Si je demande d'afficher 10 lignes par page et que j'affiche la page 2, il devait m'afficher les ligne 11 à 20. Mais si mon filtre demande l'affcihe du glaciers 4 et que les lignes correspondantes à ce glacier on les ID 300 à 310, il semble que qu'il m'affichera aucune lignes, car 11 à 20 n'est pas compris dans 300 et 310

Je pense donc que mon problème est à ce niveau, mais je ne sais pas ou je dois modifier. Es-t ce que ma requete MSSQL peut gérer ceci.
Comment peut-on gérer la pagination différement que de la manière que je l'ai entrepris????
Peut-on faire plus simple?

Milles mercis pour votre précieuse aide, car je traine depuis un moment la dessus et je suis un peu bloquer

Bonne journée

**pierrot10** · 02/03/2019, 20h31

J'ai vraiment ciblé mon problème. J'ai passé l'après midi

Le problème se situe au niveau du 'rows'

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

AND rows BETWEEN ? AND ?

Voici la requete

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 $tsql2 = '
			SELECT
			    rows, la.id_label, g_name, l_name, locations_id_location,
			    t_name, r_name, b_name, boxes_id_box, isIn, isOut, isAnalized, comment
			FROM
			    (SELECT
			        ROW_NUMBER() OVER(ORDER BY id_label) AS rows, id_label, glaciers_id_glacier,
			        locations_id_location, types_id_type, replicas_id_replica, boxes_id_box, isIn,
			        isOut, isAnalized, comment FROM labels
			    ) AS la
			    INNER JOIN glaciers  AS gl ON gl.id_glacier  = la.glaciers_id_glacier
			    INNER JOIN locations AS lo ON lo.id_location = la.locations_id_location
			    INNER JOIN types     AS ty ON ty.id_type     = la.types_id_type
			    INNER JOIN replicas  AS re ON re.id_replica  = la.replicas_id_replica
			    INNER JOIN boxes     AS bo ON bo.id_box      = la.boxes_id_box
			WHERE
			    la.id_label > 0 AND (la.glaciers_id_glacier IN (1,2,4) AND la.isout IN (1,0))
			    AND rows BETWEEN ? AND ?
 
			ORDER BY
			    la.id_label ASC;';

Le problème est que si j'extrais de ma base de donnée les infromations sur les glaciers 1,2 et 4, il y aura un trou entre le glacier 2 et 4. En fait la valeur de 'rows' est la même que 'id_label'
Je m'expliqe.

Si pour l'exemple, j'avais 10 enregistrements par glacier (j'en ai bien plus), mon code me retournera

rows, id_glacier, n_glacier, val
1-1-glacier1-1
2-2-glacier1-2
3-3-glacier1-3
4-4-glacier1-4
5-5-glacier1-5
6-6-glacier1-6
7-7-glacier1-7
8-8-glacier1-8
9-9-glacier1-9
10-10-glacier1-10

11-11-glacier2-1
12-12-glacier2-2
13-13-glacier2-3
14-14-glacier2-4
15-15-glacier2-5
16-16-glacier2-6
17-17-glacier2-7
18-18-glacier2-8
19-19-glacier2-9
20-20-glacier2-10

41-41-glacier4-1
42-42-glacier4-2
43-43-glacier4-3
44-44-glacier4-4
45-45-glacier4-5
46-46-glacier4-6
47-47-glacier4-7
48-48-glacier4-8
49-49-glacier4-9
50-50-glacier4-10

Etant donné que la premiere valeur de mon premier glacier à l'id 1,
ma premiere page l'affichera avec les 5 lignes suivantes.
Si je clique sur ma 2ème page, j'aurai les enregistrements 6 à 10 du glacier 1
Si je clique sur ma 3ème page, j'aurai les enregistrements 1 à 5 du glacier 2
Si je clique sur ma 4ème page, j'aurai les enregistrements 6 à 10 du glacier 2

mais si je clique sur 5ème page, elle sera vide, tout comme la page 6.

Je pense que pour ca fonctionne, il faudrait que 'rows' me retourne une incrémentation pour avoir en fait ceci (observer la 1ère colonne)

rows, id_label, g_name, val
1-1-glacier1-1
2-2-glacier1-2
3-3-glacier1-3
4-4-glacier1-4
5-5-glacier1-5
6-6-glacier1-6
7-7-glacier1-7
8-8-glacier1-8
9-9-glacier1-9
10-10-glacier1-10

11-11-glacier2-1
12-12-glacier2-2
13-13-glacier2-3
14-14-glacier2-4
15-15-glacier2-5
16-16-glacier2-6
17-17-glacier2-7
18-18-glacier2-8
19-19-glacier2-9
20-20-glacier2-10

21-41-glacier4-1
22-42-glacier4-2
23-43-glacier4-3
24-44-glacier4-4
25-45-glacier4-5
26-46-glacier4-6
27-47-glacier4-7
28-48-glacier4-8
29-49-glacier4-9
30-50-glacier4-10

L'autre question, quel est l'intérêt que 'rows' me retroune la même chose que 'id_label'???

Si mes pensées sont bonnes, comment pourrais-je modifier ma requête pour que 'rows' ne corresponde pas à 'id_label', mais qu'il incrémente?

Ou alors que me suggériez-vous de faire pour que ma pagination fonctionne sans tenir compte des écrats entre les id_label?

Avez-vous besoin de plus de code?

Milles mercis pour vos lumières.

**pierrot10** · 02/03/2019, 22h47

J'ai résolu mon problème en utilisant une méthode plus récente

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
$p=$page-1;
$p = $p * $rowsPerPage;
// la.id_label > 0 AND (la.glaciers_id_glacier IN (1,2,4) AND la.isout IN (1,0))
		$tsql2 = '
			SELECT
			    la.id_label, g_name, l_name, locations_id_location,
			    t_name, r_name, b_name, boxes_id_box, isIn, isOut, isAnalized, comment
			FROM labels AS la
			    INNER JOIN glaciers  AS gl ON gl.id_glacier  = la.glaciers_id_glacier
			    INNER JOIN locations AS lo ON lo.id_location = la.locations_id_location
			    INNER JOIN types     AS ty ON ty.id_type     = la.types_id_type
			    INNER JOIN replicas  AS re ON re.id_replica  = la.replicas_id_replica
			    INNER JOIN boxes     AS bo ON bo.id_box      = la.boxes_id_box
			WHERE '.$where.'
 
			ORDER BY
			    la.id_label ASC
			OFFSET '.$p.' ROWS FETCH NEXT '.$rowsPerPage.' ROWS ONLY;';
 
 
 
        // Execute the query.
        $stmt2 = sqlsrv_query($conn, $tsql2);
        $numRows = sqlsrv_num_rows($stmt);
 
        $numOfPages = ceil($rowsReturned[0]/$rowsPerPage);
        echo "<p style='font-size=0.8em;'>".$rowsReturned[0]." found entries/".$numOfPages." pages</p>";

Ceci m'a aidé
https://koukia.ca/let-sql-server-que...2-a52ca6e5beb2

Une erreur que j'ai commise, c'est d'avoir suivi un tuto qui date de 2010
https://blogs.msdn.microsoft.com/bri...ivers-for-php/
alors que MSSQL 2012 à introduit OFFSET FETCH.

Ca été d'une part pas trop compliqué à implémenter.

Voilà, donc résolu et j'expère ne pas rencontrer des surprises plus tard

[Pagination ]Comment joindre BETWEEN et un autre critère dans une requête [SQL-Server]

PHP & Base de données

Discussions similaires

Partager

Partager