Solved

MySQL table linking

Posted on 2015-01-21
7
61 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

759 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

23 Experts available now in Live!

Get 1:1 Help Now