Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

MySQL table linking

experts-exchange.txtI have 2 tables from which I am trying to construct a PHP output page that will display a column of images and beneath each image, a table containing rows of comments that pertain to that specific image. The URL and file name of each image is contained in tbl_files and the record number (an auto-increment field), date the comment is submitted, the image file name to which the comment pertains, and the image comment are contained in tbl_comments. I am able to display the images and comments on their own, respective pages but try as I might I cannot get the comments for each image to appear below each respective image and I suspect that the problem is my SQL "join" statement but try as I might, I have not been able to write a functioning statement. I have attached a file containing the code I used to write the 2 pages that display the data for each table as well as my unsuccessful attempt at joining them. Might someone correct my errors? Thanks.
0
lepirtle
Asked:
lepirtle
  • 3
  • 3
1 Solution
 
PortletPaulCommented:
$sql = "SELECT *
        FROM tbl_files, tbl_comments
        WHERE tbl_files.file = tbl_comments.file";

Open in new window

As far as I can see this is OK for SQL although I strongly encourage you to use ANSI join syntax, which would look like this:
$sql = "SELECT *
        FROM tbl_files INNER JOIN tbl_comments
        ON tbl_files.file = tbl_comments.file";

Open in new window


But the results are that image1 displays on the page but is repeated 3 times on the page with image2 also displaying but it is repeated 2 times. And none of the comments are displayed at all.
This is EXACTLY what those joined tables will do (in SQL), for each comment the image will be repeated.

In short, there is nothing actually wrong with your join; your expectation however does not match SQL reality.

So, I'll have to leave it to the PHP folks to sort out how best to handle the wanted output formatting.
0
 
mankowitzCommented:
Probably the best way to do this is to check in your loop to see if the image has changed. This should get you started.

<?php
$sql = "SELECT *
        FROM tbl_files, tbl_comments
        WHERE tbl_files.file = tbl_comments.file";
       
$result = mysql_query($sql);
$current_image = '';
WHILE($row = mysql_fetch_row($result))
   {
    if ($row[1] !== $current_image) {
if ($current_image != '') echo "<\table>";
echo "<p>".$row[1]."<br /><img src=\"".$row[0].$row[1]."\"/><br />".$row[1]." Comments:</p>";
echo "<table>";
$current_image = $row[1];
}
echo "<tr><td>".$row['eastern']."</td>";
echo "<td>"."&nbsp;".trim($row['file'])."</td>";
echo "<td>"."&nbsp;".trim($row['comment'])."</td></tr>";
   }

echo "</table>";
?>
0
 
lepirtleAuthor Commented:
PortletPaul, Thanks for the suggestion. It is good to know that I am on the correct path as for the SQL statement.

mankowitz. I think that the $current_image = ''; is probably not valid syntax as well as there is no matched curly braces for the WHILE keyword. So I tried the below modifications thinking they may work, however, the results are the same as my original code where image1 displays on the page but is repeated 3 times on the page with image2 also displaying but it is repeated 2 times. And none of the comments are displayed at all.  May I impose on you for further assistance? Thanks.

$sql = "SELECT *
        FROM tbl_files INNER JOIN tbl_comments
        ON tbl_files.file = tbl_comments.file";
       
$result = mysql_query($sql);
$current_image = $row[1];
WHILE($row = mysql_fetch_row($result))
   {
      if ($row[1]!== $row[1])echo "<table>";
      echo "<p>".$row[1]."<br /><img src=\"".$row[0].$row[1]."\"/><br />".$row[1]." Comments:</p>";
echo "<table>";
$current_image = $row[1];
echo "<tr><td>".$row['eastern']."</td>";
echo "<td>"."&nbsp;".trim($row['file'])."</td>";
echo "<td>"."&nbsp;".trim($row['comment'])."</td></tr>";
   }
echo "</table>";
?>
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mankowitzCommented:
I'm not sure what isn't working for you. The syntax should be valid. I don't have your tables, but the following works for me:

please note that I made a dummy database, but your actual database should work fine. Also note that I changed $row[0] and $row[1] to $row['folder'] and $row['file'] respectively.

$results = array(
      array('folder'=>".", 'file'=>'pic1.jpg', 'eastern'=>123, 'comment'=>'this is a comment'),
      array('folder'=>".", 'file'=>'pic1.jpg', 'eastern'=>234, 'comment'=>'this is a another comment'),
      array('folder'=>".", 'file'=>'pic2.jpg', 'eastern'=>522, 'comment'=>'this is a comment on another pic'),
      array('folder'=>".", 'file'=>'pic2.jpg', 'eastern'=>534, 'comment'=>'this is comment 2 pic 2'),
      array('folder'=>".", 'file'=>'pic2.jpg', 'eastern'=>523, 'comment'=>'this comment 3 pic 2'),
);
$current_image = '';
foreach($results as $row)
   {
    if ($row['file'] !== $current_image) {
            if ($current_image != '') echo "</table>";
echo "<p>".$row['file']."<br /><img src=\"".$row['folder'].$row['file']."\"/><br />".$row['file']." Comments:</p>";
echo "<table>";
$current_image = $row['file'];
}
echo "<tr><td>".$row['eastern']."</td>";
echo "<td>"."&nbsp;".trim($row['file'])."</td>";
echo "<td>"."&nbsp;".trim($row['comment'])."</td></tr>";
   }

echo "</table>";


The output I get looks like this:

pic1.jpg

pic1.jpg Comments:

123       pic1.jpg       this is a comment
234       pic1.jpg       this is a another comment
pic2.jpg

pic2.jpg Comments:

522       pic2.jpg       this is a comment on another pic
534       pic2.jpg       this is comment 2 pic 2
523       pic2.jpg       this comment 3 pic 2
0
 
lepirtleAuthor Commented:
Hi mankowitz,
I pasted your code into a document and can see that it is exactly how I want my page to look. It looks perfect!

Unfortunately I cannot get my data to look that way instead I receive a message "Warning: Invalid argument supplied for foreach() in line 19". I thought that perhaps the problem is in my SQL "Select" statement and the subsequent "$results" variable thinking that those 2 statements are not producing an array in the same manner as yours so I tried substituting
$results = mysql_fetch_array($sql);
as a replacement for $results = mysql_query($sql);
but that, too, threw errors.

The first 4 lines of applicable code I am using are:
$sql = "SELECT *
        FROM tbl_files INNER JOIN tbl_comments
        ON tbl_files.file = tbl_comments.file";      
$results = mysql_query($sql);

$current_image = '';
foreach($results as $row)
   {
    if ($row['file'] !== $current_image) {
            if ($current_image != '') echo "</table>";
echo "<p>".$row['file']."<br /><img src=\"".$row['folder'].$row['file']."\"/><br />".$row['file']." Comments:</p>";
echo "<table>";
$current_image = $row['file'];
}
echo "<tr><td>".$row['eastern']."</td>";
echo "<td>"."&nbsp;".trim($row['file'])."</td>";
echo "<td>"."&nbsp;".trim($row['comment'])."</td></tr>";
   }

echo "</table>";
?>
0
 
mankowitzCommented:
try to replace

foreach($results as $row) {

with

while ($row = mysql_fetch_assoc($result)) {
0
 
lepirtleAuthor Commented:
Hello mankowitz,
That's it exactly! You nailed it. Thank you so very much.

I am most grateful and appreciate your expertise and patience adding to my knowledge.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now