Select Question

[Cynthia_Blue]

Hello, I'm extremely new to PHP and MySQL. I run a dog website and an adding database functionality where folks can list themselves as a 'Rescuer', and add dogs they have up for adoption.

I have three tables I'm adding to the DB. A Rescuer Table, a Dog Table, and a Connect Table. There already is a member table.

When a person adds themselves as a new Rescuer, the DB assigns them a Rescuer_ID, unique key. Since a Member can have more than one Rescuer associated with them, I don't have the Member ID in that table. So, I would like to pull out that Rescuer_ID that was just assigned to them by the DB, and enter it into the Connect Table, which contains only a users Member ID and Rescuer ID that was just assigned.

Not sure how to do this. I assume I need to query that table for the Rescuer_ID that was just created, then insert it into the Connect table along with their Member_ID. However, how do I go about selecting only the most recent Rescuer_ID that was created? I do have an Entered Date field in there, so can most likely get the most recent one.

Is there a Where clause where I can get the most recent date? Hrm... I'm looking in the MySQL documentation for this too.

Thanks.

[goa103]

Hi Cynthia,

how do I go about selecting only the most recent Rescuer_ID that was created?

mysql_insert_id () returns the last inserted id

Not sure how to do this. I assume I need to query that table for the Rescuer_ID that was just created...

A Dog can be rescued by a Member
When a Member decides to rescue a Dog, it becomes a Rescuer.

So you need 3 tables: Dogs, Members and Rescuers. You don't need a Connect table. The Rescuers itself is the "Connect" table. A Rescuer is defined the its member id and the dog id, it wants to save.

Rescuer = MemberId, DogId

So if someone wants to save 101 dogs, the Rescuer will contain 101 entires, with the same MemberId but 101 different DogIds !

What do you think ? No more Connect table 🙂

I do have an Entered Date field in there, so can most likely get the most recent one

Don't use the Date, it doesn't UNIQUELY identify your Dogs, Members or Rescuers.

Is there a Where clause where I can get the most recent date? Hrm... I'm looking in the MySQL documentation for this too

Beginner question! You should be ashamed, kidding :p
You should chapter 3 "3 Tutorial Introduction" from the MySQL manual, it will really help you!

To get the most recent date, you have to ORDER your query. Check the SELECT statement syntax for more information:

SELECT * FROM Dogs ORDER BY InsertDate

Dogs = Id, InsertDate, Name

I hope it helps!
JM

[Cynthia_Blue]

Thank you. 🙂 I am still analyzing the layout of the tables. It is, as seems common now, based off a current message board layout... Invisionboard. So there already exists a Member table with various member information. I was going to use that one as it is, and try not to modify it. Then the rescuer table where a member can put more detailed info in about a rescue organization they may have, or just their contact info if they are an individual. 🙂

Thanks! I did get it working. 🙂