How to allow fulltext searching with hyphens in the search query

From here http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html One solution to find a word with a dashes or hyphens in is to use FULL TEXT SEARCH IN BOOLEAN MODE, and to enclose the word with the hyphen / dash in double quotes. Or from here http://bugs.mysql.com/bug.php?id=2095 There is another workaround. It was recently added to the manual: ” Modify a … Read more

mysql unique number generation

While it seems somewhat awkward, this is what can be done to achieve the goal: SELECT FLOOR(10000 + RAND() * 89999) AS random_number FROM table WHERE random_number NOT IN (SELECT unique_id FROM table) LIMIT 1 Simply put, it generates N random numbers, where N is the count of table rows, filters out those already present … Read more

Execute multiple semi-colon separated query using mysql Prepared Statement

No, it is not possible. PREPARE / EXECUTE stmt can execute only one query at a time, many statements cannot be combined. See documentation: http://dev.mysql.com/doc/refman/5.0/en/prepare.html … a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements. Anyway, to simplify your code I would create … Read more

mySQL SELECT upcoming birthdays

To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days. SELECT * FROM persons WHERE DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0) YEAR) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY); If … Read more

How to count date difference excluding weekend and holidays in MySQL

You might want to try this: Count the number of working days (took it from here) SELECT 5 * (DATEDIFF(‘2012-12-31’, ‘2012-01-01’) DIV 7) + MID(‘0123444401233334012222340111123400012345001234550’, 7 * WEEKDAY(‘2012-01-01’) + WEEKDAY(‘2012-12-31’) + 1, 1) This gives you 261 working days for 2012. Now you need to know your holidays that are not on a weekend SELECT … Read more

INSERT INTO fails with node-mysql

Pay attention to the documentation of node-mysql: If you paid attention, you may have noticed that this escaping allows you to do neat things like this: var post = {id: 1, title: ‘Hello MySQL’}; var query = connection.query(‘INSERT INTO posts SET ?’, post, function(err, result) { // Neat! }); console.log(query.sql); // INSERT INTO posts SET … Read more