DKs blog - MySQL examples

Connection strings examples
Ffmpeg
Linux administration
MySQL administration
Programming
       Android examples
       C# examples
       CSS examples
       Elastic Search
       Excel macros
       HTML/javascript examples
       MySQL examples
       Python
       Regular Expressions
       SQL (T-SQL) examples
       VB.NET examples
sublime text
Windows administration
WWW - reklame

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

DK's Blog

MySQL examples

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

How to convert timestamp to date time in MySQL