SQL : insérer en milieu de table

**Léo-Nils** · 07/12/2018, 21h54

Bonjour

J'ai une table ordonnée dans une base de donnée et je dois pouvoir insérer au milieu de la table une nouvelle ligne, connaissant les deux valeurs entre lesquelles la placer :

1=> valeur 1
insérer la nouvelle ligne "valeur 1b"
2=> valeur 2
3=> valeur 3
4=> valeur 4

Le problème est alors qu'il faut décaler les ordre d'un. Or la table contient environ 7000 valeurs, donc il y a un temps de latence de 20s à chaque insertion, ce qui est très pénible. De plus, je n'ai pas trouvé de requête qui permette d'insérer après l'id n comme on a en python : insert(index)
Le but est d'arriver à :

1=> valeur 1
2=> valeur 1b
3=> valeur 2
4=> valeur 3
5=> valeur 4

Connaissez vous des méthodes autres qu'une boucle sur les 7000 valeurs avec une 7000 requêtes ?
Merci beaucoup
Mon code pour l'instant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
//on récupère toutes les entrées à modifier
$req4 = $bdd->prepare('SELECT * FROM decoupage WHERE ordre>=:ordre');
$req4->execute(array('ordre'=>$ordre_debut));
while ($donnees4 = $req4->fetch())
{
	// on récupère et on indente
	$i=$donnees4['ordre'];
	$j=$donnees4['ordre']+1;
	// on modifie
	$req2 = $bdd->prepare('UPDATE decoupage SET ordre=:ordre WHERE ordre=:ordre2');
	$req2->execute(array('ordre'=>$j, 'ordre2'=>$i));
}

**badaze** · 07/12/2018, 22h24

Comment fais-tu pour déterminer la place du nouvel enregistrement ?

**Léo-Nils** · 07/12/2018, 22h37

C'est une donnée : je connais $ordre juste avant. J'incrémente donc tous ceux au dessus pour faire la place au petit nouveau, puis je l'insère à cet endroit

**badaze** · 07/12/2018, 22h42

Oui mais qu'est-ce qui fait que cet "ordre" a telle ou telle valeur ? Pourquoi insérer en 1B plutôt qu'en 2B ?

**badaze** · 07/12/2018, 23h08

Il faut commencer par modifier l'ordre de la plus grande valeur vers la plus petite afin d'éviter des clés en double si on partait du plus petit vers le plus grand.

Supposons que tu veuilles insérer en 11ème position.

D'abord il faut décaler les enregistrements à partir du 11ème (donc supérieur au 10ème ou supérieur ou égal au 11ème)

Code sql :

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

1
2
3
4
 
update `table` set `nombre`=`nombre`+1 
where nombre > 10
order by nombre desc

ou

Code sql :

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

1
2
3
4
 
update `table` set `nombre`=`nombre`+1 
where nombre >= 11
order by nombre desc

Nom : Capture20181207_001.JPG
Affichages : 249
Taille : 20,2 Ko

Et une fois la modification faite tu insères en 11ème position.

**Léo-Nils** · 07/12/2018, 23h11

L'application décrite ici consite en une liste auquel l'utilisateur peut ajouter des données où il le souhaite. Si par exemple la liste est ce poème

Un éclair... puis la nuit ! - Fugitive beauté (ordre = 0, id=0)
Dont le regard m'a fait soudainement renaître, (ordre = 1, id=1)
Ne te verrai-je plus que dans l'éternité ? (ordre = 2, id=2)
Ailleurs, bien loin d'ici ! trop tard ! jamais peut-être ! (ordre = 3, id=3)
Ô toi que j'eusse aimée, ô toi qui le savais ! (ordre = 4, id=4)
Charles Baudelaire (ordre = 5, id=5)

Mais l'utilisateur a oublié ce vers : "Car j'ignore où tu fuis, tu ne sais où je vais," à l'avant dernière position. Il décide alors de le rajouter, en précisant la position, que je récupère dans $ordre (à savoir ici 3 : juste après "ailleurs..." et alors, il faut rajouter dans la liste le vers, et décaler les suivant

Un éclair... puis la nuit ! - Fugitive beauté (ordre = 0, id=0)
Dont le regard m'a fait soudainement renaître, (ordre = 1, id=1)
Ne te verrai-je plus que dans l'éternité ? (ordre = 2, id=2)
Ailleurs, bien loin d'ici ! trop tard ! jamais peut-être ! (ordre = 3, id=3)
Car j'ignore où tu fuis, tu ne sais où je vais, (ordre = 4, id=6)
Ô toi que j'eusse aimée, ô toi qui le savais ! (ordre = 5, id=4)
Charles Baudelaire (ordre = 6, id=5)

Par ailleurs, j'ai fais une erreur en recopiant a fonction, il faut en effet ne pas modifier tout, donc c'est bien :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
//on récupère toutes les entrées à modifier
$req4 = $bdd->prepare('SELECT * FROM decoupage WHERE ordre>=:ordre');
$req4->execute(array('ordre'=>$ordre_debut));
while ($donnees4 = $req4->fetch())
{
	// on récupère et on indente
	$i=$donnees4['id'];
	$j=$donnees4['ordre']+1;
	// on modifie
	$req2 = $bdd->prepare('UPDATE decoupage SET ordre=:ordre WHERE id=:id');
	$req2->execute(array('ordre'=>$j, 'id'=>$i));
}

Ainsi l'ordre n'est pas modifié de proche en proche mais bien une seule fois.

**Léo-Nils** · 07/12/2018, 23h31

Sinon j'ai aussi un code comme celui

Code :

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

1
2
3
4
5
6
7
8
 
//De la dernière ligne à celle juste après, on descend
for ($i=$count-1; $i>=$ordremax; $i--) { 
	$j = $i + 1;
//on modifie
	$req = $bdd->prepare('UPDATE decoupage SET ordre=:ordre WHERE ordre=:ordreprecedent');
	$req->execute(array('ordre'=>$j, 'ordreprecedent'=>$i));
}

Là, on n'a pas non plus de modification récursive : on utilise i-- décroissant et j=i+1 croissant.

**badaze** · 07/12/2018, 23h39

As tu vu ma réponse ?

**Watilin** · 07/12/2018, 23h40

Pour moi il y a un problème de conception. Théoriquement, un jeu de données dans une table SQL n’est pas ordonné (en pratique il est toujours stocké dans un certain ordre, mais c’est de la mécanique interne et on n’est pas censé le savoir). On ordonne quand on fait un SELECT, et au dernier moment on peut ajouter une variable de « comptage ».
Du coup je ferais comme ça :

on garde si on veut la colonne id, mais on ne s’en sert plus
on ajoute un index (si ce n’est pas déjà le cas) sur la colonne ordre

Code SQL :

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

1
2
3
4
SET @n := 0;
SELECT decoupage.*, @n := @n + 1 AS n
    FROM decoupage 
    ORDER BY ordre;

Dans la pratique, PDO nous oblige en général à faire les requêtes une seule à la fois, donc on peut remplacer la séquence SET – SELECT par un bête JOIN :

Code SQL :

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

1
2
3
SELECT decoupage.*, @n := @n + 1 AS n
    FROM decoupage INNER JOIN (SELECT @n := 0) AS subquery
    ORDER BY ordre;

Une idée piquée à badaze… Ce n’est pas la première fois

Edit : mais je crois que la proposition de badaze dans son post #5 sera plus efficace.

**Léo-Nils** · 07/12/2018, 23h54

Envoyé par badaze

As tu vu ma réponse ?

Oui, je suis désolé, il y a eut un croisement de posts. Merci beaucoup. C'est vrai que j'ai trop tendance à tout faire en php et pas coder grand chose dans mes requêtes... c'est pas bien... J'ai pris la mauvaise habitude d'utiliser uniquement

Code :

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

1
2
3
4
 
$req = $bdd->exec('UPDATE ... WHERE ... ');
$req = $bdd->exec('INSERT INTO... VALUES...);
$req = $bdd->exec('SELECT ... WHERE ... ORDRE BY ...);

J'ai fais un petit comparatif des 2 solutions que je proposais et de celle de badaze : avec 4000 entrées
Exécution du script : 4.2860720157623 secondes. (avec le while en parcourant la table)
Exécution du script : 22.532021999359 secondes. (avec le for entre la dernière case et la case juste après celle à modifier)
Exécution du script : 0.016216039657593 secondes. (avec la méthode de badaze)
Je crois que j'ai choisi ma solution

Par contre savez vous pourquoi celle avec le for donne des résultats si mauvais par rapport au while?
En tout cas merci beaucoup. Je vais essayer d'étudier sql de plus près. J'aurais peu être d'autres parties de mon code qui vont se simplifier grandement.
Bonne soirée.

**badaze** · 08/12/2018, 00h39

Le tout SQL n’est pas toujours le plus rapide. Tout dépend de la complexité de la requête, des index, du nombre d’enregistrements et du nombre de fois où le code est exécuté.

Par exemple si un traitement est lancé une fois par jour qu’il dure 4 ou 30 secondes cela n’a pas grande importance. S’il est lancé des centaines de fois par jour c’est une autre paire de manches.

**badaze** · 08/12/2018, 01h37

Un exemple basique.

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
29
30
31
32
33
34
35
36
37
<?php
//---- DATABASE
define('DB_HOST', '127.0.0.1');
define('DB_NAME', 'tests');
define('DB_USER', 'root');
define('DB_PWD' , '');$pdo    = new PDO('mysql:host='.DB_HOST.'; dbname='.DB_NAME,DB_USER,DB_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
if (isset($_POST['submit']))
{
  // decalage 
  $query  = "update `decoupage` set `ordre`=`ordre`+1 where ordre >= :ordre order by ordre desc ";
  $sth    = $pdo->prepare($query);
 
  $sth->bindParam(':ordre', $_POST['ordre'], PDO::PARAM_INT);
  $sth->execute();  
 
  // insertion
  $query  = "insert into `decoupage` (ordre,texte) values(:ordre,:texte)";
  $sth    = $pdo->prepare($query);
 
  $sth->bindParam(':ordre', $_POST['ordre'], PDO::PARAM_INT);
  $sth->bindParam(':texte', $_POST['texte'], PDO::PARAM_STR); 
  $sth->execute();  
}
 
$query  = "SELECT * FROM decoupage order by ordre ";
$sth    = $pdo->prepare($query);
$sth->execute();  
$result = $sth->fetchAll();
foreach ($result as $subArray) {
 print $subArray['ordre']."-".$subArray['texte']."</br>";
} 
?>
<form method="post" />
Texte <input type="text" name="texte" /><br/>
Ordre <input type="text" name="ordre" />
<input type="submit" name="submit" />
</form>

Nom : Capture20181208_001.JPG
Affichages : 226
Taille : 14,5 Ko

Nom : Capture20181208_002.JPG
Affichages : 208
Taille : 14,8 Ko

Nom : Capture20181208_003.JPG
Affichages : 209
Taille : 15,3 Ko

Nom : Capture20181208_004.JPG
Affichages : 219
Taille : 15,6 Ko

Nom : Capture20181208_005.JPG
Affichages : 212
Taille : 15,7 Ko

**Watilin** · 08/12/2018, 02h18

Envoyé par Léo-Nils

Par contre savez vous pourquoi celle avec le for donne des résultats si mauvais par rapport au while?

Je crois que l’explication la plus probable est celle-ci : tu appelles prepare à l’intérieur de la boucle, la requête est donc re-préparée à chaque itération. Si tu mets l’appel à prepare en-dehors de la boucle, ça sera déjà moins lent.

**Léo-Nils** · 08/12/2018, 10h34

Bonjour
Très bien
Merci beaucoup pour vos réponses
Bonne journée

**CosmoKnacki** · 08/12/2018, 15h45

Une question me taraude: Est-ce que l'utilisateur pouvant faire une insertion est unique? En d'autres termes, peut-on se trouver dans la situation où lorsqu'un utilisateur A procède à une insertion, un utilisateur B a déjà modifié la table (A ayant toujours l'ancienne version de la table)?

D'ailleurs il faudrait considérer le problème plutôt en terme de session unique que d'utilisateur unique.

**badaze** · 08/12/2018, 15h47

Je me suis aussi posé cette question.

SQL : insérer en milieu de table

Langage PHP

Discussions similaires

Partager

Partager