Bloqué avec une requête de remplacement de chaîne de caractères

Bonjour,

Je travaille en local avec Wampserver 3.1.0 et MySQL en 5.7.19. J'ai créé plusieurs bases avec Joomla 3.8.2. Dans une des tables de ces bases, j'aurai besoin de changer une chaîne de caractères pour centrer des images dans les articles. J'ai fait diverses recherches pour la syntaxe à utiliser, mais je suis à chaque fois confronté à une erreur

Nom de la table : qqk0j_content

Nom du champ : introtext

Texte à remplacer : <p><img src=

Texte de remplacement : <p><img style="display: block; margin-left: auto; margin-right: auto;" src=

Voici ma requête

Code:

---

UPDATE qqk0j_content SET introtext=REPLACE(introtext,'<p><img src=','<p><img style="display: block; margin-left: auto; margin-right: auto;" src=')

---

J'ai cette erreur qui sort quand je simule la requête :

Code:

---

#1064 - Erreur de syntaxe près de ''<p><img style="display: block)' à la ligne 1

---

Ma requête est-elle correcte ? ou bien il y a t'il des caractères interdits dans les chaînes de remplacement ?

Je vous remercie de votre aide

Bernard

Essaie en échappant les guillemets :

Code:

---

UPDATE qqk0j_content SET introtext=REPLACE(introtext,'<p><img src=','<p><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=')

---

Bonsoir Philippe,

Je viens d'essayer cette syntaxe, elle bloque avec la même erreur #1064

Bonne soirée

Bernard

Test personnel sur une table bidon chez moi via phpMyAdmin :

Code:

---

INSERT INTO `test_trous` (`num`, `txt`) VALUES (NULL, '<p><img src=\"http://uneimage.com/image1.jpg\" />'), (NULL, '<p><img src=\"http://uneimage.com/image2.png\" />');

---

Nota : dans phpMyAdmin, j'ai tapé les guillemets sans les échapper et c'est phpMyAdmin qui a fait le boulot.

Adaptation de ma requête :

Code:

---

UPDATE test_trous SET txt=REPLACE(txt,'<p><img src=','<p><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=')

---

=> 2 lignes affectées. (traité en 0.0406 seconde(s).)

Résultat :
<p><img style="display: block; margin-left: auto; margin-right: auto;" src="http://uneimage.com/image1.jpg" />
<p><img style="display: block; margin-left: auto; margin-right: auto;" src="http://uneimage.com/image2.png" />

Je ne vois pas quel est le souci chez vous.

Donnez-nous un jeu de données exemples pour qu'on analyse ça de plus près.

Bonjour Michel,


Je vous remercie pour la poursuite de votre aide, je viens d'extraire une ligne de cette table que je mets en pièce jointe.

Bernard

Salut Manard82.

J'ai récupéré votre exemple, que j'ai testé chez moi, avec votre requête de mise à jour et je n'ai trouvé aucune difficulté.

Code:

---

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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136

-------------- SET AUTOCOMMIT = 0 --------------   -------------- START TRANSACTION --------------   -------------- set sql_mode ='' --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS `qqk0j_content` --------------   -------------- CREATE TABLE IF NOT EXISTS `qqk0j_content` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `asset_id` int(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'FK to the #__assets table.', `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', `introtext` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `fulltext` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `state` tinyint(3) NOT NULL DEFAULT '0', `catid` int(10) UNSIGNED NOT NULL DEFAULT '0', `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `created_by` int(10) UNSIGNED NOT NULL DEFAULT '0', `created_by_alias` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `modified_by` int(10) UNSIGNED NOT NULL DEFAULT '0', `checked_out` int(10) UNSIGNED NOT NULL DEFAULT '0', `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `images` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `urls` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `attribs` varchar(5120) COLLATE utf8mb4_unicode_ci NOT NULL, `version` int(10) UNSIGNED NOT NULL DEFAULT '1', `ordering` int(11) NOT NULL DEFAULT '0', `metakey` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `metadesc` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `access` int(10) UNSIGNED NOT NULL DEFAULT '0', `hits` int(10) UNSIGNED NOT NULL DEFAULT '0', `metadata` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `featured` tinyint(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Set if article is featured.', `language` char(7) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The language code for the article.', `xreference` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx_access` (`access`), KEY `idx_checkout` (`checked_out`), KEY `idx_state` (`state`), KEY `idx_catid` (`catid`), KEY `idx_createdby` (`created_by`), KEY `idx_featured_catid` (`featured`,`catid`), KEY `idx_language` (`language`), KEY `idx_xreference` (`xreference`), KEY `idx_alias` (`alias`(191)) ) ENGINE=InnoDB AUTO_INCREMENT=1131 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci --------------   -------------- INSERT INTO `qqk0j_content` (`id`, `asset_id`, `title`, `alias`, `introtext`, `fulltext`, `state`, `catid`, `created`, `created_by`, `created_by_alias`, `modified`, `modified_by`, `checked_out`, `checked_out_time`, `publish_up`, `publish_down`, `images`, `urls`, `attribs`, `version`, `ordering`, `metakey`, `metadesc`, `access`, `hits`, `metadata`, `featured`, `language`, `xreference`) VALUES (1057, 1157, 'GR-Agadir Aguerd', 'gr-agadir-aguerd', '<h2>Situation</h2>\r\n<p><img src=\"images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-1.jpg\" alt=\"\" /></p>\r\n<p><img src=\"images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-2.jpg\" alt=\"\" /></p>\r\n<p><img src=\"images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-3.jpg\" alt=\"\" /></p>\r\n<p><img src=\"images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-4.jpg\" alt=\"\" /></p>\r\n<p><img src=\"images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-5.jpg\" alt=\"\" /></p>\r\n<h4>Site indiqué par Abdehadi Ewague, sur l\'emplacement de l\'agadir éponyme</h4>', '', 1, 30, '2017-02-03 10:16:24', 55, '', '2017-12-02 13:34:52', 55, 0, '0000-00-00 00:00:00', '2017-02-03 10:16:24', '0000-00-00 00:00:00', '{\"image_intro\":\"\",\"float_intro\":\"\",\"image_intro_alt\":\"\",\"image_intro_caption\":\"\",\"image_fulltext\":\"\",\"float_fulltext\":\"\",\"image_fulltext_alt\":\"\",\"image_fulltext_caption\":\"\"}', '{\ "urla\":false,\"urlatext\":\"\",\"targeta\":\"\",\"urlb\":false,\"urlbtext\":\"\",\"targetb\":\"\",\"urlc\":false,\"urlctext\":\"\",\"targetc\":\"\"}', '{\"article_layout\":\"\",\"show_title\":\"\",\"link_titles\":\"\",\"show_tags\":\"\",\"show_intro\":\"\",\"info_block_position\":\"\",\"info_block_show_title\":\"\",\"show_category\":\"\",\"link_category\":\"\",\"show_parent_category\":\"\",\"link_parent_category\":\"\",\"show_associations\":\"\",\"show_author\":\"\",\"link_author\":\"\",\"show_create_date\":\"\",\"show_modify_date\":\"\",\"show_publish_date\":\"\",\"show_item_navigation\":\"\",\"show_icons\":\"\",\"show_print_icon\":\"\",\"show_email_icon\":\"\",\"show_vote\":\"\",\"show_hits\":\"\",\"show_noauth\":\"\",\"urls_position\":\"\",\"alternative_readmore\":\"\",\"article_page_title\":\"\",\"show_publishing_options\":\"\",\"show_article_options\":\"\",\"show_urls_images_backend\":\"\",\"show_urls_images_frontend\":\"\"}', 2, 7, '', '', 2, 0, '{\"robots\":\"\",\"author\":\"\ ",\"rights\":\"\",\"xreference\":\"\"}', 0, '*', '') --------------   -------------- select * from `qqk0j_content` --------------   +------+----------+------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+-------+---------------------+------------+------------------+---------------------+-------------+-------------+---------------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------- ----------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------+---------+----------+--------+------+-------------------------------------------------------+----------+----------+------------+ | id | asset_id | title | alias | introtext | fulltext | state | catid | created | created_by | created_by_alias | modified | modified_by | checked_out | checked_out_time | publish_up | publish_down | images | urls | attribs | version | ordering | metakey | metadesc | access | hits | metadata | featured | language | xreference | +------+----------+------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+-------+---------------------+------------+------------------+---------------------+-------------+-------------+---------------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------- ----------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------+---------+----------+--------+------+-------------------------------------------------------+----------+----------+------------+ | 1057 | 1157 | GR-Agadir Aguerd | gr-agadir-aguerd | <h2>Situation</h2> <p><img src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-1.jpg" alt="" /></p> <p><img src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-2.jpg" alt="" /></p> <p><img src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-3.jpg" alt="" /></p> <p><img src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-4.jpg" alt="" /></p> <p><img src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-5.jpg" alt="" /></p> <h4>Site indiqué par Abdehadi Ewague, sur l'emplacement de l'agadir éponyme</h4> | | 1 | 30 | 2017-02-03 10:16:24 | 55 | | 2017-12-02 13:34:52 | 55 | 0 | 0000-00-00 00:00:00 | 2017-02-03 10:16:24 | 0000-00-00 00:00:00 | {"image_intro":"","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""} | {"urla":false,"urlatext":"","targeta":"","urlb":false,"urlbtext":"","targetb":"","urlc":false,"urlctext":"","targetc":""} | {"article_layout":"","show_title":"","link_titles":"","show_tags":"","show_intro":"","info_block_position":"","info_block_show_title":"","show_category":"","link_category":"","show_parent_category":"","link_parent_category":"","show_associations":"","show_author":"","link_author":"","show_create_date":"","show_modify_date":"","show_publish_date":"","show_item_navigation":"","show_icons":"","show_print_icon":"","show _email_icon":"","show_vote":"","show_hits":"","show_noauth":"","urls_position":"","alternative_readmore":"","article_page_title":"","show_publishing_options":"","show_article_options":"","show_urls_images_backend":"","show_urls_images_frontend":""} | 2 | 7 | | | 2 | 0 | {"robots":"","author":"","rights":"","xreference":""} | 0 | * | | +------+----------+------------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+-------+---------------------+------------+------------------+---------------------+-------------+-------------+---------------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------- ----------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+----------+---------+----------+--------+------+-------------------------------------------------------+----------+----------+------------+ -------------- UPDATE `qqk0j_content` SET introtext = REPLACE(introtext,'<p><img src=','<p><img style="display: block; margin-left: auto; margin-right: auto;" src=') --------------   -------------- select * from `qqk0j_content` --------------   +------+----------+------------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+-------+---------------------+------------+------- -----------+---------------------+-------------+-------------+---------------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------+---------+----------+---------+----------+--------+------+-------------------------------------------------------+----------+----------+------------+ | id | asset_id | title | alias | introtext | fulltext | state | catid | created | created_by | create d_by_alias | modified | modified_by | checked_out | checked_out_time | publish_up | publish_down | images | urls | attribs | version | ordering | metakey | metadesc | access | hits | metadata | featured | language | xreference | +------+----------+------------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+-------+---------------------+------------+------- -----------+---------------------+-------------+-------------+---------------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------+---------+----------+---------+----------+--------+------+-------------------------------------------------------+----------+----------+------------+ | 1057 | 1157 | GR-Agadir Aguerd | gr-agadir-aguerd | <h2>Situation</h2> <p><img style="display: block; margin-left: auto; margin-right: auto;" src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-1.jpg" alt="" /></p> <p><img style="display: block; margin-left: auto; margin-right: auto;" src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-2.jpg" alt="" /></p> <p><img style="display: block; margin-left: auto; margin-right: auto;" src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-3.jpg" alt="" /></p> <p><img style="display: block; margin-left: auto; margin-right: auto;" src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-4.jpg" alt="" /></p> <p><img style="display: block; margin-left: auto; margin-right: auto;" src="images/Photos/Prehistoire/Aguerd/Carte-GR-Agadir-Aguerd-5.jpg" alt="" /></p> <h4>Site indiqué par Abdehadi Ewague, sur l'emplacement de l'agadir éponyme</h4> | | 1 | 30 | 2017-02-03 10:16:24 | 55 | | 2017-12-02 13:34:52 | 55 | 0 | 0000-00-00 00:00:00 | 2017-02-03 10:16:24 | 0000-00-00 00:00:00 | {"image_intro":"","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""} | {"urla":false,"urlatext":"","targeta":"","urlb":false,"urlbtext":"","targetb":"","urlc":false,"urlctext":"","targetc":""} | {"article_layout":"","show_title":"","link_titles":"","show_tags":"","show_intro":"","info_block_position":"","info_block_show_title":"","show_category":"","link_category":"","show_parent_category":"","link_parent_category":"","show_associations":"","show_author":"","link_author":"","show_create_date":"","show_modify_date":"","show_publish_date":"","show_item_navigation":"","show_icons":"","show_print_icon":"","show _email_icon":"","show_vote":"","show_hits":"","show_noauth":"","urls_position":"","alternative_readmore":"","article_page_title":"","show_publishing_options":"","show_article_options":"","show_urls_images_backend":"","show_urls_images_frontend":""} | 2 | 7 | | | 2 | 0 | {"robots":"","author":"","rights":"","xreference":""} | 0 | * | | +------+----------+------------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+-------+---------------------+------------+------- -----------+---------------------+-------------+-------------+---------------------+---------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------+---------+----------+---------+----------+--------+------+-------------------------------------------------------+----------+----------+------------+ -------------- COMMIT --------------   -------------- SET AUTOCOMMIT = 1 --------------   Appuyez sur une touche pour continuer...

---

Il n'est même pas nécessaire d'utiliser le caractère d'échappement car il n'y a aucun confusion entre le guillemet et l'apostrophe dans votre requête.

Votre requête est correcte !

@+

Bonjour Artemus24,

Je vous remercie pour votre test, moi j'ai lancé cette requête SQL depuis phpMyAdmin, est-ce lui qui bloquerait sur le terme "block" ou la syntaxe autour ?

Bernard

Salut Manard82.

Je préfère de loin utiliser des scripts MySql plutôt que de passer par PhpMyAdmi, car avec un script, je conserve la requête que je fais, tandis qu'avec PhpMyAdmin, je suis obligé de tout retaper à chaque fois.
J'ai fait aussi le test sous PhpMyAdmin et voici le résultat de l'exécution de la requête :

http://i63.tinypic.com/14izhgn.png

Je pense que votre problème peut éventuellement venir de votre PhpMyAdmin, mais j'ai quand même un doute.
Mon test a été fait dans la version 4.7.6, la dernière version de PhpMyAdmin.

@+

Bonjour,

Je viens de faire la même opération que vous, effectivement en lançant la requête sans la simuler elle passe, mais quand je fais "Simuler la requête" j'ai cette erreur, je n'étais pas allé plus loin pensant que si la simulation bloquait, il en serait de même de l'exécution

J'ai fait l'opération sur la base originale, effectivement elle passe. Donc peut-être un bug dans la simulation ? Ma version de phpMyAdmin est 4.7.4

Bernard

Salut Manard82.

Citation:

---

Envoyé par Manard82

J'ai cette erreur qui sort quand je simule la requête :

---

Qu'est-ce que vous entendez par "simuler" ?
Je voie que vous utilisez à plusieurs reprises ce terme.

@+

Bonsoir,

Quand je mets la requête dans la fenêtre, en bas à droite est proposé un bouton "Simuler la requête"

Pièce jointe 335472

Bonne soirée et encore merci pour vos essais

Bernard

Salut Manard82.

Ce bouton "simuler la requête", je l'obtiens que si je saisie la requête "update".
Si je mets à la place "select", le bouton n'apparait pas.
Du coup, j'ai cliqué dessus, et j'ai bien obtenu la même erreur que vous :

Code:

---

#1064 - Erreur de syntaxe près de ''<p><img style="display: block)' à la ligne 1

---

Je ne comprends pas pourquoi il y a une erreur.
Tandis que si vous cliquez sur le bouton "exécuter", la requête fonctionne correctement.

Merci pour l'image ! :)

@+