Skip to content

Instantly share code, notes, and snippets.

@proudcommerce
Last active August 2, 2023 12:19
Show Gist options
  • Save proudcommerce/3d4cfc228cc68e35826a to your computer and use it in GitHub Desktop.
Save proudcommerce/3d4cfc228cc68e35826a to your computer and use it in GitHub Desktop.
collection of useful and important mysql snippets for the oxid eshop
/*** ARTIKEL / ARTICLES ***/
/******************************************************/
/* artikelnamen aller artikel anzeigen, welcher einer bestimmten kategorie zugeordnet sind [proudcommerce.com] */
select oxarticles.oxtitle, oxarticles.OXVARSELECT as Variant, oxcategories.OXTITLE as Kategorie from oxarticles left join oxobject2category on oxarticles.OXID = oxobject2category.OXOBJECTID left join oxcategories on oxcategories.OXID = oxobject2category.OXCATNID where oxcategories.OXROOTID = '<YOUR-CATEGORY-OXID>';
/* variantenpreise (oxvarmin, oxvarmax) anhand der aktuellen artikel aktualisieren [foxido.de] */
UPDATE oxarticles B, (SELECT oxparentid,MIN( oxprice ) AS min ,MAX( oxprice ) AS max FROM oxarticles GROUP BY oxparentid) AS A SET oxvarminprice = A.min, oxvarmaxprice = A.max WHERE B.oxid = A.oxparentid
/*** KATEGORIEN / CATEGORIES ***/
/******************************************************/
/* kategorien finden, bei welchen keine seodaten (meta description) vorhanden sind [proudcommerce.com] */
SELECT * FROM oxcategories WHERE oxid NOT IN (SELECT oxobjectid FROM oxobject2seodata);
/* kategorien finden, bei welchen keine bechreibung (oxlongdesc) vorhanden ist [proudcommerce.com] */
SELECT * FROM oxcategories WHERE oxlongdesc = "" OR length(oxlongdesc) <= 10;
/*** HERSTELLER / MANUFACTURER ***/
/******************************************************/
/* hersteller finden, bei welchen keine seodaten (meta description) vorhanden sind [proudcommerce.com] */
SELECT * FROM oxmanufacturers WHERE oxid NOT IN (SELECT oxobjectid FROM oxobject2seodata);
/*** BENUTZER(GRUPPEN) / USER (GROUPS) ***/
/******************************************************/
/* benutzer finden, welche einer bestimmten gruppe zugeordnet sind [foxido.de] */
SELECT * FROM oxuser WHERE oxid IN (SELECT oxobjectid FROM oxobject2group WHERE oxgroupsid = '<YOUR-USERGROUP-OXID>')
/* doppelt genutzte email-adresse finden [proudcommerce.com] */
SELECT oxusername, COUNT(*) FROM oxuser GROUP BY oxusername HAVING COUNT(*) > 1
/*** NEWSLETTER ***/
/******************************************************/
/* aktuelle newsletter empfänger [proudcommerce.com] */
SELECT oxnewssubscribed.oxsal, oxnewssubscribed.oxfname, oxnewssubscribed.oxlname, oxnewssubscribed.oxemail, oxuser.oxcompany FROM oxnewssubscribed, oxuser WHERE OXUNSUBSCRIBED = '0000-00-00 00:00:00' AND oxuser.OXID = oxnewssubscribed.OXUSERID AND oxuser.OXID IN (SELECT OXOBJECTID FROM oxobject2group WHERE OXGROUPSID = 'oxidnewsletter')
@nostadt
Copy link

nostadt commented Nov 28, 2016

Original-Skript von Ackis-Oxid. Dort sind jedoch MySQL-invalide Anführungszeichen angegeben. https://www.ackis-oxid.de/2013/module-kann-nicht-aktiviert-werden-in-oxid-eshop-beheben-modulkonfiguration-zurcksetzen/

Modul-Konfiguration zurücksetzen

delete from oxconfig where oxvarname in (
'aDisabledModules',
'aLegacyModules',
'aModuleFiles',
'aModulePaths',
'aModules',
'aModuleTemplates'
);

@tabsl
Copy link

tabsl commented Dec 25, 2016

datenbanktabellen mit personenbezogenen daten leeren

TRUNCATE TABLE oxaddress;
TRUNCATE TABLE oxnewssubscribed;
TRUNCATE TABLE oxorderarticles;
TRUNCATE TABLE oxorder;
TRUNCATE TABLE oxorderfiles;
TRUNCATE TABLE oxuserbasketitems;
TRUNCATE TABLE oxuserbaskets;
TRUNCATE TABLE oxuserpayments;
DELETE FROM oxuser WHERE oxrights != 'malladmin';

@tabsl
Copy link

tabsl commented Mar 22, 2022

doppelte (älteste) blöcke löschen

DELETE t1 FROM oxtplblocks t1 INNER JOIN oxtplblocks t2 WHERE t1.OXTIMESTAMP < t2.OXTIMESTAMP AND t1.OXBLOCKNAME = t2.OXBLOCKNAME AND t1.OXTEMPLATE = t2.OXTEMPLATE AND t1.OXSHOPID = t2.OXSHOPID AND t1.OXMODULE = t2.OXMODULE AND t1.OXSHOPID = 1 ;

@tabsl
Copy link

tabsl commented Mar 22, 2022

Original-Skript von Ackis-Oxid. Dort sind jedoch MySQL-invalide Anführungszeichen angegeben. https://www.ackis-oxid.de/2013/module-kann-nicht-aktiviert-werden-in-oxid-eshop-beheben-modulkonfiguration-zurcksetzen/

Modul-Konfiguration zurücksetzen

delete from oxconfig where oxvarname in (
'aDisabledModules',
'aLegacyModules',
'aModuleFiles',
'aModulePaths',
'aModules',
'aModuleTemplates'
);

aktuelles SQL

delete from oxconfig where oxvarname in ( 'aDisabledModules', 'aLegacyModules', 'aModuleFiles', 'aModulePaths', 'aModules', 'aModuleTemplates', 'activeModules', 'aModuleEvents', 'aModuleExtensions', 'aModuleVersions', 'aModuleControllers' );

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment