How we temporarily handle the right to data portability (Art. 20 GDPR)

We’ve experienced requests concerning the “Right to data portability” (Art. 20 GDPR) on several channels. The legal text states

… receive the personal data … in a structured, commonly used and machine-readable format…

which is not clearly specified, is it? Until clear specification, we tread the path of providing an SQL script for reading out relevant information from the database (see below).

Please note that this script was tested in OXID eShop v6.0.2 any edition. It is licensed under GPLv3, so please feel free to port it to other OXID eShop versions (and let the community participate in your derived work).

For the usage of the provided SQL script you’ll need the database name, the database user name and the password as well as the UserID which can be found in Admin -> Administer users -> Users. Hover your mouse cursor above the specific user name and find his UserID in the link (button left corner).

Find out oxID of a user

Usage

Copy this script into a file of your choosing, name it something like gdpr_query_collection.sql.

In line 1 of the script, you’ll find the following:

set @USERID='[INSERT USERID HERE]';

Please replace [INSERT USERID HERE] with the real customer number you found in your admin panel (see screenshot above).

Now you can go through the script and adjust it for your needs: some fields are skipped for the sake of readability – of course you can add whatever you want or whatever is possible.

Please connect to the terminal of your server via SSH and run the script on the shell, for example like this (replace everything in brackets with your data):

mysql --batch -u [database user] -p[database password] [database name] < gdpr_query_collection.sql > output.txt

If you’d rather have semicolons instead of tabs as field separators, run the following:

mysql --batch -u [database user] -p[database password] [database name] < gdpr_query_collection.sql | sed 's/\t/;/g' > output.csv

Data to be exported:

  • data from oxuser
  • newsletter status
  • additional delivery addresses
  • order data
  • wishlist data
  • gift registry data
  • listmania data
  • download orders
  • basket data
  • ratings
  • reviews
  • invitations
  • price alerts
  • vouchers used
  • payments performed
  • agreed to terms and services (private sales only)

Please note:

  • Product Comparison is stored in the user’s browser session and never to the database.
  • Product Recommendations are directly submitted via e-mail and will never be stored to the database.

Script:

set @USERID='[INSERT USERID HERE]';
/*
Data from oxuser
We are skipping (add them if needed):
`OXID`, `OXRIGHTS`, `OXPASSWORD`, `OXPASSSALT`
*/
SELECT `OXACTIVE`, `OXSHOPID`, IFNULL((SELECT `OXNAME` FROM `oxshops` WHERE `OXID`=`OXSHOPID`), '') AS OXSHOPNAME, `OXUSERNAME`, `OXCUSTNR`, `OXUSTID`, `OXCOMPANY`, `OXFNAME`, `OXLNAME`, `OXSTREET`, `OXSTREETNR`, `OXADDINFO`, `OXCITY`, `OXCOUNTRYID`, IFNULL((SELECT `OXTITLE` FROM `oxcountry` WHERE `OXID`=`OXCOUNTRYID`), '') AS OXCOUNTRYNAME, `OXSTATEID`, IFNULL((SELECT `OXTITLE` FROM `oxstates` WHERE `OXID`=`OXSTATEID`), '') AS OXSTATENAME, `OXZIP`, `OXFON`, `OXFAX`, `OXSAL`, `OXBONI`, `OXCREATE`, `OXREGISTER`, `OXPRIVFON`, `OXMOBFON`, `OXBIRTHDATE`, `OXURL`, `OXUPDATEKEY`, `OXUPDATEEXP`, `OXPOINTS`, `OXTIMESTAMP` FROM `oxuser` WHERE `OXID`[email protected];
SELECT '' AS ''; /* Insert two line breaks */
/* Newsletter status */
SELECT IF(`OXDBOPTIN`>0,'Subscribed','Unsubscribed') AS OXNEWSLETTERSTATUS FROM `oxnewssubscribed` WHERE `OXUSERID`[email protected];
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored additional delivery addresses for this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`, `OXADDRESSUSERID`,
*/
SELECT `OXCOMPANY`, `OXFNAME`, `OXLNAME`, `OXSTREET`, `OXSTREETNR`, `OXADDINFO`, `OXCITY`, `OXCOUNTRY`, `OXCOUNTRYID`, `OXSTATEID`, IFNULL((SELECT `OXTITLE` FROM `oxstates` WHERE `OXID`=`OXSTATEID`), '') AS OXSTATENAME, `OXZIP`, `OXFON`, `OXFAX`, `OXSAL`, `OXTIMESTAMP` FROM `oxaddress` WHERE `OXUSERID`[email protected] ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored order data for this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`, `OXPAYMENTID`
We are aggregating the articles of an order in OXARTICLESINORDER with separators as follows:
% to separate one article from another
~ to separate the article fields from one another
In this aggregate we are skipping from the table oxorderarticles(add them if needed):
`OXID`, `OXORDERID`, `OXARTID`, `OXSHORTDESC`, `OXSELVARIANT`, `OXNETPRICE`, `OXVATPRICE`, `OXVAT`, `OXPERSPARAM`, `OXPRICE`, `OXBPRICE`, `OXNPRICE`, `OXWRAPID`, `OXEXTURL`, `OXURLDESC`, `OXURLIMG`, `OXTHUMB`, `OXPIC1`, `OXPIC2`, `OXPIC3`, `OXPIC4`, `OXPIC5`, `OXWEIGHT`, `OXSTOCK`, `OXDELIVERY`, `OXINSERT`, `OXTIMESTAMP`, `OXLENGTH`, `OXWIDTH`, `OXHEIGHT`, `OXFILE`, `OXSEARCHKEYS`, `OXTEMPLATE`, `OXQUESTIONEMAIL`, `OXISSEARCH`, `OXFOLDER`, `OXSUBCLASS`, `OXSTORNO`, `OXORDERSHOPID`, `OXISBUNDLE`
*/
SELECT `OXSHOPID`, IFNULL((SELECT `OXNAME` FROM `oxshops` WHERE `OXID`=`OXSHOPID`), '') AS OXSHOPNAME,  `OXORDERDATE`, `OXORDERNR`, `OXBILLCOMPANY`, `OXBILLEMAIL`, `OXBILLFNAME`, `OXBILLLNAME`, `OXBILLSTREET`, `OXBILLSTREETNR`, `OXBILLADDINFO`, `OXBILLUSTID`, `OXBILLCITY`, `OXBILLCOUNTRYID`, IFNULL((SELECT `OXTITLE` FROM `oxcountry` WHERE `OXID`=`OXBILLCOUNTRYID`), '') AS OXBILLCOUNTRYNAME, `OXBILLSTATEID`, IFNULL((SELECT `OXTITLE` FROM `oxstates` WHERE `OXID`=`OXBILLSTATEID`), '') AS OXBILLSTATENAME, `OXBILLZIP`, `OXBILLFON`, `OXBILLFAX`, `OXBILLSAL`, `OXDELCOMPANY`, `OXDELFNAME`, `OXDELLNAME`, `OXDELSTREET`, `OXDELSTREETNR`, `OXDELADDINFO`, `OXDELCITY`, `OXDELCOUNTRYID`, IFNULL((SELECT `OXTITLE` FROM `oxcountry` WHERE `OXID`=`OXDELCOUNTRYID`), '') AS OXDELCOUNTRYNAME, `OXDELSTATEID`, IFNULL((SELECT `OXTITLE` FROM `oxstates` WHERE `OXID`=`OXDELSTATEID`), '') AS OXDELSTATENAME, `OXDELZIP`, `OXDELFON`, `OXDELFAX`, `OXDELSAL`, `OXPAYMENTTYPE`, `OXTOTALNETSUM`, `OXTOTALBRUTSUM`, `OXTOTALORDERSUM`, `OXARTVAT1`, `OXARTVATPRICE1`, `OXARTVAT2`, `OXARTVATPRICE2`, `OXDELCOST`, `OXDELVAT`, `OXPAYCOST`, `OXPAYVAT`, `OXWRAPCOST`, `OXWRAPVAT`, `OXGIFTCARDCOST`, `OXGIFTCARDVAT`, `OXCARDID`, `OXCARDTEXT`, `OXDISCOUNT`, `OXEXPORT`, `OXBILLNR`, `OXBILLDATE`, `OXTRACKCODE`, `OXSENDDATE`, `OXREMARK`, `OXVOUCHERDISCOUNT`, `OXCURRENCY`, `OXCURRATE`, `OXFOLDER`, `OXTRANSID`, `OXPAYID`, `OXXID`, `OXPAID`, `OXSTORNO`, `OXIP`, `OXTRANSSTATUS`, `OXLANG`, `OXINVOICENR`, `OXDELTYPE`, `OXTIMESTAMP`, `OXISNETTOMODE`, (SELECT GROUP_CONCAT(CONCAT(`OXARTNUM`,'~',`OXTITLE`,'~',`OXAMOUNT`,'~',`OXBRUTPRICE`) SEPARATOR '%') FROM `oxorderarticles` WHERE `OXORDERID`=`oxorder`.`OXID`) AS OXARTICLESINORDER FROM `oxorder` WHERE `OXUSERID`[email protected] ORDER BY `OXORDERDATE`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored wishlist data for this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`, `OXTITLE`
We are aggregating the articles of the wishlist in OXARTICLESINWISHLIST from two tables with separators as follows:
% to separate one article from another
~ to separate the article fields from one another
From the table oxuserbasketitems we are skipping (add them if needed):
`OXID`, `OXBASKETID`, `OXARTID`, `OXSELLIST`, `OXPERSPARAM`
From the table oxarticles we are skipping (add them if needed):
`OXID`, `OXSHOPID`, `OXPARENTID`, `OXACTIVE`, `OXHIDDEN`, `OXACTIVEFROM`, `OXACTIVETO`, `OXEAN`, `OXDISTEAN`, `OXMPN`, `OXSHORTDESC`, `OXPRICE`, `OXBLFIXEDPRICE`, `OXPRICEA`, `OXPRICEB`, `OXPRICEC`, `OXBPRICE`, `OXTPRICE`, `OXUNITNAME`, `OXUNITQUANTITY`, `OXEXTURL`, `OXURLDESC`, `OXURLIMG`, `OXVAT`, `OXTHUMB`, `OXICON`, `OXPIC1`, `OXPIC2`, `OXPIC3`, `OXPIC4`, `OXPIC5`, `OXPIC6`, `OXPIC7`, `OXPIC8`, `OXPIC9`, `OXPIC10`, `OXPIC11`, `OXPIC12`, `OXWEIGHT`, `OXSTOCK`, `OXSTOCKFLAG`, `OXSTOCKTEXT`, `OXNOSTOCKTEXT`, `OXDELIVERY`, `OXINSERT`, `OXTIMESTAMP`, `OXLENGTH`, `OXWIDTH`, `OXHEIGHT`, `OXFILE`, `OXSEARCHKEYS`, `OXTEMPLATE`, `OXQUESTIONEMAIL`, `OXISSEARCH`, `OXISCONFIGURABLE`, `OXVARNAME`, `OXVARSTOCK`, `OXVARCOUNT`, `OXVARSELECT`, `OXVARMINPRICE`, `OXVARMAXPRICE`, `OXVARNAME_1`, `OXVARSELECT_1`, `OXVARNAME_2`, `OXVARSELECT_2`, `OXVARNAME_3`, `OXVARSELECT_3`, `OXTITLE_1`, `OXSHORTDESC_1`, `OXURLDESC_1`, `OXSEARCHKEYS_1`, `OXTITLE_2`, `OXSHORTDESC_2`, `OXURLDESC_2`, `OXSEARCHKEYS_2`, `OXTITLE_3`, `OXSHORTDESC_3`, `OXURLDESC_3`, `OXSEARCHKEYS_3`, `OXBUNDLEID`, `OXFOLDER`, `OXSUBCLASS`, `OXSTOCKTEXT_1`, `OXSTOCKTEXT_2`, `OXSTOCKTEXT_3`, `OXNOSTOCKTEXT_1`, `OXNOSTOCKTEXT_2`, `OXNOSTOCKTEXT_3`, `OXSORT`, `OXSOLDAMOUNT`, `OXNONMATERIAL`, `OXFREESHIPPING`, `OXREMINDACTIVE`, `OXREMINDAMOUNT`, `OXAMITEMID`, `OXAMTASKID`, `OXVENDORID`, `OXMANUFACTURERID`, `OXSKIPDISCOUNTS`, `OXRATING`, `OXRATINGCNT`, `OXMINDELTIME`, `OXMAXDELTIME`, `OXDELTIMEUNIT`, `OXUPDATEPRICE`, `OXUPDATEPRICEA`, `OXUPDATEPRICEB`, `OXUPDATEPRICEC`, `OXUPDATEPRICETIME`, `OXISDOWNLOADABLE`, `OXSHOWCUSTOMAGREEMENT`
*/
SELECT `OXTIMESTAMP`, `OXPUBLIC`, `OXUPDATE`, (SELECT GROUP_CONCAT(CONCAT((SELECT CONCAT(`OXARTNUM`,'~',`OXTITLE`,'~',`OXPRICE`) FROM `oxarticles` WHERE `oxarticles`.`OXID`=`OXARTID`),'~',`OXAMOUNT`,'~',`OXTIMESTAMP`) SEPARATOR '%') FROM `oxuserbasketitems` WHERE `oxuserbaskets`.`OXID`=`OXBASKETID`) AS OXARTICLESINWISHLIST FROM `oxuserbaskets` WHERE `OXUSERID`[email protected] AND `OXTITLE`='noticelist' ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/* Gift registry how? */
/*
Stored gift registry data for this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`, `OXTITLE`
We are aggregating the articles of the gift registry in OXARTICLESINREGISTRY from two tables with separators as follows:
% to separate one article from another
~ to separate the article fields from one another
From the table oxuserbasketitems we are skipping (add them if needed):
`OXID`, `OXBASKETID`, `OXARTID`, `OXSELLIST`, `OXPERSPARAM`
From the table oxarticles we are skipping (add them if needed):
`OXID`, `OXSHOPID`, `OXPARENTID`, `OXACTIVE`, `OXHIDDEN`, `OXACTIVEFROM`, `OXACTIVETO`, `OXEAN`, `OXDISTEAN`, `OXMPN`, `OXSHORTDESC`, `OXPRICE`, `OXBLFIXEDPRICE`, `OXPRICEA`, `OXPRICEB`, `OXPRICEC`, `OXBPRICE`, `OXTPRICE`, `OXUNITNAME`, `OXUNITQUANTITY`, `OXEXTURL`, `OXURLDESC`, `OXURLIMG`, `OXVAT`, `OXTHUMB`, `OXICON`, `OXPIC1`, `OXPIC2`, `OXPIC3`, `OXPIC4`, `OXPIC5`, `OXPIC6`, `OXPIC7`, `OXPIC8`, `OXPIC9`, `OXPIC10`, `OXPIC11`, `OXPIC12`, `OXWEIGHT`, `OXSTOCK`, `OXSTOCKFLAG`, `OXSTOCKTEXT`, `OXNOSTOCKTEXT`, `OXDELIVERY`, `OXINSERT`, `OXTIMESTAMP`, `OXLENGTH`, `OXWIDTH`, `OXHEIGHT`, `OXFILE`, `OXSEARCHKEYS`, `OXTEMPLATE`, `OXQUESTIONEMAIL`, `OXISSEARCH`, `OXISCONFIGURABLE`, `OXVARNAME`, `OXVARSTOCK`, `OXVARCOUNT`, `OXVARSELECT`, `OXVARMINPRICE`, `OXVARMAXPRICE`, `OXVARNAME_1`, `OXVARSELECT_1`, `OXVARNAME_2`, `OXVARSELECT_2`, `OXVARNAME_3`, `OXVARSELECT_3`, `OXTITLE_1`, `OXSHORTDESC_1`, `OXURLDESC_1`, `OXSEARCHKEYS_1`, `OXTITLE_2`, `OXSHORTDESC_2`, `OXURLDESC_2`, `OXSEARCHKEYS_2`, `OXTITLE_3`, `OXSHORTDESC_3`, `OXURLDESC_3`, `OXSEARCHKEYS_3`, `OXBUNDLEID`, `OXFOLDER`, `OXSUBCLASS`, `OXSTOCKTEXT_1`, `OXSTOCKTEXT_2`, `OXSTOCKTEXT_3`, `OXNOSTOCKTEXT_1`, `OXNOSTOCKTEXT_2`, `OXNOSTOCKTEXT_3`, `OXSORT`, `OXSOLDAMOUNT`, `OXNONMATERIAL`, `OXFREESHIPPING`, `OXREMINDACTIVE`, `OXREMINDAMOUNT`, `OXAMITEMID`, `OXAMTASKID`, `OXVENDORID`, `OXMANUFACTURERID`, `OXSKIPDISCOUNTS`, `OXRATING`, `OXRATINGCNT`, `OXMINDELTIME`, `OXMAXDELTIME`, `OXDELTIMEUNIT`, `OXUPDATEPRICE`, `OXUPDATEPRICEA`, `OXUPDATEPRICEB`, `OXUPDATEPRICEC`, `OXUPDATEPRICETIME`, `OXISDOWNLOADABLE`, `OXSHOWCUSTOMAGREEMENT`
*/
SELECT `OXTIMESTAMP`, `OXPUBLIC`, `OXUPDATE`, (SELECT GROUP_CONCAT(CONCAT((SELECT CONCAT(`OXARTNUM`,'~',`OXTITLE`,'~',`OXPRICE`) FROM `oxarticles` WHERE `oxarticles`.`OXID`=`OXARTID`),'~',`OXAMOUNT`,'~',`OXTIMESTAMP`) SEPARATOR '%') FROM `oxuserbasketitems` WHERE `oxuserbaskets`.`OXID`=`OXBASKETID`) AS OXARTICLESINREGISTRY FROM `oxuserbaskets` WHERE `OXUSERID`[email protected] AND `OXTITLE`='wishlist' ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored listmania data for this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`
*/
SELECT `OXSHOPID`, IFNULL((SELECT `OXNAME` FROM `oxshops` WHERE `OXID`=`OXSHOPID`), '') AS OXSHOPNAME, `OXAUTHOR`, `OXTITLE`, `OXDESC`, `OXRATINGCNT`, `OXRATING`, `OXTIMESTAMP` FROM `oxrecommlists` WHERE `OXUSERID`[email protected] ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored download orders for this user
We are skipping (add them if needed):
`OXID`, `OXORDERID`, `OXFILEID`, `OXORDERARTICLEID`
*/
SELECT `OXFILENAME`, `OXSHOPID`, IFNULL((SELECT `OXNAME` FROM `oxshops` WHERE `OXID`=`OXSHOPID`), '') AS OXSHOPNAME, `OXFIRSTDOWNLOAD`, `OXLASTDOWNLOAD`, `OXDOWNLOADCOUNT`, `OXMAXDOWNLOADCOUNT`, `OXDOWNLOADEXPIRATIONTIME`, `OXLINKEXPIRATIONTIME`, `OXRESETCOUNT`, `OXVALIDUNTIL`, `OXTIMESTAMP` FROM `oxorderfiles` WHERE `OXORDERID` IN (SELECT `OXORDERID` FROM `oxorder` WHERE `OXUSERID`[email protected]) ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored basket data for this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`, `OXTITLE`
We are aggregating the articles of the basket in OXARTICLESINBASKET from two tables with separators as follows:
% to separate one article from another
~ to separate the article fields from one another
From the table oxuserbasketitems we are skipping (add them if needed):
`OXID`, `OXBASKETID`, `OXARTID`, `OXSELLIST`, `OXPERSPARAM`
From the table oxarticles we are skipping (add them if needed):
`OXID`, `OXSHOPID`, `OXPARENTID`, `OXACTIVE`, `OXHIDDEN`, `OXACTIVEFROM`, `OXACTIVETO`, `OXEAN`, `OXDISTEAN`, `OXMPN`, `OXSHORTDESC`, `OXPRICE`, `OXBLFIXEDPRICE`, `OXPRICEA`, `OXPRICEB`, `OXPRICEC`, `OXBPRICE`, `OXTPRICE`, `OXUNITNAME`, `OXUNITQUANTITY`, `OXEXTURL`, `OXURLDESC`, `OXURLIMG`, `OXVAT`, `OXTHUMB`, `OXICON`, `OXPIC1`, `OXPIC2`, `OXPIC3`, `OXPIC4`, `OXPIC5`, `OXPIC6`, `OXPIC7`, `OXPIC8`, `OXPIC9`, `OXPIC10`, `OXPIC11`, `OXPIC12`, `OXWEIGHT`, `OXSTOCK`, `OXSTOCKFLAG`, `OXSTOCKTEXT`, `OXNOSTOCKTEXT`, `OXDELIVERY`, `OXINSERT`, `OXTIMESTAMP`, `OXLENGTH`, `OXWIDTH`, `OXHEIGHT`, `OXFILE`, `OXSEARCHKEYS`, `OXTEMPLATE`, `OXQUESTIONEMAIL`, `OXISSEARCH`, `OXISCONFIGURABLE`, `OXVARNAME`, `OXVARSTOCK`, `OXVARCOUNT`, `OXVARSELECT`, `OXVARMINPRICE`, `OXVARMAXPRICE`, `OXVARNAME_1`, `OXVARSELECT_1`, `OXVARNAME_2`, `OXVARSELECT_2`, `OXVARNAME_3`, `OXVARSELECT_3`, `OXTITLE_1`, `OXSHORTDESC_1`, `OXURLDESC_1`, `OXSEARCHKEYS_1`, `OXTITLE_2`, `OXSHORTDESC_2`, `OXURLDESC_2`, `OXSEARCHKEYS_2`, `OXTITLE_3`, `OXSHORTDESC_3`, `OXURLDESC_3`, `OXSEARCHKEYS_3`, `OXBUNDLEID`, `OXFOLDER`, `OXSUBCLASS`, `OXSTOCKTEXT_1`, `OXSTOCKTEXT_2`, `OXSTOCKTEXT_3`, `OXNOSTOCKTEXT_1`, `OXNOSTOCKTEXT_2`, `OXNOSTOCKTEXT_3`, `OXSORT`, `OXSOLDAMOUNT`, `OXNONMATERIAL`, `OXFREESHIPPING`, `OXREMINDACTIVE`, `OXREMINDAMOUNT`, `OXAMITEMID`, `OXAMTASKID`, `OXVENDORID`, `OXMANUFACTURERID`, `OXSKIPDISCOUNTS`, `OXRATING`, `OXRATINGCNT`, `OXMINDELTIME`, `OXMAXDELTIME`, `OXDELTIMEUNIT`, `OXUPDATEPRICE`, `OXUPDATEPRICEA`, `OXUPDATEPRICEB`, `OXUPDATEPRICEC`, `OXUPDATEPRICETIME`, `OXISDOWNLOADABLE`, `OXSHOWCUSTOMAGREEMENT`
*/
SELECT `OXTIMESTAMP`, `OXPUBLIC`, `OXUPDATE`, (SELECT GROUP_CONCAT(CONCAT((SELECT CONCAT(`OXARTNUM`,'~',`OXTITLE`,'~',`OXPRICE`) FROM `oxarticles` WHERE `oxarticles`.`OXID`=`OXARTID`),'~',`OXAMOUNT`,'~',`OXTIMESTAMP`) SEPARATOR '%') FROM `oxuserbasketitems` WHERE `oxuserbaskets`.`OXID`=`OXBASKETID`) AS OXARTICLESINBASKET FROM `oxuserbaskets` WHERE `OXUSERID`[email protected] AND `OXTITLE`='savedbasket' ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored ratings from this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`, `OXTYPE`, `OXOBJECTID`
We are aggregating the article fields for the review in OXARTICLEREVIEW from oxarticles as follows:
~ to separate the article fields from one another
From the table oxarticles we are skipping (add them if needed):
`OXID`, `OXSHOPID`, `OXPARENTID`, `OXACTIVE`, `OXHIDDEN`, `OXACTIVEFROM`, `OXACTIVETO`, `OXEAN`, `OXDISTEAN`, `OXMPN`, `OXSHORTDESC`, `OXPRICE`, `OXBLFIXEDPRICE`, `OXPRICEA`, `OXPRICEB`, `OXPRICEC`, `OXBPRICE`, `OXTPRICE`, `OXUNITNAME`, `OXUNITQUANTITY`, `OXEXTURL`, `OXURLDESC`, `OXURLIMG`, `OXVAT`, `OXTHUMB`, `OXICON`, `OXPIC1`, `OXPIC2`, `OXPIC3`, `OXPIC4`, `OXPIC5`, `OXPIC6`, `OXPIC7`, `OXPIC8`, `OXPIC9`, `OXPIC10`, `OXPIC11`, `OXPIC12`, `OXWEIGHT`, `OXSTOCK`, `OXSTOCKFLAG`, `OXSTOCKTEXT`, `OXNOSTOCKTEXT`, `OXDELIVERY`, `OXINSERT`, `OXTIMESTAMP`, `OXLENGTH`, `OXWIDTH`, `OXHEIGHT`, `OXFILE`, `OXSEARCHKEYS`, `OXTEMPLATE`, `OXQUESTIONEMAIL`, `OXISSEARCH`, `OXISCONFIGURABLE`, `OXVARNAME`, `OXVARSTOCK`, `OXVARCOUNT`, `OXVARSELECT`, `OXVARMINPRICE`, `OXVARMAXPRICE`, `OXVARNAME_1`, `OXVARSELECT_1`, `OXVARNAME_2`, `OXVARSELECT_2`, `OXVARNAME_3`, `OXVARSELECT_3`, `OXTITLE_1`, `OXSHORTDESC_1`, `OXURLDESC_1`, `OXSEARCHKEYS_1`, `OXTITLE_2`, `OXSHORTDESC_2`, `OXURLDESC_2`, `OXSEARCHKEYS_2`, `OXTITLE_3`, `OXSHORTDESC_3`, `OXURLDESC_3`, `OXSEARCHKEYS_3`, `OXBUNDLEID`, `OXFOLDER`, `OXSUBCLASS`, `OXSTOCKTEXT_1`, `OXSTOCKTEXT_2`, `OXSTOCKTEXT_3`, `OXNOSTOCKTEXT_1`, `OXNOSTOCKTEXT_2`, `OXNOSTOCKTEXT_3`, `OXSORT`, `OXSOLDAMOUNT`, `OXNONMATERIAL`, `OXFREESHIPPING`, `OXREMINDACTIVE`, `OXREMINDAMOUNT`, `OXAMITEMID`, `OXAMTASKID`, `OXVENDORID`, `OXMANUFACTURERID`, `OXSKIPDISCOUNTS`, `OXRATING`, `OXRATINGCNT`, `OXMINDELTIME`, `OXMAXDELTIME`, `OXDELTIMEUNIT`, `OXUPDATEPRICE`, `OXUPDATEPRICEA`, `OXUPDATEPRICEB`, `OXUPDATEPRICEC`, `OXUPDATEPRICETIME`, `OXISDOWNLOADABLE`, `OXSHOWCUSTOMAGREEMENT`
*/
SELECT `OXSHOPID`, IFNULL((SELECT `OXNAME` FROM `oxshops` WHERE `OXID`=`OXSHOPID`), '') AS OXSHOPNAME, `OXRATING`, `OXTIMESTAMP`,(SELECT CONCAT(`OXARTNUM`,'~',`OXTITLE`,'~',`OXPRICE`) FROM `oxarticles` WHERE `oxarticles`.`OXID`=`OXOBJECTID`) AS OXARTICLEREVIEW FROM `oxratings` WHERE `OXUSERID`[email protected] AND `OXTYPE`='oxarticle';
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored reviews from this user
We are skipping (add them if needed):
`OXID`, `OXOBJECTID`, `OXTYPE`, `OXUSERID`, `OXLANG`
We are aggregating the article fields for the review in OXARTICLEREVIEW from oxarticles as follows:
~ to separate the article fields from one another
From the table oxarticles we are skipping (add them if needed):
`OXID`, `OXSHOPID`, `OXPARENTID`, `OXACTIVE`, `OXHIDDEN`, `OXACTIVEFROM`, `OXACTIVETO`, `OXEAN`, `OXDISTEAN`, `OXMPN`, `OXSHORTDESC`, `OXPRICE`, `OXBLFIXEDPRICE`, `OXPRICEA`, `OXPRICEB`, `OXPRICEC`, `OXBPRICE`, `OXTPRICE`, `OXUNITNAME`, `OXUNITQUANTITY`, `OXEXTURL`, `OXURLDESC`, `OXURLIMG`, `OXVAT`, `OXTHUMB`, `OXICON`, `OXPIC1`, `OXPIC2`, `OXPIC3`, `OXPIC4`, `OXPIC5`, `OXPIC6`, `OXPIC7`, `OXPIC8`, `OXPIC9`, `OXPIC10`, `OXPIC11`, `OXPIC12`, `OXWEIGHT`, `OXSTOCK`, `OXSTOCKFLAG`, `OXSTOCKTEXT`, `OXNOSTOCKTEXT`, `OXDELIVERY`, `OXINSERT`, `OXTIMESTAMP`, `OXLENGTH`, `OXWIDTH`, `OXHEIGHT`, `OXFILE`, `OXSEARCHKEYS`, `OXTEMPLATE`, `OXQUESTIONEMAIL`, `OXISSEARCH`, `OXISCONFIGURABLE`, `OXVARNAME`, `OXVARSTOCK`, `OXVARCOUNT`, `OXVARSELECT`, `OXVARMINPRICE`, `OXVARMAXPRICE`, `OXVARNAME_1`, `OXVARSELECT_1`, `OXVARNAME_2`, `OXVARSELECT_2`, `OXVARNAME_3`, `OXVARSELECT_3`, `OXTITLE_1`, `OXSHORTDESC_1`, `OXURLDESC_1`, `OXSEARCHKEYS_1`, `OXTITLE_2`, `OXSHORTDESC_2`, `OXURLDESC_2`, `OXSEARCHKEYS_2`, `OXTITLE_3`, `OXSHORTDESC_3`, `OXURLDESC_3`, `OXSEARCHKEYS_3`, `OXBUNDLEID`, `OXFOLDER`, `OXSUBCLASS`, `OXSTOCKTEXT_1`, `OXSTOCKTEXT_2`, `OXSTOCKTEXT_3`, `OXNOSTOCKTEXT_1`, `OXNOSTOCKTEXT_2`, `OXNOSTOCKTEXT_3`, `OXSORT`, `OXSOLDAMOUNT`, `OXNONMATERIAL`, `OXFREESHIPPING`, `OXREMINDACTIVE`, `OXREMINDAMOUNT`, `OXAMITEMID`, `OXAMTASKID`, `OXVENDORID`, `OXMANUFACTURERID`, `OXSKIPDISCOUNTS`, `OXRATING`, `OXRATINGCNT`, `OXMINDELTIME`, `OXMAXDELTIME`, `OXDELTIMEUNIT`, `OXUPDATEPRICE`, `OXUPDATEPRICEA`, `OXUPDATEPRICEB`, `OXUPDATEPRICEC`, `OXUPDATEPRICETIME`, `OXISDOWNLOADABLE`, `OXSHOWCUSTOMAGREEMENT`
*/
SELECT `OXACTIVE`, `OXTEXT`, `OXCREATE`, `OXRATING`, `OXTIMESTAMP`,(SELECT CONCAT(`OXARTNUM`,'~',`OXTITLE`,'~',`OXPRICE`) FROM `oxarticles` WHERE `oxarticles`.`OXID`=`OXOBJECTID`) AS OXARTICLEREVIEW FROM `oxreviews` WHERE `OXUSERID`[email protected] AND `OXTYPE`='oxarticle' ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored invitations from this user
We are skipping (add them if needed):
`OXUSERID`, `OXPENDING`, `OXACCEPTED`
*/
SELECT `OXDATE`, `OXEMAIL`, `OXTYPE`, `OXTIMESTAMP` FROM `oxinvitations` WHERE `OXUSERID`[email protected] ORDER BY `OXTIMESTAMP`, `OXEMAIL`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Stored price alarms for this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`, `OXARTID`, `OXLANG`
We are aggregating the article fields for the review in OXARTICLEALARM from oxarticles as follows:
~ to separate the article fields from one another
From the table oxarticles we are skipping (add them if needed):
`OXID`, `OXSHOPID`, `OXPARENTID`, `OXACTIVE`, `OXHIDDEN`, `OXACTIVEFROM`, `OXACTIVETO`, `OXEAN`, `OXDISTEAN`, `OXMPN`, `OXSHORTDESC`, `OXPRICE`, `OXBLFIXEDPRICE`, `OXPRICEA`, `OXPRICEB`, `OXPRICEC`, `OXBPRICE`, `OXTPRICE`, `OXUNITNAME`, `OXUNITQUANTITY`, `OXEXTURL`, `OXURLDESC`, `OXURLIMG`, `OXVAT`, `OXTHUMB`, `OXICON`, `OXPIC1`, `OXPIC2`, `OXPIC3`, `OXPIC4`, `OXPIC5`, `OXPIC6`, `OXPIC7`, `OXPIC8`, `OXPIC9`, `OXPIC10`, `OXPIC11`, `OXPIC12`, `OXWEIGHT`, `OXSTOCK`, `OXSTOCKFLAG`, `OXSTOCKTEXT`, `OXNOSTOCKTEXT`, `OXDELIVERY`, `OXINSERT`, `OXTIMESTAMP`, `OXLENGTH`, `OXWIDTH`, `OXHEIGHT`, `OXFILE`, `OXSEARCHKEYS`, `OXTEMPLATE`, `OXQUESTIONEMAIL`, `OXISSEARCH`, `OXISCONFIGURABLE`, `OXVARNAME`, `OXVARSTOCK`, `OXVARCOUNT`, `OXVARSELECT`, `OXVARMINPRICE`, `OXVARMAXPRICE`, `OXVARNAME_1`, `OXVARSELECT_1`, `OXVARNAME_2`, `OXVARSELECT_2`, `OXVARNAME_3`, `OXVARSELECT_3`, `OXTITLE_1`, `OXSHORTDESC_1`, `OXURLDESC_1`, `OXSEARCHKEYS_1`, `OXTITLE_2`, `OXSHORTDESC_2`, `OXURLDESC_2`, `OXSEARCHKEYS_2`, `OXTITLE_3`, `OXSHORTDESC_3`, `OXURLDESC_3`, `OXSEARCHKEYS_3`, `OXBUNDLEID`, `OXFOLDER`, `OXSUBCLASS`, `OXSTOCKTEXT_1`, `OXSTOCKTEXT_2`, `OXSTOCKTEXT_3`, `OXNOSTOCKTEXT_1`, `OXNOSTOCKTEXT_2`, `OXNOSTOCKTEXT_3`, `OXSORT`, `OXSOLDAMOUNT`, `OXNONMATERIAL`, `OXFREESHIPPING`, `OXREMINDACTIVE`, `OXREMINDAMOUNT`, `OXAMITEMID`, `OXAMTASKID`, `OXVENDORID`, `OXMANUFACTURERID`, `OXSKIPDISCOUNTS`, `OXRATING`, `OXRATINGCNT`, `OXMINDELTIME`, `OXMAXDELTIME`, `OXDELTIMEUNIT`, `OXUPDATEPRICE`, `OXUPDATEPRICEA`, `OXUPDATEPRICEB`, `OXUPDATEPRICEC`, `OXUPDATEPRICETIME`, `OXISDOWNLOADABLE`, `OXSHOWCUSTOMAGREEMENT`
*/
SELECT `OXSHOPID`, IFNULL((SELECT `OXNAME` FROM `oxshops` WHERE `OXID`=`OXSHOPID`), '') AS OXSHOPNAME, `OXEMAIL`, `OXPRICE`, `OXCURRENCY`, `OXINSERT`, `OXSENDED`, `OXTIMESTAMP`,(SELECT CONCAT(`OXARTNUM`,'~',`OXTITLE`,'~',`OXPRICE`) FROM `oxarticles` WHERE `oxarticles`.`OXID`=`OXARTID`) AS OXARTICLEALARM FROM `oxpricealarm` WHERE `OXUSERID`[email protected] ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Vouchers this customer used
We are skipping (add them if needed):
`OXORDERID`, `OXUSERID`, `OXRESERVED`, `OXVOUCHERSERIEID`, `OXID`
*/
SELECT `OXDATEUSED`, `OXVOUCHERNR`, `OXDISCOUNT`, `OXTIMESTAMP` FROM `oxvouchers` WHERE `OXUSERID`[email protected] ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
Payments performed by this user
We are skipping (add them if needed):
`OXID`, `OXUSERID`
*/
SELECT `OXPAYMENTSID`, `OXVALUE`, `OXTIMESTAMP` FROM `oxuserpayments` WHERE `OXUSERID`[email protected] ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */
/*
When Private Sales is enabled it is also stored if a customer agreed to the terms
We are skipping (add them if needed):
OXUSERID
*/
SELECT `OXSHOPID`, IFNULL((SELECT `OXNAME` FROM `oxshops` WHERE `OXID`=`OXSHOPID`), '') AS OXSHOPNAME, `OXTERMVERSION`, `OXACCEPTEDTIME`, `OXTIMESTAMP` FROM `oxacceptedterms` WHERE `OXUSERID`[email protected] ORDER BY `OXTIMESTAMP`;
SELECT '' AS ''; /* Insert two line breaks */

 

We hope that helps until we got a bit more clear information about this topic.



Start the discussion at OXID forums