RE: Tree-like structure: make it simply
how about recreating the table w/o the autoincrement, then reload the data, then alter
table to reimplement the autoincrement?
-Original Message-
From: Matthew [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 7:12 PM
To: Alex E.Wintermann; [EMAIL PROTECTED]
Subject: Re: Tree-like structure: make it simply
I think I can help with questions 1 and 3... see below,
- Original Message -
From: Alex E.Wintermann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:44 PM
Subject: Tree-like structure: make it simply
Hello mysql,
I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
);
INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1',
'(to subcat.2
to cat.1)');
/* END DUMP /
_QUESTION 1_: in what way should i run query to restore table with
`id`' like in dump? (`id` is auto_increment)
either omit the `id` field when re-loading the data, e.g.
INSERT INTO `sp_tovar_vid`
(`id_tovar_vid`,`name`,`description`) VALUES (0,
'root', 'root category');
or, replace the `id` values with an empty string when
re-loading the table
data, e.g.
INSERT INTO `sp_tovar_vid` VALUES ('', 0, 'cat.1', '');
both techniques prompt mysql to reassign the `id` values.
note that I do not think it is wise/possible to run a query
to restore (or
clean up) the auto_increment values without re-loading the table data.
_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?
_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?
use SELECT IF(id=18, true_expression, false expression) FROM table...
I'm not clear what you mean by subsubcat 1 - subcat.2 -
cat.1... minus, or
some sort of string concatenation?
true expression might be field1 - field2, or CONCAT(field1,
field2, ...)
--
Best regards,
Alex mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Tree-like structure: make it simply
Hello Matthew,
Friday, December 5, 2003, 2:12:08 AM, you wrote:
thanks, but some more see below.
[skipped]
I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
);
INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', '(to subcat.2
to cat.1)');
/* END DUMP /
[skipped]
_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?
_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?
M use SELECT IF(id=18, true_expression, false expression) FROM table...
M I'm not clear what you mean by subsubcat 1 - subcat.2 - cat.1... minus, or
M some sort of string concatenation?
M true expression might be field1 - field2, or CONCAT(field1, field2, ...)
elucidation: i need to run recurse search to build string shown.
it means: if we have `id`=18, we must build following string after
mentioned recurse search runnnig.
string: subsubcat 1 + subcat 2 + cat.1 + root
it's tree:
/**/
root (`id`=0, `parent`=0)
|--cat.1 (`id`=7, `parent`=0)
| |-subcat 1
| |--subcat 2 (`id`=13, `parent`=7)
| | |-subsubcat 1 (`id`=18, `parent`=13)
| |-subcat 3
|-cat.2
|--cat.3
| |-subcat 1
| |-subcat 2
| |-subcat 3
|-cat.4
|-cat.5
/**/
--
Best regards,
Alexmailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tree-like structure: make it simply
Hello mysql,
I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
);
INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', '(to subcat.2
to cat.1)');
/* END DUMP /
_QUESTION 1_: in what way should i run query to restore table with
`id`' like in dump? (`id` is auto_increment)
_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?
_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?
--
Best regards,
Alex mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tree-like structure: make it simply
I think I can help with questions 1 and 3... see below,
- Original Message -
From: Alex E.Wintermann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:44 PM
Subject: Tree-like structure: make it simply
Hello mysql,
I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
);
INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', '(to subcat.2
to cat.1)');
/* END DUMP /
_QUESTION 1_: in what way should i run query to restore table with
`id`' like in dump? (`id` is auto_increment)
either omit the `id` field when re-loading the data, e.g.
INSERT INTO `sp_tovar_vid` (`id_tovar_vid`,`name`,`description`) VALUES (0,
'root', 'root category');
or, replace the `id` values with an empty string when re-loading the table
data, e.g.
INSERT INTO `sp_tovar_vid` VALUES ('', 0, 'cat.1', '');
both techniques prompt mysql to reassign the `id` values.
note that I do not think it is wise/possible to run a query to restore (or
clean up) the auto_increment values without re-loading the table data.
_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?
_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?
use SELECT IF(id=18, true_expression, false expression) FROM table...
I'm not clear what you mean by subsubcat 1 - subcat.2 - cat.1... minus, or
some sort of string concatenation?
true expression might be field1 - field2, or CONCAT(field1, field2, ...)
--
Best regards,
Alex mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
