Oder of processing data

[frizzle]

Hi Group,

On my site, i have a number of employees in the DB,
each of the employees can work together with max 3 colleagues.

The table 'employees'looks like the following:
'id' INT(5) , 'name' VARCHAR(50) , 'buddies_id' VARCHAR(20)
1 , John , '21-2-90'
2 , Bruce , '45-78'
etc.
First i run a query (LIMIT 20) to get 20 employees id's, names, and
their
'buddies'. The buddies are an array of max 3 id's of other colleagues.

I do a while $employees = $get_employees, print blah

I also store the values of buddies_id, turn that into a real array,
remove
empty fields, duplicates, and sort the array.
Then i run a 2nd query, SELECT 'name' FROM employees WHERE id
IN($array)

This works fine, and thanks to someone else in this group, i managed to
combine
the names in the returned buddies-id with ther id, so i can use
$returned_buddy[4]; (and then i get the name belonging to id=4)

Well, what a story, and now the question: While i print out the names
in the first part, i need the names of their buddies together with
them. But since
i've done the printing already, i can't do it again.
How sould i do this?

I really hope this is clear, if not please ask.

Thanks.

[Colin McKinnon]

frizzle wrote:

Hi Group,

On my site, i have a number of employees in the DB,
each of the employees can work together with max 3 colleagues.

The table 'employees'looks like the following:
'id' INT(5) , 'name' VARCHAR(50) , 'buddies_id' VARCHAR(20)
1 , John , '21-2-90'
2 , Bruce , '45-78'
etc.
<snip> i need the names of their buddies together with
them. But since
i've done the printing already, i can't do it again.

Normalize your data.

C.

[frizzle]

i'm sorry?

[NC]

frizzle wrote:

On my site, i have a number of employees in the DB,
each of the employees can work together with max 3 colleagues.

The table 'employees'looks like the following:
'id' INT(5) , 'name' VARCHAR(50) , 'buddies_id' VARCHAR(20) 1 , John , '21-2-90'
2 , Bruce , '45-78'
etc.
Bad idea. You need TWO tables:

employees buddies
id INT(5) employee INT(5)
name VARCHAR(50) buddy INT(5)
While i print out the names in the first part, i need
the names of their buddies together with them. But since
i've done the printing already, i can't do it again.
How sould i do this?

If you change your data design, here's an idea:

SELECT e1.name AS name,
e2.buddy AS buddyname
FROM
(employees AS e1 LEFT JOIN buddies ON e1.id buddies.empoyee)
LEFT JOIN employees AS e2 ON buddies.buddy = e2.id
ORDER BY e1.id;

Cheers,
NC