One to Many relationship: processing each value from a query

ssmitra

This is a tricky one, which I've spent two days trying to figure out without much sucesss, I hope you can help.

I have two tables, one called "Image" with two fields, "Img_ID" and "Image".

Table Image(Img_ID, Image)

And the other called "User_Img" which contains two fields, "User_ID" and "Img_ID".

Table User_Img(User_ID, Img_ID)

"User_ID" comes from the "User" table that displays information about a user.

"Img_ID" in table "Image" is a Primary key. "User_ID" and "Img_ID" in "User_Img" are both Foriegn Keys.

One user can have multiple, or many, images. What I'm trying to do is create a query that will search the "Image" table using the "Img_ID" and return the "Image" which is assicated with a "User_ID" from the "User_Img" table.

For example,

Table: Image
Img_ID Image
1 -----(image)
2 -----(image)
3 -----(image)

Table: User_Img
User_ID Img_ID
1 ------ 1
1 ------ 2
1 ------ 3

So, as you can see, User with ID=1 has three images, Img_ID's 1,2 and 3.

The query I'm trying to write would return each image in a variable (I used an array variable) so that it can be processed.

This is the code that I've written for this problem.

$get_img = array();
$get_img = mysql_query("SELECT img_ID FROM user_img WHERE userid='$uid'") or die(mysql_error());

for($i=0; $i<count($get_img); $i++) {

echo $get_img[$i];
}

Obviously, I don't just want to print the Img_ID's, but once I know I can get them I can then use them to search the Image table and return the Image/s.

It's a toughy so thank you in advance! 🙂

sneakyimp

try this?

SELECT i.Img_ID, i.Image FROM User_Img u
INNER JOIN Image i
ON i.Img_ID=u.Img_ID
WHERE u.userid='$uid'

Your post isn't very consistent about uppercase/lowercase of your table names and field names.

ssmitra

I've attempted to implement your SQL query, but without success.

The fundamental problem here is how to iterate down a column/field in a database, assuming that I have the correct pointer (dictated by the ID number) that points to each row.

I've tried using the following php code:

$sql_query_inArr = mysql_fetch_array($sql_query);

The idea of this code is to return the query into an indexed array so that I could iterate through the array and process each value. All it returns is '1', which refers to the first index value at $sql_query_inArr[0].

Any idea's on where I should be going with this implementation? (Apart from the rubber room!!)

Thanks again. 🙂

sneakyimp

rtfm!

http://php.net/mysql_query
http://php.net/mysql_fetch_array
http://php.net/mysql_fetch_assoc