CODEWITHSUNDEEP
phpmysql
Saulius Antanavicius
Saulius Antanavicius

Reputation: 1381

Mysql relationships and getting linked data

I have been doing this for a while now, via some php, first lets say we have two tables:

Users
user_id name email

Images 
image_id user_id url

user_id and user_id from images table would be linked with a relationship.

Now what I would do is select the user by their Id, check if the user is found, if so then make another query to images table, and check for num rows and loop through the return, is there a function that I could use that would allow me to just select the user and all the images that are linked to the user without doing a joint query.

Thank you for any help

Upvotes: 3

Views: 2281

Answers (3)

Shoan
Shoan

Reputation: 4078

You'll have to use a join to retrieve data from multiple tables in a single query.

The foreign key relationships enforce constraints. Ex: You can't insert a record into Table A referring to a key in Table B without the record actually being in Table B.

Upvotes: 0

Caleb
Caleb

Reputation: 141

This can be done in one query rather than two by using an inner join to get your result set.

    $sql = "SELECT u.user_nid, i.url
    FROM tbl_user u
    INNER JOIN i.user_nid = u.user_nid
    WHERE user_nid = ?"

With this query you will receive a list of the users images and if there are no images returned or the user does not exist, than you will have a row return of zero.

Upvotes: 0

JYelton
JYelton

Reputation: 36512

When you say "without doing a joint query" I think you mean "without doing two queries."

In fact, what you want is probably a LEFT JOIN. The idea is that you select users from the user table matching some ID, and LEFT JOIN the images table. The left join will give you null values if no images exist for the user. If you use a normal join, the fact that no matching records exist in the images table will result in no rows returned.

Here is an example:

SELECT u.name, u.email, i.url
FROM Users u
LEFT JOIN Images i ON (i.user_id = u.user_id)
WHERE u.id = @SpecificUserID;

Assuming the user id is found and there are some images for that user, you will get a result that looks like this:

name    email    url
-----   -----    -----
John    [email protected]  abc.jpg
John    [email protected]  def.jpg
John    [email protected]  ghi.jpg

Now as you can see, the name and email values keep repeating. You get a unique image url for each row and the matching username and email.

If you only select one user at a time, this is simple to process in a loop. On the first iteration read all three values. On subsequent iterations just read the url, adding it to your list or array.

Here is a useful tutorial on joins: Understanding JOINs in MySQL and Other Relational Databases

Upvotes: 1

Related Questions