think tank forum

so I'm trying to write some simple blog software that will allow me to make entries into the posts table and upload pictures which are associated with posts. the problem I'm having is with displaying posts with their related pictures. this is what I've got right now.

http://www.thehomerow.net/~brain/dev/2/

as you can see, "Post 2 body." occurs twice, when the behavior I want is for both of the pictures associated with post 2 to be listed under it.

here's my code.

mysql query:

SELECT * FROM posts LEFT JOIN pictures ON posts.id=pictures.post_id;

and the output at pastebin.ca: http://pastebin.ca/465344

relevant php:

$posts_query = 'SELECT * FROM posts LEFT JOIN pictures ON posts.id=pictures.post_id;';
        $posts_result = mysql_query($posts_query);
          if(!$posts_result) {
            die(mysql_error());
          }
        while($posts_row = mysql_fetch_assoc($posts_result)) {
          echo
        "<p>
          $posts_row[title]<br />
          $posts_row[body]<br />
          <a href=\"pictures/$posts_row[filename]\"><img src=\"pictures/thumbnails/$posts_row[filename]\" /></a><br />";
        }

I basically just don't know where I need to focus my efforts. is that as good as the mysql query is going to get? is this something I need to sort out in the php? any hints of the right direction would be appreciated. thanks, guys.

that is what should be happening.

you need to figure out what you want to happen when you have two pictures with one post.

you can do something like this:

<?php
$sql = "SELECT * FROM posts LEFT JOIN pictures ON posts.id=pictures.post_id";
if (!$posts_result = mysql_query($sql)) die(mysql_error());
unset($prev_post_id);
while ($posts_row = mysql_fetch_assoc($posts_result)) {
    if ($posts_row["id"] != $prev_post_id) {
        echo "{$posts_row["title"]}<br />{$posts_row["body"]}<br />".
             "<a href=\"pictures/{$posts_row["filename"]}\">".
             "<img src=\"pictures/thumbnails/{$posts_row["filename"]}\" /></a><br />";
    } else {
        echo "<a href=\"pictures/{$posts_row["filename"]}\">".
             "<img src=\"pictures/thumbnails/{$posts_row["filename"]}\" /></a><br />";
    };
    $prev_post_id = $posts_row["id"];
}
?>

note: this is untested, but it should work and you get the point.

p.s. i rewrote the way that most people like their code. hope you find the little coding tips helpful.

yeah, I had a feeling I was going to have to mess with more conditional stuff in the php. thanks for pointing me in the right direction, this shouldn't be too tough now!

and what do you mean you rewrote it?

well, you should have quotes around associative keys ($a["bc"] instead of $a[bc])

and i made the "if error/then die" conditional shorter, i suggest it.

in fact, that's stupid. this is better:

<?php
$sql = "SELECT * FROM posts LEFT JOIN pictures ON posts.id=pictures.post_id";
if (!$posts_result = mysql_query($sql)) die(mysql_error());
unset($prev_post_id);
while ($posts_row = mysql_fetch_assoc($posts_result)) {
    if ($posts_row["id"] != $prev_post_id) {
        echo "{$posts_row["title"]}<br />{$posts_row["body"]}<br />";
    }
    echo "<a href=\"pictures/{$posts_row["filename"]}\">".
         "<img src=\"pictures/thumbnails/{$posts_row["filename"]}\" /></a><br />";
    $prev_post_id = $posts_row["id"];
}
?>

what function do the quotes serve?

I kinda like the if/die stuff on separate lines just for aesthetic reasons.

the quotes are just for correct syntax.

http://us2.php.net/manual/en/language.types.array.php

cool, thanks for that tip. always glad to correct formatting :).

it's working now :D.

thanks, lucas.

awesome

so I've got another question regarding this.

here is a basic example of the HTML I would like outputted:

<div class="update">
	<div class="date">
		Someday, Some Month 0, 0000
	</div>
	<div class="body">

		Example entry.
	</div>
	<img src="picture.jpg" />
	<img src="picture.jpg" />
</div>

my MySQL query outputs:

+----+---------------------+-------+------------+---------+--------------+----------+
| id | datetime            | body  | picture_id | comment | filename     | entry_id |
+----+---------------------+-------+------------+---------+--------------+----------+
| 41 | 2007-04-30 12:38:30 | asdf  |         59 | x-files | L-X14F11.jpg |       41 |
| 41 | 2007-04-30 12:38:30 | asdf  |         58 | x-files | B-X11F2.jpg  |       41 |
| 42 | 2007-04-30 12:42:06 | asdf2 |         57 | ben     | Sisko.jpg    |       42 |
+----+---------------------+-------+------------+---------+--------------+----------+

my PHP:

unset($prev_entry_id);
        while($journal_row = mysql_fetch_assoc($journal_result)) {
          if($journal_row['id'] != $prev_entry_id) {
            echo 'div update<br />';
            echo 'div datetime<br />';
            echo $journal_row[datetime] . '<br />';
            echo 'div close datetime<br />';
            echo 'div body<br />';
            echo $journal_row[body] . '<br />';
            echo 'div close body<br />';
            if(isset($journal_row['filename'])) {
              echo $journal_row[filename] . '<br />';
            }
            $prev_entry_id = $journal_row['id'];
          }
          else {
            echo $journal_row[filename] . '<br />';
            $prev_entry_id = $journal_row['id'];
          }
        }

obviously that's not the actual html output, I just did it like that so I could view it easily in a web browser without doing 'view source'. you can see it live here: http://www.thehomerow.net/~brain/dev/4/journal.php

the problem is where to put the closing div tag for the update div. it's almost as if the php has to know if there is going to be another image related to the relevant entry so it knows whether or not it should end the update div. any ideas? I can explain further or more clearly if necessary. tia!

sigh, three revisions to that post and still got the formatting messed up. you get the idea.

unfortunately it's not true revisions.

yeah, more like messy copying and pasting :/.

okay this feels kinda messy but it works.

$update_count = 0;
        while($journal_row = mysql_fetch_assoc($journal_result)) {
          if($journal_row['id'] != $prev_entry_id) {
            if($update_count > 0) {
              echo 'div close update<br />';
            }

"div close update" obviously meaning the close tag for div class=update.

hm then the last update div isn't closed. okay I put another closing tag outside of the while statement. talk about a mess!

that's how i do a lot of it. there's really no way around the messiness.

kinda like printing a list with commas, but without a comma at the end

can you use && within ifs?
as in:

if(a>b && b<c) {
    somecode
}

that would summarize your two ifs into one statement.

yeah, you can, but there's a lot more code after the second if statement that's still inside the first if statement :).