Jump to content

Two tables with relationship how to query one record


Waddy

Recommended Posts

Hi,

 

I have two tables, clients and options. In MySQL I have created a relationship, primary key is ID in clients table, Foreign key = client_ID in table options.

 

I can insert using mysql_insert_id(); as the value for client_ID in options.

 

This all works fine. Client ID matches ID

 

My question is, how can i perform a select query to get the client details from table client and also the options values from the option table? Preferably using the matching ids... I have looked and tried many things and would appreciate a prod in the right direction, best method...

 

In a nutshell i want to look at the client details and also see the options they picked.

 

Link to comment
Share on other sites

Thanks, thats what i was looking for, couldnt find and easy example to follow and wasnt sure what it was called.  Kate helped me in IRC. (thanks Kate)

 

I have made the query for the two tables and it works fine. Did a three table query and it worked also.

 

Example:

 

$query = "SELECT client.ID, order.client_ID ".

"FROM client, order ".

"WHERE client.ID = order.client_ID";

 

Edit: added query for others benefit.

Link to comment
Share on other sites

Hi Waddy,

I have exactly the same problem, can you explain this more since I haven't figured it out yet ?

I am newbie to MySql, and I even have questions about relationships between tables :(

see, we have two tables: Clients, Services

and we want to show a specific client services matching by ID !

how we should relate these to tables to each other ?

and then how to query ?

 

Thank you in advanced  ;)

Link to comment
Share on other sites

Hi, I found it hard to find the information to achieve this, suprised me.

 

This is how i achieved the result.

 

On one (clients) table have an auto increment row (id), use this as your primary key. Create another table say 'orders', add a row called client_ID then you need to create a foreign key on this table linking to the client_id.

 

ALTER TABLE ORDERS

ADD FOREIGN KEY (client_id) REFERENCES CLIENTS(ID);

 

On your client insert grab the ID the client gets, use that to insert into order (client_ID), that will give both the same ID for when you use the above query.

 

"WHERE client.ID = order.client_ID";

 

The result gives you the client and order related to the client.

 

I hope that helps.

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.