View Full Version : Simple MySQL get problem

11-14-2006, 02:03 PM
Hi there,

Basically heres the outline of my system just now:

"i have a system where i am adding clients to a database.

Some clients are linked to other clients if they are married (client_rel = married) (client_rel_id = id of client their married to)

i want to pull out a list of clients but if client_rel_id is not NULL then i want to display the string as client id X & client id Y

so for example

James Brown
Adam Griffin
Steve Staunton
Billy Elliot & jennifer Ellison
Frank Swot

but at the moment it does this:

James Brown
Adam Griffin
Steve Staunton
Billy Elliot & jennifer Ellison
Jennifer Ellison & Billy Elliot
Frank Swot

Im sure its really simple but i just cant figure out how to stop php displaying the married record twice.
Thanks in advance for anyone who can point me in the right direction :)

Andrew Robins

11-14-2006, 03:52 PM
There is probably an even more efficient way to make a database to work with related information. However, I'm a little behind on my database tech right now.

So, basically you have a list of names, a rel field, and a rel_id field right? Something like this:

id name rel rel_id
1 Anthony married 3
2 John single null
3 Elizabeth married 1
And the output you want is:

Anthony & Elizabeth

You can loop through your results once, using the values to populate a multidimensional array. (ie myRecords(0)(ID), myRecords(0)(NAME), etc...)
Then loop through your array, check for the rel_id field, and if it exist call that record from the array. Then you can set that record's value for rel_id to -1, and the rel_id record it pointed to, to -1.

So now your array loop has reached the spouse's record, but his/her rel_id field has been reset to -1, and you know not to print records with a rel_id of -1.

Make sense?

11-14-2006, 04:18 PM
Yeh thanks that makes sense. I think i remember doing somethign similar a few years ago when i was at school with Visual basic. However i have never used multi-dimensional arrays in php/mysql before so im a bit lost.

Do you know of a small excerpt of code anywhere which would enlighten me?

My table is called client and the columns are


These will be presented in a drop down box. When you select a single person their client_id will be carried forward and an edit page will be populated with the client id's info.
When you select a couple i will carry both client id's forward and populate two sets of edit boxes with the client id's infos.

Cheers for theory anyway mate, really appreciate it :)