The products table has the products_date_added and products_last_modified fields already there. I would probably use the products_last_modified
The following does not include the number of sales for each product or take into consideration a product that is in a sub-category of a sub-category of a category
If you are using a MySQL statement to retrieve all the products, you would just add an ORDER BY
Something like
Code:
SELECT p.*, pd.products_nameFROM products AS p
JOIN products_description AS pd ON p.products_id = pd.products_id
ORDER BY p.products_last_modified DESC
INTO OUTFILE '/path/to/your/exported_file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Note that this is set to get all the info from the products table and will show the newest date first. You may only want the products_id. if so, the p.* would become p.products_id. Changing to that would give you the product title and date last entered/modified.
If you wanted to include a link to the product in the spreadsheet, you would need to do a little more work for that as
Code:
https://your_site.com/index.php?main_page=product_info&cPath=1_36&products_id=338
has the main category of 1 with the sub-category of 26 and then the products_id of 338.
In the case above, the 1_36 is the combination of the parent_id for the categories_id of 38 in the categories table and, the 36 is the master_categories_id from the products table.
Code:
SELECT p.*,
pd.products_name,
CONCAT('https://yoursite.com/index.php?main_page=product_info&cpPath=', c.parent_id, '_', c.master_categories_id, '&products_id=', p.products_id) AS product_url
FROM products AS p
JOIN products_description AS pd ON p.products_id = pd.products_id
JOIN categories AS c ON c.categories_id = p.master_categories_id
ORDER BY p.products_last_modified DESC
INTO OUTFILE '/path/to/your/exported_file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Again, this code is getting everything from the products table.
CAVEAT EMPTOR as I have not thoroughly tested the above. The good news is that SELECT statements can and do fail without munging the DB.
STILL... BACKUP FIRST!
Bookmarks