PHP / mySQL

paddy · 6 Nov 2006 at 18:53

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

I have this table:

Code:

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).

Code:

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 · 7 Nov 2006 at 09:26

Solved.

Dj_Jestar · 7 Nov 2006 at 09:30

Care to share?

/rhyme

toString · 7 Nov 2006 at 09:55

Yeah me too.

I've tried this in the past and was forced to use recursive queries..

paddy · 7 Nov 2006 at 11:04

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.

Dj_Jestar · 7 Nov 2006 at 11:27

Recursion is the only method afaik, so was wondering if you stumbled upon recursive statements that I did not know existed.

paddy · 9 Nov 2006 at 19:23

I worked out a solution, of sorts

I added 2 extra fields to the table:

Code:

+------------+-------------------+-----------------+-----------------+-------+
| 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

adam3223 · 10 Nov 2006 at 14:16

If u were searching for a particular path like:
Windows/Reformatting/XP/Home

You know how many levels are in it and could dynamically create 1 sql SELECT using the required number of joins.

Also because you have added the depth column, you can search from a particular ID becuse you know how deep it is and you can create 1 joined sql query from that, but you would have to use another sql query to get the depth in the first place.