Linking Image to User in seperate table

[reandre68]

I am building a dynamic Image gallery where users can upload images to their profile for customers to view. The images are stored in a file with the path to the image stored in a different table than the details for the user. So far I have managed to do the image upload and link the user ID in the Images table to the ID in the details table so that all the images associated with that user is linked to his record in the details table. My problem comes in when joining these two tables for one recordset. I have managed to join them, only that each record with more than one image associated with it displays more than once. So if user 1 uploaded two images, his record comes up twice in the results. Any suggestions as to how I can force the query to display every record only once no matter how many images that user uploaded?

Hope this makes sense. Thanks

Reandré

[bregent]

>Hope this makes sense.

It really doesn't make sense, at least with the info you've provided so far. If you join tables with a one-to-many relationship, then you are going to get duplicated results from the user table.

>how I can force the query to display

>every record only once no matter how

>many images that user uploaded

You need to tell us more. If you are joining users to images, then I assume you must want image data in the recordset - right? If you only want one record for each user, then which image do you want?

[reandre68]

If you join tables with a one-to-many relationship, then you are going to get duplicated results from the user table.

So there is no way to get only one result per user?

Sorry about not making sense. I'm not sure how to put it or exactly what information you will need, but let me give it a shot:

I have two tables- 1.Details and 2.images

The details table is self-explanatory. A user can log in and  upload images to his gallery with the filename/path stored in the database - in the images table. The images table has 3 columns, picID, userID and filepath. The user ID is the ID of the user that uploaded the images so all the images with his user ID is all the images he uploaded and I want to display all those images accordingly with each user's ID. When a client does a search, I get more than one result per user at this time, because there is more than one image per user.

What I want to do, is to create a recordset where all the fields are present, including image data, but without displaying every record twice because there is more than one image linked to that user. I don't want any one specific image, I want all the images available for that user. I have created a recordset joining the 2 tables like this. eg:

SELECT details.id, details.name, images.`path` 
FROM details, images 
WHERE images.usrID = details.id;

Something like that. SELECT DISTINCT won't work obviously since every row is different because of the image path.

Hope this makes better sense. Please let me know if there is something specific you need. If there is a better way to do this that you know of, please let me know.

[David_Powers]

reandre68 wrote:

I don't want any one specific image, I want all the images available for that user.

When looping through the results, you need to suppress the display of duplicate names, but continue to display the images. You do this by creating a variable to store the previous name, and comparing the value with the current name. Display the name only if it's different from the previous one.

You start by initializing the variable as an empty string outside the repeat region, and assigning the current name to it each time the repeat region loop runs. It looks like this:

<?php
// initialize a variable to store the previous name
$previous = '';
do { ?>
<tr>
   <td><?php if ($row_rsName['name'] != $previous) { 
     echo $row_rsName['name'];
     // store the current name in the $previous variable
     $previous = $row_rsName['name'];
} else {
  echo ' ';
} ?></td>
    <td><img src="<?php echo $row_rsName['path']; ?>"></td>
  </tr>
<?php } while ($row_rsName = mysql_fetch_assoc($rsName)); ?>

[reandre68]

Hi David,

thanks for your help.

From what I understand of the suggestion you made for suppressing the display of duplicate results, this will only suppress the display of duplicate images? Forgive me if I'm wrong, could you then please explain the function to me in more detail? Let me try to lay out what I'm trying to achieve:

I am trying to make a master/detail page set. On the master page, the results are shown as I have mentioned, duplicated if there is more than one image per name stored in the images table. That is also the reason I am trying to join these two tables, so that when the ID carries over to the detail page, the userID from the images table will  display the images uploaded by that user.

The detail table will have data like this:

ID Name

1  Ben

2  John

3  Sarah

The images table like this:

ID userID imgPath

1  1         /Images/image1

2  1         /Images/image2

3  3         /Images/image3

So on the master page the results from the detail table will be displayed. I will link to the detail page from the name and unique id will be carried over to the detail page by the ID. On the detail page, if the userID matches that of the ID from the detail table, all the images with that user's ID must be displayed. The problem is that on the master page, if user ID 1 (Ben) has uploaded 2 images, the results on the master page will look like this:

ID Name

1  Ben

1  Ben

3  Sarah

I want it to display every record only once like:

ID Name

1  Ben

3  Sarah

Even though Ben has 2 images.

I have a hard time explaining myself so hope this makes sense to you? Please let me know if you need anything else?

Regards

[David_Powers]

reandre68 wrote:

From what I understand of the suggestion you made for suppressing the display of duplicate results, this will only suppress the display of duplicate images?

No. It does exactly what you want. Try the code and see.

[reandre68]

Yeah you're right of course. It does work, to some extent.

Now I get results like this:

ID Name

1  Ben

1 

2  John

3  Sarah

I successfully removes the duplicate names but the record is still there, only the name doesn't show.

Isn't there an easier way to do this? I still want to store the images like I do, but without the hassle I'm having now.

Right now my recordset query looks like this:

SELECT details.id, details.name

FROM details LEFT JOIN images

ON images.usrID = details.id

Is there a way that I can just display the images for a user without joining the tables and sitting with this problem?

[David_Powers]

reandre68 wrote:

I successfully removes the duplicate names but the record is still there, only the name doesn't show.

Well, if you don't want the ID to show, hide that too. You can't solve every problem with a SQL query, but you can control the output through conditional logic.

It's not clear how you're planning to display the photos. The solution I gave you is the simplest to implement, because it suppresses the repeated information, but puts the new image in a new table row. If you want to put the images alongside each other, the coding is a bit more complex, but it's not impossible.

The following code displays the images in a single cell:

 <tr>
   <td>
   <?php 
   $previous = '';
   $first = true;
   do {          
     if ($row_rsName['id'] != $previous) {
       // if not the first item, close row and open new one
       if (!$first) {
         echo "</td></tr>\n<tr><td>";
       }
       // display the ID and name
       echo $row_rsName['id'];
       // store current ID as previous
       $previous = $row_rsName['id'];
       echo '</td><td>' . $row_rsName['name'] . '</td><td>';
     } 
     echo '<img src=" . $row_getPlaces['name'] . '">'; 
     // reset $first at the end of the first loop
     $first = false;
   } while ($row_getPlaces = mysql_fetch_assoc($getPlaces)); ?>
   </td>
 </tr>

[reandre68]

I understand what you mean, yes. I am building a website with a travel directory. A guest-house for example should be able to upload images of his establishment to my website. When a client does a search for guest houses, a list of guest houses will appear. That will be the master page. When you click on an establishment name, you go to his "profile" which is the detail page. It is there I want to display the images, not next to the name on the master page. I have no problem with uploading and handling one image per establishment. I am trying to figure out how I can manage all those images for an establishments' "image gallery".

How does all these social websites handle all the images? Like on facebook your images are linked to your profile. When I click on your profile on facebook, all the images you have uploaded are available to view. So obviously all your images are linked to your facebook user ID in some way. I want to achieve something like that. I can't find anything on google or anywhere else for that matter.

Hope this makes better sense?

[David_Powers]

reandre68 wrote:

It is there I want to display the images, not next to the name on the master page.

In that case, just add a WHERE clause to your query:

SELECT details.id, details.name 
FROM details LEFT JOIN images 
ON images.usrID = details.id
WHERE images.usrID = 12

That selects the details and images for usrID 12. You pass the value through a query string like this:

<a href="details.php?usrID=<?php echo $row_rsName['usrID']; ?>">See details</a>

You use the value passed through the query string as a URL parameter in the SQL query.

[reandre68]

So in that case I will have to create a page for each establishment manually?

I just had a look at my facebook profile to try and learn something from this. My url for my facebook profile looks like this:

http://www.facebook.com/profile.php?id=762039663

While my images URL look like this:

http://www.facebook.com/profile.php?id=762039663&v=photos

I don't want to copy facebook's methods, but that only proves that there is a simpler way to do this. Do you maybe have any idea how I can do that?

Sorry I'm not very clued up with these things as you have probably noticed. lol. But I appreciate your help and patience very much.

Thanks

[reandre68]

I have decided on another approach so this thread probably no longer qualifies as I will only use one table. I will explain anyway.

I put together a small function that will create a unique directory for each user's images, then a function with some php and javascript that calls the folder and displays all the images in that users' image directory. I store the path to the images directory in the table along with all the other details. That way there are no issues with duplicate results etc. and only one entry for each establishment to work with, much much easier.

Thanks for all the help anyway David. I appreciate it a lot.

[David_Powers · Accepted solution]

reandre68 wrote:

I don't want to copy facebook's methods, but that only proves that there is a simpler way to do this.

What makes you think that the back end of Facebook is simple? What makes a site like Facebook successful is making the front end simple for users, while hiding the complexity behind the scenes.

One of the difficulties of providing help in a forum like this is that there's no way of knowing the other person's level of knowledge or how far down the road they have gone with a particular strategy. It sounds as though you're just beginning with PHP and database-driven sites. It's a challenging and often frustrating time. I remember the problems I had trying to get my head around the best way to structure a database. What makes it difficult is there's no single "right" way to do it. Each database is different. You might develop a database that works just fine; but when you decide to add extra functionality, you discover the structure is too rigid. That happened to me with a major project I worked on several years ago.

Some of the best advice I received about working with databases was to spend a lot of time planning not only what you want the database to do now, but also what you might want it to do in future. The coding takes only a small proportion the total time spent on designing the project. A book that helped me greatly was "Database Design for Mere Mortals". The author's writing style is a bit tedious, but the information is rock solid.

Good luck with your redesign.

[reandre68]

Thanks for the good luck David.

I took your advice and went back to the drawing board. I decided to try my first strategy again from scratch where I store the image path in a different database table and then link that image to a user with the userID. I was just wondering if it could work if I put a small if function on my details page to display a users' images like this:

<?php if ($row_Recordset1['usrID'] = $row_DetailRS1['id'])
  echo "<img src="$row_Recordset1['path']"/>";
?>

That's the exact code I tried and it gave me an error like this:

Parse error: syntax error, unexpected T_VARIABLE, expecting ',' or ';' in C:\public_html\TravelTo\detail.php on line 135

Of the 3 lines of code I provided above the second line is line 135, and I don't really understand what the problem is since I put the ';' there. I'm obviously missing something crucial.

Can you please tell me what I'm doing wrong and if a function like that will work at all? I'm not joining the 2 tables like I did before and was hoping to achieve the results I wanted just by adding a function like that to display all the images in the table where the userID matches that of the user's id in the details table.

P.S I had a look at your website and your books. Which one of your books would you recommend to a complete novice like myself?

Regards

[Guest]

<?php if ($row_Recordset1['usrID'] = $row_DetailRS1['id'])
  echo "<img src="$row_Recordset1['path']"/>";
?>

escape the quotes in your code and concatenate the variable:

<?php if ($row_Recordset1['usrID'] = $row_DetailRS1['id'])
  echo "<img src=\".$row_Recordset1['path'].\"/>";
?>

or use this:

<?php if ($row_Recordset1['usrID'] = $row_DetailRS1['id']) { ?>
  <img src="<?php echo $row_Recordset1['path']; ?>" />
<?php } ?>

[reandre68]

<?php if ($row_Recordset1['usrID'] = $row_DetailRS1['id']) { ?>
  <img src="<?php echo $row_Recordset1['path']; ?>" />
<?php } ?>

It works if I use the above so I'm on the right track I believe. Only problem still is that it displays an image with userID 9 even though I'm on a user with id10.

What is missing from this function to let only the images where the id matches that of the userID display?

[Guest]

Please provide the query for Recordset1 in order to determine your current issue.

[reandre68]

$query_Recordset1 = "SELECT * FROM images";

This the query you are talking about?

[Guest]

yep. There's no WHERE statement to determine the filter of the query.

EDIT:

Whoops, I think it's your DetailRS1 query that needs to filter the URL parameter. You don't need two recordsets in this case, just one recordset based off the URL parameter containing the id of the user whose images you want to display.

$query_DetailRS1 = "SELECT * FROM images WHERE id = URL_Parameter";

[reandre68]

The detail page will be used to display all the info on a specific establishment, so my detailRS1 Recordset is the actual detail recordset and not the images recordset. Not sure if it makes sense? Let me paste the code here:

$maxRows_DetailRS1 = 10;
$pageNum_DetailRS1 = 0;
if (isset($_GET['pageNum_DetailRS1'])) {
  $pageNum_DetailRS1 = $_GET['pageNum_DetailRS1'];
}
$startRow_DetailRS1 = $pageNum_DetailRS1 * $maxRows_DetailRS1;

$colname_DetailRS1 = "-1";
if (isset($_GET['recordID'])) {
  $colname_DetailRS1 = $_GET['recordID'];
}
mysql_select_db($database_test, $test);
$query_DetailRS1 = sprintf("SELECT * FROM name WHERE id = %s", GetSQLValueString($colname_DetailRS1, "int"));
$query_limit_DetailRS1 = sprintf("%s LIMIT %d, %d", $query_DetailRS1, $startRow_DetailRS1, $maxRows_DetailRS1);
$DetailRS1 = mysql_query($query_limit_DetailRS1, $test) or die(mysql_error());
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);

if (isset($_GET['totalRows_DetailRS1'])) {
  $totalRows_DetailRS1 = $_GET['totalRows_DetailRS1'];
} else {
  $all_DetailRS1 = mysql_query($query_DetailRS1);
  $totalRows_DetailRS1 = mysql_num_rows($all_DetailRS1);
}
$totalPages_DetailRS1 = ceil($totalRows_DetailRS1/$maxRows_DetailRS1)-1;

mysql_select_db($database_test, $test);
$query_Recordset1 = "SELECT * FROM images";
$Recordset1 = mysql_query($query_Recordset1, $test) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
//head
//body
<div id="content"> 
  <img src="Images/heading_index.png" alt="Search, Browse and find &lt;b&gt;Accommodation in South Africa&lt;b&gt;" width="735" height="33" hspace="80" vspace="10" />
  <p><?php if ($row_Recordset1['usrID'] = $row_DetailRS1['id']) { ?>
  <img src="<?php echo $row_Recordset1['path']; ?>" />
<?php } ?></p>
  <table border="1" align="center">
  <tr>
    <td>id</td>
    <td><?php echo $row_DetailRS1['id']; ?></td>
  </tr>
  <tr>
    <td>name</td>
    <td><?php echo $row_DetailRS1['name']; ?></td>
  </tr>
  <tr>
    <td>province</td>
    <td><?php echo $row_DetailRS1['province']; ?></td>
  </tr>
  <tr>
    <td>city</td>
    <td><?php echo $row_DetailRS1['city']; ?></td>
  </tr>
  <tr>
    <td>type</td>
    <td><?php echo $row_DetailRS1['type']; ?></td>
  </tr>
  <tr>
    <td>price</td>
    <td><?php echo $row_DetailRS1['price']; ?></td>
  </tr>
  <tr>
    <td>website</td>
    <td><?php echo $row_DetailRS1['website']; ?></td>
  </tr>
  <tr>
    <td>email</td>
    <td><?php echo $row_DetailRS1['email']; ?></td>
  </tr>
  <tr>
    <td>fax</td>
    <td><?php echo $row_DetailRS1['fax']; ?></td>
  </tr>
  <tr>
    <td>landline</td>
    <td><?php echo $row_DetailRS1['landline']; ?></td>
  </tr>
  <tr>
    <td>cell</td>
    <td><?php echo $row_DetailRS1['cell']; ?></td>
  </tr>
  <tr>
    <td>contact_name</td>
    <td><?php echo $row_DetailRS1['contact_name']; ?></td>
  </tr>
  <tr>
    <td>caontact_surname</td>
    <td><?php echo $row_DetailRS1['caontact_surname']; ?></td>
  </tr>
  <tr>
    <td>profile_pic</td>
    <td><?php echo $row_DetailRS1['profile_pic']; ?></td>
  </tr>
  <tr>
    <td>username</td>
    <td><?php echo $row_DetailRS1['username']; ?></td>
  </tr>
  <tr>
    <td>password</td>
    <td><?php echo $row_DetailRS1['password']; ?></td>
  </tr>
</table>
  master
</div>

Hope you can make more sense of what I am trying to do. Not sure how to explain it. This is only a test page but all the basic information is there