Solved

MySQL table linking

Posted on 2015-01-21
7
66 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
php help 34 58
ability to create nested list on the UI using PHP, HTML, Javascript 7 45
JQuery Search Filter 2 39
RAISERROR WITH NOWAIT 2 18
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

828 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