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;
SELECT a.nid, GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName FROM Notes a INNER JOIN Positions b ON FIND_IN_SET(b.id, a.forDepts) > 0 GROUP BY a.nid SQLFiddle Demo
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
Use the list_of_ids directly: format_strings=”,”.join([‘%s’] * len(list_of_ids)) cursor.execute(“DELETE FROM foo.bar WHERE baz IN (%s)” % format_strings, tuple(list_of_ids)) That way you avoid having to quote yourself, and avoid all kinds of sql injection. Note that the data (list_of_ids) is going directly to mysql’s driver, as a parameter (not in the query text) so there is no … Read more
Here’s how I’d do it – For Schemas (or Databases – they are synonyms): SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = “mydatabasename”; For Tables: SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = “mydatabasename” AND T.table_name = “tablename”; For Columns: SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = “mydatabasename” AND table_name … 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
In Mysql there is a cheap trick to do this: mysql> select ~0; +———————-+ | ~0 | +———————-+ | 18446744073709551615 | +———————-+ the tilde is the bitwise negation. The resulting value is a bigint. See: http://dev.mysql.com/doc/refman/5.1/en/bit-functions.html#operator_bitwise-invert For the other integer flavours, you can use the right bitshift operator >> like so: SELECT ~0 as max_bigint_unsigned … 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