Differences

This shows you the differences between two versions of the page.

--- serveurs:mrscandi-ovh [2021/02/04 16:57] – [mysql] quentin
+++ serveurs:mrscandi-ovh [2025/08/20 09:41] (current) – external edit 127.0.0.1
@@ Line 11: / Line 11: @@
 ==== mysql ====
-[[https://phpmyadmin.cluster028.hosting.ovh.net/index.php?pma_username=vacwdjksql&pma_servername=vacwdjksql.mysql.db]]
+[[https://phpmyadmin.cluster028.hosting.ovh.net/index.php?pma_username=vacwdjkprod&pma_servername=vacwdjkprod.mysql.db]]
-> vacwdjksql.mysql.db
+> vacwdjkprod.mysql.db
-> vacwdjksql
+> vacwdjkprod
 > QTrFPrXJ5YCgQgxB
+===== PREPROD =====
-Requête pour récupérer les promos :
+[[https://preprod.mr-scandinave.fr]]
-<code sql>
+==== mysql ====
-SELECT
+[[https://phpmyadmin.cluster028.hosting.ovh.net/index.php?pma_username=vacwdjkdev&pma_servername=vacwdjkdev.mysql.db]]
-ph.post_id,
+> vacwdjkdev.mysql.db
-MAX(prix) as prix_barre,
+> vacwdjkdev
-pm.meta_value as prix_courant,
+> QTrFPrXJ5YCgQgxB
-ROUND((MAX(prix)-pm.meta_value), 2) as montant_remise,
-ROUND((100 - ((pm.meta_value * 100) / MAX(prix))), 0) as pourcentage_remise
+===== SQL AIDE =====
-FROM (wp_price_history ph, wp_posts p)
-LEFT JOIN wp_postmeta pm ON (pm.post_id = ph.post_id)
+** Afficher tous les produits shopping en brouillon avant le 15-09**
-WHERE 1
+<code `sql`>
-AND p.post_type = 'shopping'
+SELECT * FROM `wp_posts` WHERE `post_status` LIKE 'draft' AND `post_modified` < '2021-09-15 15:09:01' AND `post_type` LIKE 'shopping' ORDER BY `wp_posts`.`post_modified` ASC
-AND pm.meta_key = 'prix'
-AND p.post_status = 'publish'
-AND ph.post_id = p.ID
-AND created > (
-	-- On récupère la date du changement qui a donné le prix d'il y a X jours
-	SELECT created
-	FROM wp_price_history
-	WHERE post_id = ph.post_id
-	AND created < DATE_SUB(NOW(), INTERVAL 10 DAY)
-	LIMIT 1)
-GROUP BY ph.post_id
-HAVING pourcentage_remise > 1  -- uniquement les produits concernés par des promos
-ORDER BY pourcentage_remise DESC;
 </code>
+** Passer en archive tous les produits shopping en brouillon avant le 15-09**
+<code `sql`>UPDATE `wp_posts` SET `post_status` = 'archive' WHERE `post_status` LIKE 'draft' AND `post_modified` < '2021-09-15 15:09:01' AND `post_type` LIKE 'shopping'</code>
+** Afficher tous les produits shopping en brouillon**
+<code `sql`>SELECT * FROM `wp_posts` WHERE `post_status` LIKE 'draft' AND `post_type` LIKE 'shopping'</code>
+** Afficher tous les produits shopping en brouillon contenant le mot matelas**
+<code `sql`>SELECT * FROM `wp_posts` WHERE `post_status` LIKE 'draft' AND `post_type` LIKE 'shopping' AND `post_title` LIKE '%matelas%';</code>
+** Supprimer tous les produits shopping en brouillon contenant le mot matelas**
+<code `sql`>DELETE FROM `wp_posts` WHERE `post_status` LIKE 'draft' AND `post_type` LIKE 'shopping' AND `post_title` LIKE '%matelas%';</code>
+** Afficher les produits les + cliqués **
+<code `sql`>SELECT p.ID, p.guid, p.post_title, meta_value as nb_clics
+FROM `wp_postmeta` pm, wp_posts p
+WHERE meta_key = 'nb_clics'
+AND pm.post_id = p.ID
+ORDER BY CAST(meta_value AS DECIMAL) DESC
+LIMIT 200;</code>