Traitement fractionné d'une grande table de BDD

**clend** · 07/02/2020, 08h57

Bonjour,

Confronté au traitement séquentiel d'une table imposante de BDD, je tente de pratiquer une itération par blocs de 4000 lignes en utilisant les 2 paramètres complémentaires "LIMIT" & "OFFSET" à la fin de chaque requête.
Mon script présenté ci-dessous fonctionne parfaitement pour une table n'excédant pas 4000 lignes, mais au-delà le processus tourne indéfiniment sans aucun affichage de résultats.
Si je teste manuellement chaque boucle (ex: table de 14000 lignes) directement par phpmyadmin, tout est correct.

nb: si je place une instruction "break' entre mes 2 boucles "while" (cf. ligne 55) -pour cesser le processus à la fin de la 1ère boucle-, les résultats sont bons. Mon problème se situe donc bien dans le processus d'itération, dès le second passage (traitement des lignes 4001 à 8000), voire plus.

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
52
53
54
55
56
57
58
59
60
61
62
 
<?php
$conn = new mysqli('localhost','user','pass','bdd');
 
// décompte du nombre de lignes à traiter
$riq = "SELECT id FROM syw7g_ameliv
WHERE ((cp_ville != '') AND (a3 = 'C') AND (url IS NOT NULL) AND !(((code_profession = '60') AND (b5 = 'S'))
OR (code_profession = '86') OR (code_profession = '50') OR (code_profession = '21') OR (code_profession = '28') OR (code_profession = '98')))";
$resultat = $conn->query($riq) or die('Erreur SQL !'. $riq .'' . $conn->error);
$nbl = $resultat->num_rows;
 
// calcul du nombre d'itérations de 4000 lignes à traiter
$boucles = intval($nbl/4000);
$ntot = $boucles*4000;
if ($ntot < $nbl) {$boucles = $boucles + 1;}
 
// préparation du tableau d'affichage des résultats
echo '<table cellpadding="1" cellspacing="1" border="3" style="font-size:12px;">';
echo '<thead>';
echo '<tr class="centrer-noir">';
echo '<th class="centrer">Praticien</th>';
echo '<th class="centrer">Profession</th>';
echo '<th class="centrer">Spécialité</th>';
echo '<th class="centrer">Adresse</th>';
echo '<th class="centrer">Ville</th>';
echo '</tr>';
echo '</thead>';
echo '<tbody>';
 
$debut = -4000;
$ajout = 4000;
$num_boucle = 1;
while($num_boucle <= $boucles) {
   $debut = $debut + 4000;
   if ($num_boucle == $boucles) {$ajout = $nbl-$debut;}
   $resultat->free();
   $riq = "SELECT nom, prenom, profession, specialite, cp_ville, num_rue, voie, nom_voie, url FROM syw7g_ameliv
   WHERE ((cp_ville != '') AND (a3 = 'C') AND (url IS NOT NULL) AND !(((code_profession = '60') AND (b5 = 'S')) OR (code_profession = '86') OR (code_profession = '50')
   OR (code_profession = '21') OR (code_profession = '28') OR (code_profession = '98'))) ORDER BY nom LIMIT " .$ajout. " OFFSET " .$debut;
   $resultat = $conn->query("SET NAMES utf8");
   $resultat = $conn->query($riq) or die('Erreur SQL !'. $riq .'' . $conn->error);
 
   while($arr = $resultat->fetch_array(MYSQLI_NUM)) {
       echo '<tr>';
       $nom = $arr[1] . ' ' .$arr[0];
       $profession = ucfirst(mb_strtolower($arr[2]));
       $specialite = ucfirst(mb_strtolower($arr[3]));
       echo "<td class='centrer'>".$nom."</a></td>";
       echo "<td class='centrer'>".$profession."</td>";
       echo "<td class='centrer'>".$specialite."</td>";
       echo "<td class='centrer'>".trim(mb_strtolower($arr[5])). " " .trim(mb_strtolower($arr[6])). " " .trim(mb_strtolower($arr[7]))."</td>";
       echo "<td class='centrer'>".trim(mb_strtolower($arr[4]))."</td>";
       echo '</tr>';
   }
   $num_boucle++;
}
echo '</tbody>';
echo '</table>';
 
$resultat->free();
$conn->close();
?>

Peut-être ai-je mal dimensionné chaque bloc en prenant 4000 lignes ?
Merci à celui qui me trouvera mon erreur et me précisera le correctif à y apporter.
Bonne journée à tous.

Invité · 07/02/2020, 09h32

Bonjour,

1- c'est, à peu de chose près, le même principe qu'une pagination PHP.

2- améliore l'indentation, qu'on y voit clair.

**clend** · 07/02/2020, 09h59

Mais j'avais bien fait ces recherches avant de poster ici et lu la technique de pagination php proposée. Le principe proposé est très proche de la technique que j'ai adoptée ici. Elle diffère simplement dans le fait que mon traitement porte toujours sur la totalité de la table, de A à Z en séquence sans se poser le problème du n° de page concernée.

Quant à l'indentation, elle existe dans mon script source sur mon pc et avait disparu dans mon copier / coller : je l'ai rétablie. Merci.

Invité · 07/02/2020, 11h01

Sauf erreur... :

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
<?php
$conn = new mysqli('localhost','user','pass','bdd');
$conn->query("SET NAMES utf8"); 
 
// Nombre TOTAL de lignes à traiter
$riq = "SELECT COUNT(*) as total_lignes FROM syw7g_ameliv
	WHERE ((cp_ville != '') AND (a3 = 'C') AND (url IS NOT NULL) AND !(((code_profession = '60') AND (b5 = 'S'))
	OR (code_profession = '86') OR (code_profession = '50') OR (code_profession = '21') OR (code_profession = '28') OR (code_profession = '98')));";
$resultat = $conn->query($riq) or die('Erreur SQL !'. $riq .'' . $conn->error);
$row = $resultat->fetch_assoc();
$nbreTotalLignes = $row['total_lignes'];
$resultat->free();
 
// PAGINATION
$nbreParPage = 4000;
// nombre de pages à créer
$nbreTotalPages = ceil($nbreTotalLignes/$nbreParPage);
// tableau d'affichage des résultats
?>
	<table cellpadding="1" cellspacing="1" border="3" style="font-size:12px;">
		<thead>
			<tr class="centrer-noir">
				<th class="centrer">Praticien</th>
				<th class="centrer">Profession</th>
				<th class="centrer">Spécialité</th>
				<th class="centrer">Adresse</th>
				<th class="centrer">Ville</th>
			</tr>
		</thead>
		<tbody>
<?php
$numPage = 1;
while($numPage <= $nbreTotalPages) 
{
	$sqlLimit = $nbreParPage;
	$sqlOffset = ($numPage - 1) * $nbreParPage;
	$riq = "SELECT nom, prenom, profession, specialite, cp_ville, num_rue, voie, nom_voie, url FROM syw7g_ameliv
		WHERE ((cp_ville != '') AND (a3 = 'C') AND (url IS NOT NULL) AND !(((code_profession = '60') AND (b5 = 'S')) OR (code_profession = '86') OR (code_profession = '50')
		OR (code_profession = '21') OR (code_profession = '28') OR (code_profession = '98'))) ORDER BY nom LIMIT " .$sqlLimit. " OFFSET " .$sqlOffset. ";";
	$resultat = $conn->query($riq) or die('Erreur SQL !'. $riq .'' . $conn->error);
 
	while( $arr = $resultat->fetch_assoc() )
	{
	   $nom = htmlspecialchars(mb_convert_case($arr['prenom'], MB_CASE_TITLE) . ' ' . mb_convert_case($arr['nom'], MB_CASE_UPPER));
	   $profession = htmlspecialchars(mb_convert_case($arr['profession']), MB_CASE_TITLE);
	   $specialite = htmlspecialchars(mb_convert_case($arr['specialite']), MB_CASE_TITLE);
	   $adresse = htmlspecialchars(mb_convert_case($arr['num_rue']. ' ' .$arr['voie']. ' ' .$arr['url'],  MB_CASE_LOWER));
	   $cp = htmlspecialchars($arr['cp_ville']);
?>
			<tr>
				<td class="centrer"><?php echo $nom; ?></td>
				<td class="centrer"><?php echo $profession; ?></td>
				<td class="centrer"><?php echo $specialite; ?></td>
				<td class="centrer"><?php echo $adresse; ?></td>
				<td class="centrer"><?php echo $cp; ?></td>
			</tr>
<?php
   }
   $numPage++;
   $resultat->free();
}
$conn->close();
?>
		</tbody>
	</table>

**clend** · 07/02/2020, 13h09

Envoyé par jreaux62

Sauf erreur... :

Je te remercie à plus d'un titre :
1. mon script fonctionne parfaitement avec tes correctifs,
2. au travers de tes modifications, j'ai pu découvrir quelques astuces ignorées jusqu'à ce jour,
3. tu n'avais commis qu'une infime erreur dans tes adaptations : il y avait un ";" au lieu d'un ")" à la fin du second ordre "while" !

À tête reposée, j'essaierai de comprendre ce qui coinçait dans mon script original. Mais je crois avoir déjà compris que mon "select" dans la boucle était mal terminé, ce qui faisait que la variable d'offset restait inconnue ... donc égale à 0, sans offset : je tournais toujours en rond à ne traiter que les 4000 premières lignes !

Tu es vraiment sympa d'avoir pris autant de temps à refondre mon script. Encore mille mercis à toi, et je t'envoie un beau rayon de soleil Niçois !
Amicalement.
claude

Invité · 07/02/2020, 13h50

Petit bonus...
Pour éviter les erreurs entre tes 2 requêtes :

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
 
// Clause WHERE
$clauseWHERE = " WHERE (
		cp_ville != '' 
		AND a3 = 'C' 
		AND url IS NOT NULL 
		AND !(
			(code_profession = '60' AND b5 = 'S')
			OR 
			(code_profession IN ('86','50','21','28','98'))
			)
	)";
// Nombre TOTAL de lignes à traiter
$riq = "SELECT COUNT(*) as total_lignes 
	FROM syw7g_ameliv
	".$clauseWHERE.";";

Code :

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

1
2
3
4
5
6
7
8
9
10
while($numPage <= $nbreTotalPages) 
{
	$sqlLimit = $nbreParPage;
	$sqlOffset = ($numPage - 1) * $nbreParPage;
	$riq = "SELECT nom, prenom, profession, specialite, cp_ville, num_rue, voie, nom_voie, url 
		FROM syw7g_ameliv
		".$clauseWHERE."
		ORDER BY nom 
		LIMIT " .$sqlLimit. " OFFSET " .$sqlOffset. ";";
	$resultat = $conn->query($riq) or die('Erreur SQL !'. $riq .'' . $conn->error);

Traitement fractionné d'une grande table de BDD [MySQL]

PHP & Base de données

Discussions similaires

Partager

Partager