INSERT 3 digits max.

[timmy0320]

Is there a way to INSERT into the database such as this way:

 

id    | node    | cat_name
----+--------+-------------
9    | 009      | Parent
10  | 010      | Another Parent
11  | 009011 | Sub Cat
13  | 010013 | Sub Cat 2
12  | 010012 | Sub Cat 3

 

As `id` will be auto_increment for the 'node' it will take the number it is getting assigned and add 2 zeros before it (if it's 10 then it will only add one zero). Basically every 3 digits will be a max value of 3 (xxx) digits.

 

Thanks

[rhodesa]

For what you want, you would have to do the INSERT, so an auto_increment is assigned, then calculate with PHP the value of Node, then update that row.

 

But if you are looking to manage hierarchical data, check this out:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

 

[Psycho]

Doing that would make it difficult to query for child elements. It would make much more sense to me, just to create a field for the parent (use 0 if the element is a parent)

id  | parent | cat_name
----+--------+-------------
9   | 0      | Parent
10  | 0      | Another Parent
11  | 9      | Sub Cat
13  | 10     | Sub Cat 2
12  | 10     | Sub Cat 3

 

You could then easily create the node at run time like so:

 

if($record['parent']==0) {
 $node = sprintf("%03d", $record['id']);
} else {
 $node = sprintf("%03d%03d", $record['id'], $record['parent']);
}

[timmy0320]

That's what I currently have but when I go to delete with multiple childs, I can only get it to delete the first child and not every child under it.

 

Like I had

 

Links

  -Site 1

    -Site 1 test

  -Site 2

    -Site 2 test

 

When I did a DELETE by parent or category id it wouldn't delete the 'test' ones.

[Psycho]

That's what I currently have but when I go to delete with multiple childs, I can only get it to delete the first child and not every child under it.

 

Then you aren't doing it right. in my example above you can delete ALL the child records for the parent with the ID of 10 with the following query

DELETE FROM table WHERE parent = 10

 

OR you can delete the parent and all child records with a single query

DELETE FROM table WHERE id = 10 OR parent = 10

 

[timmy0320]

That's what I currently have but when I go to delete with multiple childs, I can only get it to delete the first child and not every child under it.

 

Then you aren't doing it right. in my example above you can delete ALL the child records for the parent with the ID of 10 with the following query

DELETE FROM table WHERE parent = 10

 

OR you can delete the parent and all child records with a single query

DELETE FROM table WHERE id = 10 OR parent = 10

 

 

 

Yes that then limits you to one child per parent. For example in your example if Sub Cat2 had a child of 'Test' it would then be assigned ID 14 with a Parent of 13

 

 

So then when you go to delete parent 10 by PARENT = 10 OR ID = 10  , You will successfully delete Sub Cat2 and it's parent leaving ID 'Test' left hanging in the database

[Psycho]

Yes that then limits you to one child per parent. For example in your example if Sub Cat2 had a child of 'Test' it would then be assigned ID 14 with a Parent of 13

 

So then when you go to delete parent 10 by PARENT = 10 OR ID = 10  , You will successfully delete Sub Cat2 and it's parent leaving ID 'Test' left hanging in the database

 

Your original post showed an example with parent and child records. It did NOT show, nor did you mention, that the child records could also have sub-child records. That is a VERY important piece of information that you left our. As my sig states "The quality of the responses received is directly porportional to the quality of the question asked."

 

Even with that new information, what you originally asked in the first post doesn't solve that problem. You might want to look at the link rhodesa provided above.

 

But, using the NEW parametes you have provided, it is still a fairly easy operation to delete a record and all it's child records. Although I abhore doing queries within loops the most straitforward appraoch would be a recursive function:

 

function deleteRecord($recordID) {

  //Delete the parent record
  $query = "DELETE FROM table WHERE id = $recordID";
  mysql_query($query) or die (mysql_error());

  //Find the child records for the current record
  $query = "SELECT id FROM table WHERE parent = $recordID";
  $result = mysql_query($query) or die (mysql_error());

  //Repeat process for each child record
  while ($childRecord = mysql_fetch_assoc($result)) {
    $childID = $childRecord['id'];
    deleteRecord($childID);
  }
}

[discomatt]

Here's a great article on MySQL hierarchies

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[Psycho]

Here's a great article on MySQL hierarchies

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

That the same link rhodesa posted in reply #1

[discomatt]

That the same link rhodesa posted in reply #1

My mistake

[timmy0320]

Thanks for all the help... I just changed my whole setup to using a nested type as explained on the links using left and right values. Thanks for the help though, I guess I'll be more specific next time I post a question.