PHP Query help!!

[mattyj10]

What I am wanting to do is display all the accounts of the customers in my database.
The problem is I have a separate user, account, and usertype tables.

The usertype table is linked to the users table with the foreign key fk1_user_type
and the accounts table is linked to the users table with fk1_user_id.

To find out whether or not the record in the account table belongs to a customer you have to use the foriegn key fk1_user_id to match it with the record in the users table from where you can identify the account type by the foreign key fk1_user_type.

My problem is how do I get a query to do this as there are multiple customer accounts in the account table.

I don’t know where to start at minute I’ve got
SELECT * FROM users WHERE fk1_user_type = 'customers'

Which returns multiple rows but how can I match these rows using there user_id to the fk1_user_id in the account table to display all the accounts belonging to the customers.

Any Help Appreciated Thanks 😃

[leatherback]

You are looking fro something like the query below. As I do not know the individual colum names, please ensure the right colum names. What it does: It SELECTS everythin (*) from the table USERS which is (left) JOINED to the ACCOUNTS table using the values in the ID colum of users and the FK1_user_id in accounts to match records. You do the same to link account and account type on their linking colums. And you want all records where FK1_USER_TYPE = 'coustomers'

SELECT * FROM users
left join accounts on USERS_TABLE_ID = fk1_user_id
left join account_type on ACCOUNTS_TABLE_ID = ACCOUNT_TYPE_ACCOUNT_FK
WHERE fk1_user_type = 'customers'

[ahundiak]

So the database looks roughly like this:
account
..account_id
..user_id
..name

user
..user_id
..user_type_id

user_type
..user_type_id

SELECT account.name
FROM account
LEFT JOIN user ON user.user_id = account.user_id AND user.user_type_id = 'Customer';

Spend some time learning about joins. Makes these sorts of queries easy. Also suggest you get this working using a console before adding it to your web app.

[mattyj10]

thanks for quick replies guys much appreciated.

Yeh database looks like this

Account Table
account_id
first_name
surname
address
email
telephone
fk1_user_id

Users
user_id
username
password
fk1_user_typeid

User Type
user_typeid
user_type

I'll give the joining thing ago, thanks

[Roger_Ramjet]

"the joining thing" is the first, most useful and most essential thing you have to learn if you want to use a relational database. JOIN SYNTAX