Solved

MySQL table linking

Posted on 2015-01-21
7
62 Views
Last Modified: 2015-01-23
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
Comment
Question by:lepirtle
  • 3
  • 3
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40563543
$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
 
LVL 24

Expert Comment

by:mankowitz
ID: 40563547
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
 

Author Comment

by:lepirtle
ID: 40563582
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 24

Expert Comment

by:mankowitz
ID: 40563596
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
 

Author Comment

by:lepirtle
ID: 40564286
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
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 40565690
try to replace

foreach($results as $row) {

with

while ($row = mysql_fetch_assoc($result)) {
0
 

Author Closing Comment

by:lepirtle
ID: 40566920
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now