I need some assistance with a SQL query and update that I would like to do.
In my MySQL Database, I would like to update the date_added field to the current date for ALL records.
Any SQL experts that could suggest the statement(s) to use to perform this?
Thanks..
OC version. Just use this instead: https://github.com/opencart/opencart/is ... -813333246
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Then I believe this is the link you are searching for
https://stackoverflow.com/
https://stackoverflow.com/
DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.
https://www.youtube.com/watch?v=zXIxDoCRc84
UPDATE oc_product SET date_added = NOW()
Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com
From the master branch version, by pulling the db_schema() function and the helper file from the system/helper/db_schema.php file, you could use something like:
If you have enough amount of resource on your server to increase the post size in your php.ini or .user.ini file, you could run this query to update all date fields in shot after doing a full backup of your store - including database. Alternatively, at best, to run this query on a development / virtual server.
However, take note that applying these changes won't change anything based on the fact that your payment and shipping service providers may not provide this option to reset your dates. Which means, the data between the two databases, local and providers, will provide inaccurate results with the datetime period.
Code: Select all
require_once(DIR_SYSTEM . 'helper/db_schema.php');
$tables = db_schema();
foreach ($tables as $table) {
$table_query = $this->db->query("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" . DB_DATABASE . "' AND TABLE_NAME = '" . DB_PREFIX . $table['name'] . "'");
if ($table_query->num_rows) {
for ($i = 0; $i < count($table['field']); $i++) {
if (($table['field'][$i]['type'] == 'date' || $table['field'][$i]['type'] == 'datetime') && $table['field'][$i]['name'] == 'date_added') {
$this->db->query("UPDATE `" . $table['name'] . "` SET `" . $table['field'][$i]['name'] . "` = NOW()");
}
}
}
}
However, take note that applying these changes won't change anything based on the fact that your payment and shipping service providers may not provide this option to reset your dates. Which means, the data between the two databases, local and providers, will provide inaccurate results with the datetime period.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
It's much easier to copy the string I provided and simply replace the table name using PHPMyAdmin. You can grab the matching table names using this:
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';
Easy-peasy, no need for any PHP to achieve this.
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';
Easy-peasy, no need for any PHP to achieve this.
Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com
However, your query is only pulling the data, it does not write to it while the OP's request is about updating the date_added field.thekrotek wrote: ↑Sat Oct 09, 2021 7:58 pmIt's much easier to copy the string I provided and simply replace the table name using PHPMyAdmin. You can grab the matching table names using this:
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';
Easy-peasy, no need for any PHP to achieve this.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
I already provided the string to update the field earlier.straightlight wrote: ↑Sat Oct 09, 2021 9:47 pmHowever, your query is only pulling the data, it does not write to it while the OP's request is about updating the date_added field.
Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com
Correct, you provided the string for one table already aside from the rest.thekrotek wrote: ↑Sat Oct 09, 2021 10:03 pmI already provided the string to update the field earlier.straightlight wrote: ↑Sat Oct 09, 2021 9:47 pmHowever, your query is only pulling the data, it does not write to it while the OP's request is about updating the date_added field.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Who is online
Users browsing this forum: No registered users and 28 guests
