PHP / mySQL

[paddy]

I'll try and explain this the best I can:

I have this table:

mysql> select * from subject_areas;
+------------+-------------------+-----------------+
| subject_id | subject_parent_id | subject_area    |
+------------+-------------------+-----------------+
|          1 |                 0 | mySQL           |
|          2 |                 0 | installing php  |
|          3 |                 0 | C++             |
|          4 |                 0 | Cisco IP Phones |
|          5 |                 0 | Windows         |
|          6 |                 0 | Linux           |
|          7 |                 6 | User Management |
|          8 |                 5 | Reformatting    |
|          9 |                 8 | XP              |
|         10 |                 5 | Network         |
|         11 |                 9 | Home            |
|         12 |                 9 | Pro             |
+------------+-------------------+-----------------+

and I am trying to get the following results set:
(a nested tree with each subject under its respective parent).

1-mySQL
2-installing php
3-C++
4-Cisco IP Phones
5-Windows
- 8-Reformatting
- - 9-XP
- - - 11-Home
- - - 12-Pro
- 10-Network
6-Linux
- 7-User Management

I am currently doing this with an echo statement and a recursive PHP call.

For multiple reasons (not just that recursion in PHP is nasty) I am trying to obtain the same results from a single mySQL query.
I am using mySQL v.4.0.24, upgrading is not an option.

Can anyone help?

Also if there is anyway the dashes can be recreated that would be awesome.
I've been trying for most of the afternoon evening, but have come up blank.

Thanks for any help you can give

 

[paddy]

Solved.

 

[paddy]

Ah, well, erm, not really "solved"

I needed the result set in an array - at the time of asking I was just echoing the results to screen.

"solved" it by calling the function with another function and passing it an array by reference, which it populates.

Not elegant, or clever, or really what I was looking for, but a work around which will work for this small thing.

It should be solvable with nested SELECTs (mySQL > v4.3) and a bit more carefull database design (designer_experience > 2 weeks). I'm still using recursion.

Sorry for the false hope.

 

[paddy]

I worked out a solution, of sorts

I added 2 extra fields to the table:

+------------+-------------------+-----------------+-----------------+-------+
| subject_id | subject_parent_id | subject_area    | string_id       | depth |
+------------+-------------------+-----------------+-----------------+-------+
|          1 |                 0 | mySQL           | 001             |     1 |
|          2 |                 0 | installing php  | 002             |     1 |
|          3 |                 0 | C++             | 003             |     1 |
|          4 |                 0 | Cisco IP Phones | 004             |     1 |
|          5 |                 0 | Windows         | 005             |     1 |
|          6 |                 0 | Linux           | 006             |     1 |
|          7 |                 6 | User Management | 006.001         |     2 |
|          8 |                 5 | Reformatting    | 005.001         |     2 |
|          9 |                 8 | XP              | 005.001.001     |     3 |
|         10 |                 5 | Network         | 005.002         |     2 |
|         11 |                 9 | Home            | 005.001.001.001 |     4 |
|         12 |                 9 | Pro             | 005.001.001.002 |     4 |
|         13 |                 6 | XFconfig        | 006.002         |     2 |

the 002.003 etc is a string, when sorted it falls into the correct order.

A few funky functions create the correct string for any new subjects which are added, and the depth function notes how many "-" need to be added at presentation time, as well as keeping track of which number is next

So, a sort of solution

I idea came to me when thinking about sorting IP addresses