This works well for me. SHOW COLUMNS FROM `table` LIKE ‘fieldname’; With PHP it would be something like… $result = mysql_query(“SHOW COLUMNS FROM `table` LIKE ‘fieldname'”); $exists = (mysql_num_rows($result))?TRUE:FALSE;
Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you’re making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn’t necessarily … Read more
The MySQL documentation you cite actually says a little bit more than you mention. It also says, A “’” inside a string quoted with “’” may be written as “””. (Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current … Read more
You have to do the processing on the results from the db query on a callback only. Just like. function getColour(username, roomCount, callback) { connection.query(‘SELECT hexcode FROM colours WHERE precedence = ?’, [roomCount], function(err, result) { if (err) callback(err,null); else callback(null,result[0].hexcode); }); } //call Fn for db query with callback getColour(“yourname”,4, function(err,data){ if (err) { … Read more
Here you go, use this to get the date between the 1st of last month and the last of last month in MySQL: … order_placed_date BETWEEN DATE_FORMAT(NOW() – INTERVAL 1 MONTH, ‘%Y-%m-01 00:00:00’) AND DATE_FORMAT(LAST_DAY(NOW() – INTERVAL 1 MONTH), ‘%Y-%m-%d 23:59:59’)
The insert syntax is INSERT INTO table (columns_list) VALUES (values_list) so your insert would look something like this (i’m not a MySQL expert but you can fit the query): INSERT INTO data_tracking (`data_id` , `field` , `old_value` , `new_value` , `modified` ) VALUES (NEW.data_id, ‘field1’, OLD.field, NEW.field, CURRENT_DATETIME()); And repeat it for variation on field2 … Read more
If you need a replacement hash to match the password() function, use this: SHA1(UNHEX(SHA1())); E.g. mysql> SELECT PASSWORD(‘mypass’); +——————————————-+ | PASSWORD(‘mypass’) | +——————————————-+ | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | +——————————————-+ and replacement that gives the same answer in version 8: mysql> SELECT CONCAT(‘*’, UPPER(SHA1(UNHEX(SHA1(‘mypass’))))); +————————————————-+ | CONCAT(‘*’, UPPER(SHA1(UNHEX(SHA1(‘mypass’))))) | +————————————————-+ | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | +————————————————-+
You can insert/update multiple rows using INSERT … ON DUPLICATE KEY UPDATE. The documentation has the following example: INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); Or am I misunderstanding your question?
only_full_group_by = on tells MySQL engine: Do not apply GROUP BY when you have doubt about what results to show and throw an error. Only apply it if Group By specifically tells you what to do. i.e. when the Group By is full and perfect! only_full_group_by = off tells MySQL engine: always apply GROUP BY … Read more
This should do it: SELECT report_id, computer_id, date_entered FROM reports AS a WHERE date_entered = ( SELECT MAX(date_entered) FROM reports AS b WHERE a.report_id = b.report_id AND a.computer_id = b.computer_id )