How to link one db record to another...

[Otis]

Ok so I'm fairly new to php and I'm astonished I've managed to get this far. Anyway I can't seem to figure out how to link progeny to parent or parents to progeny.

 

For this particular site I have cats registered in the DB with registratonID, name,tom, and queen. Now on the cat showcase template all this information shows up as it should through the echos. Now since this particular project it for displaying pedigees I want the offspring to link back to the parents via name but I can't wrap my brain around how. For example: I have 'CVC Fools Gold' this kittens parents are $tom - 'CVC Lanister Gold' and $queen - 'Mamsies Legaly Blond' I would like the ecos for $tom; and $queen; to show up as links to those cats.

 

If at all possible I would also like to be show a few steps in the right direction for showing the progeny of the two breedings cats on their pages so that one can look at Lanister Gold and see all of the cats listed where he is listed as $tom

[kicken]

Your tom and queen fields should be INT fields that hold the ID number of a record for the appropriate cats.  Then you just use an INNER JOIN in your query to join the rows together.

 

eg:

SELECT
    progeny.registrationId as catId,
    progeny.name as catName,
    tom.registrationId as tomCatId,
    tom.name  as tomCatName,
    queen.registrationId as queenCatId,
    queen.name as queenCatName
FROM cats progeny
INNER JOIN cats tom ON progeny.tom= tom.registrationId
INNER JOIN cats queen ON progeny.queen = queen.registrationId
WHERE
    progeny.registrationId = 123

 

 

[Otis]

Sorry it took me so long to reply, I have been quite sick.

 

Ok so my DB is set up like so:

id, name, origin, breed, gender, color, pattern, tom, tomsqueen, tomstom, queen, queensqueens, queenstom, yob

 

I also have a form where the user puts in this information in order to submit said cat into their

 

The tom and queen fields (as well as grandparents) are text (varchar) by request. Is it still possible to link $tom, and so on, so other cats in the DB if the ($name)s are identical?

 

If not is there a way to automatically give $tom an existing ID (as a new field $tomid) upon the progeny's input into the DB from the entry form assuming names are identical?

If this is an option what about a cat that doesn't exist in the DB? would I be able to leave them with no ID and no link to an empty page?

 

Or (other then allowing entry of the cats ID number into the DB submit form) is there any other options?

I apologize if I am being difficult. I am trying to both learn more about php, and please my partners requests at the same time.

[kickstart]

Hi

 

It is possible to do a JOIN on VARCHAR fields, just not as efficient and not really necessary.

 

What you should have is the cats details with an INT primary key. Other details fields migth be one for each of the parents, but NOT storing the parents names (you can easily get these).

 

This way you take the cat table, you JOIN it against itself where the queen = ID and also JOIN it against itself where the tom = ID (this way you can get the queens and the toms name from their rows). You can do this more times to get the grand parents as well if you want.

 

If you possibly do not have a cats details for the parent then leave the queen / tom ID field as NULL and you use a LEFT OUTER JOIN. This way if there is no matching field the base row will still be brought back but the fields from the JOINed table will be NULL.

 

If you want to enter the details you have a drop down select list where the display field is the name but the VALUE on the drop down is the ID field.

 

All the best

 

Keith