ip: 18.97.9.175 DKs blog - MySQL examples

DK's Blog

MySQL examples

How to get all IDs in hierarchy (if your table have int ID, int ParentID, ...)

 

DELIMITER $$
DROP FUNCTION IF EXISTS `DBName`.`GetAncestry` $$
CREATE FUNCTION `DBName`.`GetAncestry` (StartID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
  DECLARE rv VARCHAR(1024);
  DECLARE cm CHAR(1);
  DECLARE ch INT;
 
  SET rv = '';
  SET cm = '';
  SET ch = StartID;
  WHILE ch > 0 DO
    SELECT IFNULL(ParentID ,-1) INTO ch FROM
    (SELECT ParentID FROM Category WHERE ParentID = ch) A;
    IF ch > 0 THEN
      SET rv = CONCAT(rv,cm,ch);
      SET cm = ',';
    END IF;
  END WHILE;
  RETURN rv;
END $$
DELIMITER ;

 



How to convert timestamp to date time in MySQL

Lets say you have field in table which name is clock and of timestamp type(INT)
This is European date format (DD.MM.YYYY HH:mm:ss)

 

SELECT DATE_FORMAT(FROM_UNIXTIME(`clock`), '%e.%c.%Y %H:%i:%s') AS 'MyDateTime'
FROM myTable

 


@2016