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'look s 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]

Re: Oder of processing data

frizzle wrote:
[color=blue]
> 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'look s like the following:
> 'id' INT(5) , 'name' VARCHAR(50) , 'buddies_id' VARCHAR(20)
> 1 , John , '21-2-90'
> 2 , Bruce , '45-78'
> etc.
>[/color]
<snip>[color=blue]
> i need the names of their buddies together with
> them. But since
> i've done the printing already, i can't do it again.[/color]

Normalize your data.

C.

[frizzle]

Re: Oder of processing data

i'm sorry?

[NC]

Re: Oder of processing data

frizzle wrote:[color=blue]
>
> 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'look s like the following:
> 'id' INT(5) , 'name' VARCHAR(50) , 'buddies_id'[/color]
VARCHAR(20)[color=blue]
> 1 , John , '21-2-90'
> 2 , Bruce , '45-78'
> etc.[/color]

Bad idea. You need TWO tables:

employees buddies
id INT(5) employee INT(5)
name VARCHAR(50) buddy INT(5)
[color=blue]
> 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?[/color]

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