?
Solved

MySQL table linking

Posted on 2015-01-21
7
Medium Priority
?
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 49

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
Suggested Courses

718 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