0

What I have here is a bulletin board in PHP with the ability to post comments and images. I am able to store my comments and my images to the database. My question involves 2 of my SQL tables, one for stored posts and the other table for storing images. When I echo out my table of posts I would like to echo out the corresponding image inside the post body. I have set up my tables to match with the column attribute of DATETIME but I fail to comprehend how to access my images' table while I am in a foreach loop with my posts table. I have included the code for my index.php file and my getImage.php file. Help would be greatly appreciated.

I made an update to my getImage file in the $ID = $_GET['id']; where 'id' needed to be lower case. I am now getting some images displayed in my table but some only partial. I think I am on my way. I know I need an if statement to determine if there is an image in the post before displaying. thanks for all the help.

<?php

include('connect.php');
$query = 'SELECT * FROM forumPosts LEFT JOIN PostImages ON 
forumPosts.DATETIME = PostImages.ImageDATETIME ORDER BY replyIndex 
ASC';

$statement = $db->prepare($query);
$statement->execute();
$posts = $statement->fetchAll();

$statement->closeCursor();
echo "<table>";
foreach ($posts as $post){
if ($post['post_type'] == "r"){
    $post_id = $post['post_id'];
    echo "<tr bgcolor='beige'><td>reply</td><td>". 
    $post['post_title'] . "</td ><td>". $post['post_body'] . "<img 
    src='getImage.php?id=".$post['ID']."'>". "</td><td>". 
    $post['DATETIME']. "</td><td>". $post['replyIndex']. "</td>
    <td>".$post['post_type']."</td>";

?>  

<?php

include('connect.php');


 $ID = $_GET['id'];

 $query = "SELECT * FROM PostImages WHERE ID=:ID";
 $statement = $db->prepare($query);
 $statement->bindvalue(':ID', $ID);
 $statement->execute();
 $row = $statement->fetch(PDO::FETCH_ASSOC);


  header("Content-type: image/jpeg");
  echo $row['image'];

 ?>
Matt Macy
  • 141
  • 1
  • 2
  • 13
  • Don't post pictures of your database. It's very difficult to read. Instead show the table schema using https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html – Reactgular Dec 16 '17 at 02:42
  • The structure tables isn't clear and I think isn't complete. – Alejandro Dec 16 '17 at 03:25

1 Answers1

1

You probably want to use the JOIN syntax in your SELECT query. Something like:

$query = 'SELECT * FROM forumPosts LEFT JOIN postImages ON forumPosts.DATETIME = postImages.imageDATETIME ORDER BY replyIndex ASC'

Then you can get the image data from your foreach loop using $post['name'] and $post['image'].

The LEFT JOIN will still return all rows from the first table (forumPosts) even if there is no match on the second table (postImages).

Mysql Join Syntax

Jay A. Little
  • 3,239
  • 2
  • 11
  • 32
  • I am thinking about this. Don't immediately understand what the will do. – Matt Macy Dec 16 '17 at 04:05
  • Your query only selects the forumPosts table, so it will not return anything from your postImages table. By using JOIN, you are selecting all from both tables and joining them together wherever the DATETIMEs from both tables match. The result will be a single table with full data from both tables properly joined. So you can get the postImages data the same way you get the forumPosts data: `$post['name']` and `$post['image']` – Jay A. Little Dec 16 '17 at 04:12
  • I am understanding. Will only rows that match be displayed? Sorry for my ignorance. We study joins in school but I never have implemented. – Matt Macy Dec 16 '17 at 04:16
  • By using a `LEFT JOIN`, all rows from the first table will be returned, even if it does not have a match on the second table. – Jay A. Little Dec 16 '17 at 04:21
  • I updated my query to perform the LEFT JOIN and it should be echoing out the image through post['image'] but its not. I have included an screen grab of that above. My query shows purple text which I usually think means bad. Maybe someone can tell me where I went wrong. thanks. – Matt Macy Dec 16 '17 at 16:32
  • Try this answer for how to echo an image saved as a BLOB: https://stackoverflow.com/questions/7793009/how-to-retrieve-images-from-mysql-database-and-display-in-an-html-tag – Jay A. Little Dec 16 '17 at 16:40
  • As I understand it that answer uses to post the image file with the other code being for the getImage.php file itself. I don't think id=1 on the end of that file will work for me because I want the image file that corresponds to the $post that I am in at the time. Excuse me I am trying to understand this. – Matt Macy Dec 16 '17 at 21:07
  • You do know that specific image's ID because it comes with your LEFT JOIN result. Just imagine both tables together as one. So post['ID'] is your image table's ID. So echo ''; – Jay A. Little Dec 17 '17 at 01:31
  • I am so confused. The PHP gods have made this very difficult. I am getting the following error: Parse error: syntax error, unexpected 'ID' (T_STRING), expecting ',' or ';' in /Applications/XAMPP/xamppfiles/htdocs/thisoneisbackedup/index.php on line 49 – Matt Macy Dec 17 '17 at 20:11
  • Shouldn't this work? "". $post['post_body'] . ''. "" I am getting an error. Please see the picture added of getImage.php – Matt Macy Dec 17 '17 at 23:06
  • when following the specific syntax ("". "") I get Use of undefined constant post - assumed 'post' – Matt Macy Dec 17 '17 at 23:23
  • Lol sry for your confusion but yeah you've got a few problems. – Jay A. Little Dec 18 '17 at 01:15
  • You are better off posting the code in snippets in the question than posting images. Then we can edit it all and see other problems and leave you comments in the code. For example the error says line 49, but I cant see what is on line 49. So I can only take my best guess at the error, when there might be something earlier. – Jay A. Little Dec 18 '17 at 01:26
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161410/discussion-between-jay-a-little-and-matt-macy). – Jay A. Little Dec 18 '17 at 01:28