Here’s the expression that also caters for leap years: YEAR(date1) – YEAR(date2) – (DATE_FORMAT(date1, ‘%m%d’) < DATE_FORMAT(date2, ‘%m%d’)) This works because the expression (DATE_FORMAT(date1, ‘%m%d’) < DATE_FORMAT(date2, ‘%m%d’)) is true if date1 is “earlier in the year” than date2 and because in mysql, true = 1 and false = 0, so the adjustment is simply … Read more
try this solution: not tried it myself but apparently it works. source: http://forums.mysql.com/read.php?52,177343,177985#msg-177985 SET GLOBAL log_bin_trust_function_creators=1; DROP FUNCTION IF EXISTS fnStripTags; DELIMITER | CREATE FUNCTION fnStripTags( Dirty varchar(4000) ) RETURNS varchar(4000) DETERMINISTIC BEGIN DECLARE iStart, iEnd, iLength int; WHILE Locate( ‘<‘, Dirty ) > 0 And Locate( ‘>’, Dirty, Locate( ‘<‘, Dirty )) > 0 … Read more
MySQL documentation covers this topic. Here is a synopsis. When using join or inner join, the on condition is optional. This is different from the ANSI standard and different from almost any other database. The effect is a cross join. Similarly, you can use an on clause with cross join, which also differs from standard … Read more
Old thread but just looked into this, so here goes: if you are using InnoDB on a recent version of MySQL, you can get the list of IDs using LAST_INSERT_ID() and ROW_COUNT(). InnoDB guarantees sequential numbers for AUTO INCREMENT when doing bulk inserts, provided innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive). Consequently you … Read more
USE TIMESTAMPDIFF MySQL function. For example, you can use: SELECT TIMESTAMPDIFF(SECOND, ‘2012-06-06 13:13:55’, ‘2012-06-06 15:20:18’) In your case, the third parameter of TIMSTAMPDIFF function would be the current login time (NOW()). Second parameter would be the last login time, which is already in the database.
SELECT * FROM courses WHERE DATE_ADD(NOW(), INTERVAL 2 HOUR) > start_time See Date and Time Functions for other date/time manipulation.
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;
I almost never do two queries. Simply return one more row than is needed, only display 10 on the page, and if there are more than are displayed, display a “Next” button. SELECT x, y, z FROM `table` WHERE `some_condition` LIMIT 0, 11 // iterate through and display 10 rows. // if there were 11 … Read more
I’ll assume you want to check a string value. One nice way is the REGEXP operator, matching the string to a regular expression. Simply do select field from table where field REGEXP ‘^-?[0-9]+$’; this is reasonably fast. If your field is numeric, just test for ceil(field) = field instead.
For others that find this question looking to delete while using a subquery, I leave you this example for outsmarting MySQL (even if some people seem to think it cannot be done): DELETE e.* FROM tableE e WHERE id IN (SELECT id FROM tableE WHERE arg = 1 AND foo = ‘bar’); will give you … Read more