Попробуйте следующее:
Определение таблицы:
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
parent_id INT,
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category (id)
) engine=innodb;
Экспериментальные строки:
INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);
Рекурсивная хранимая процедура:
DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
DECLARE catname VARCHAR(20);
DECLARE temppath TEXT;
DECLARE tempparent INT;
SET max_sp_recursion_depth = 255;
SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
IF tempparent IS NULL
THEN
SET path = catname;
ELSE
CALL getpath(tempparent, temppath);
SET path = CONCAT(temppath, '/', catname);
END IF;
END$$
DELIMITER ;
Функция обертки для хранимой процедуры:
DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE res TEXT;
CALL getpath(cat_id, res);
RETURN res;
END$$
DELIMITER ;
Выберите пример:
SELECT id, name, getpath(id) AS path FROM category;
Выход:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1 |
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA |
| 24 | categoryB | category1/categoryA/categoryB |
| 25 | categoryC | category1/categoryA/categoryC |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+
Фильтрация строк определенным путем :
SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';
Выход:
+----+-----------+-----------------------------------------+
| id | name | path |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2 |
| 21 | category3 | category1/category2/category3 |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+
Замените 2010 на нужный год, а Europe / Berlin на свой часовой пояс:
<?php
date_default_timezone_set('Europe/Berlin');
echo gmdate("W", strtotime("31 December 2010"));
?>
Вы получите одно из значений 01
, 52
или 53
.
Просто для удовольствия ( Демо ):
<?php
date_default_timezone_set('Europe/Berlin');
echo "ISO-8601 week number of year, weeks starting on Monday, of 31 December:\n\n";
for ($year = 1900; $year < 2100; $year++) {
$date = strtotime("31 December $year");
echo "$year => ", gmdate("W", $date), "\n";
}
?>
Вывод:
ISO-8601 week number of year, weeks starting on Monday, of 31 December:
1900 => 01
1901 => 01
1902 => 01
1903 => 53
1904 => 52
1905 => 52
1906 => 52
1907 => 01
1908 => 53
1909 => 52
1910 => 52
1911 => 52
1912 => 01
1913 => 01
1914 => 53
1915 => 52
1916 => 52
1917 => 52
1918 => 01
1919 => 01
1920 => 53
1921 => 52
1922 => 52
1923 => 52
1924 => 01
1925 => 53
1926 => 52
1927 => 52
1928 => 52
1929 => 01
1930 => 01
1931 => 53
1932 => 52
1933 => 52
1934 => 52
1935 => 01
1936 => 53
1937 => 52
1938 => 52
1939 => 52
1940 => 01
1941 => 01
1942 => 53
1943 => 52
1944 => 52
1945 => 52
1946 => 01
1947 => 01
1948 => 53
1949 => 52
1950 => 52
1951 => 52
1952 => 01
1953 => 53
1954 => 52
1955 => 52
1956 => 52
1957 => 01
1958 => 01
1959 => 53
1960 => 52
1961 => 52
1962 => 52
1963 => 01
1964 => 53
1965 => 52
1966 => 52
1967 => 52
1968 => 01
1969 => 01
1970 => 53
1971 => 52
1972 => 52
1973 => 52
1974 => 01
1975 => 01
1976 => 53
1977 => 52
1978 => 52
1979 => 52
1980 => 01
1981 => 53
1982 => 52
1983 => 52
1984 => 52
1985 => 01
1986 => 01
1987 => 53
1988 => 52
1989 => 52
1990 => 52
1991 => 01
1992 => 53
1993 => 52
1994 => 52
1995 => 52
1996 => 01
1997 => 01
1998 => 53
1999 => 52
2000 => 52
2001 => 52
2002 => 01
2003 => 01
2004 => 53
2005 => 52
2006 => 52
2007 => 52
2008 => 01
2009 => 53
2010 => 52
2011 => 52
2012 => 52
2013 => 01
2014 => 01
2015 => 53
2016 => 52
2017 => 52
2018 => 52
2019 => 01
2020 => 53
2021 => 52
2022 => 52
2023 => 52
2024 => 01
2025 => 01
2026 => 53
2027 => 52
2028 => 52
2029 => 52
2030 => 01
2031 => 01
2032 => 53
2033 => 52
2034 => 52
2035 => 52
2036 => 01
2037 => 53
2038 => 01
2039 => 01
2040 => 01
2041 => 01
2042 => 01
2043 => 01
2044 => 01
2045 => 01
2046 => 01
2047 => 01
2048 => 01
2049 => 01
2050 => 01
2051 => 01
2052 => 01
2053 => 01
2054 => 01
2055 => 01
2056 => 01
2057 => 01
2058 => 01
2059 => 01
2060 => 01
2061 => 01
2062 => 01
2063 => 01
2064 => 01
2065 => 01
2066 => 01
2067 => 01
2068 => 01
2069 => 01
2070 => 01
2071 => 01
2072 => 01
2073 => 01
2074 => 01
2075 => 01
2076 => 01
2077 => 01
2078 => 01
2079 => 01
2080 => 01
2081 => 01
2082 => 01
2083 => 01
2084 => 01
2085 => 01
2086 => 01
2087 => 01
2088 => 01
2089 => 01
2090 => 01
2091 => 01
2092 => 01
2093 => 01
2094 => 01
2095 => 01
2096 => 01
2097 => 01
2098 => 01
2099 => 01
Если бы вы спрашивали, как получить количество недель, оставшихся до конца года, это бы помогло:
<?php
$year = date('Y');
$week_count = date('W', strtotime($year . '-12-31'));
if ($week_count == '01')
{
$week_count = date('W', strtotime($year . '-12-24'));
}
echo ($week_count - date('W'));
echo ' weeks left in ' . date('Y') . '!';
?>
Edit: Added logic to compensate for the '01' returned by date('W');