How can I call for a photograph by id without a mention of .jpg

[computerbarry]

Hi all

Recently set up my table and populated it with some photo's and caption's etc, what I need to no is how can i get my images to show on the page so the address bar will look like this:

/photos/070923 - will show the thumbnails from that date
/photos/070923&01 - will show the larger photo once the thumbnail is clicked, or something like that? suggestions?

I might have to edit my .htaccess file, but need to understand when photographs are in the database how are they stored and retrieved?

photo_id = photo file name, example - 07092301 do I need to add the .jpg in my gallery table?

PHP:

#snippet 1
$sql = "SELECT photo_id, title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery WHERE date='" . mysql_real_escape_string($_GET['date']) . "'";
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['title'] . '</h5><p>' . $row['dr'] . '</p><h5>' . $row['photo_id'] . '</h5>';
    } 

Everything works fine here, but instead of showing my photos it just shows the id number on the page - 07092301, 07092302 etc?

I want to be able to click the thumbnail and get taken to the bigger photo ?

PHP:

#snippet 2
    $query = "SELECT date FROM gallery_information";
    $res = mysql_query($query);
    while($row = mysql_fetch_assoc($res))
    echo "<a href=\"photos/{$row['date']}\">{$row['date']}</a><br />\n"; 

This snippet will show the dates of the galleries and when clicked, will show all the thumbs from that date like above?

If it helps..

all the photos are separated into folders:

thumbs -
images -

Thanks

[kromey]

If I follow correctly, in your database you store e.g.
07092301, "Red Tulips in Grass", 2007-18-06
07092302, "Roses Are Red", 2007-18-06

The images themselves are stored on your filesystem, e.g. for "Red Tulips in Grass":
thumbs/07092301.jpg
images/07092301.jpg

Am I right?

If so, displaying them is quite simple: once you select the id from the database, then you echo the img tag like so:

<img src="thumbs/<?php echo $row['photo_id'] ?>.jpg" alt="<?php echo $row['title'] ?>" />

You can of course use similar logic to wrap that in an anchor tag that links to the full-sized image.

If you want to implement URL rewriting on this, look up mod_rewrite (if you're using Apache).

[computerbarry]

Thanks Kromey, yes exactly right besides the caption which I'll just have beneath the img, but i will use what you said above, very handy for the thumbnail caption.

just not quite sure, with your code? I added the below..?

$sql = "SELECT photo_id, title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery_information WHERE date='" . mysql_real_escape_string($_GET['date']) . "'";
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['title'] . '</h5><p>' . $row['dr'] . '</p><h5><img src="thumbs/170307/' . $row['photo_id'] . '.jpg</h5>';
    } 

This is what it said when I clicked view image on the place holder of the image

The requested URL /thumbs/170307/17030702.jpg</h5></p> <p><img src= was not found on this server.

You can of course use similar logic to wrap that in an anchor tag that links to the full-sized image.

How would I do that? I want it to show the big image with the caption on the same page if possible replacing the thumbs?

thanks

[kromey]

Integrated into your code, it should look like this:

$sql = "SELECT photo_id, title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery_information WHERE date='" . mysql_real_escape_string($_GET['date']) . "'";
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['title'] . '</h5><p>' . $row['dr'] . '</p><a href="bigimage.php?id=' . $row['photo_id'] . '"><img src="thumbs/170307/' . $row['photo_id'] . '.jpg" alt="' . $row['title'] . '" /></a>';
    }

Then bigimage.php (name it whatever you want) would include this code:

$sql = "SELECT photo_id, title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery_information WHERE photo_id=" . ((int)$_GET['id']);
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['title'] . '</h5><p>' . $row['dr'] . '</p><img src="images/170307/' . $row['photo_id'] . '.jpg" alt="' . $row['title'] . '" />';
    }

[computerbarry]

Thank you!

Works great, almost

Seems to be repeating the title and date with every thumbnail, instead of just the once?

Also if it's no bother, how do i add a back link when I click the big photo? Would it be much extra code to add a next and previous link button on bigphoto.php?

Thanks Kromey

[computerbarry]

Could somebody please show me how to stop my title and date from repeating it's self with every thumbnail it shows?

Thank You

[kromey]

I'm not sure I understand the problem. Doesn't each thumbnail have an associated title and date? And therefore don't you want to display each thumbnail's title and date along with the thumbnail itself?

As for a "back" link on the big photo, the easiest solution is a JavaScript one:

<a href="javascript:history.go(-1)">Back</a>

I can't provide a more robust solution without a better understanding of your application's structure.

[computerbarry]

Thanks kromey, ok my table 'gallery':

Ill show it in order I want it to display

title - varchar - the title of the gallery
date - index - the date of the gallery
photo_id - primary key - the thumbs
caption - varchar - for each individual photo

Each photo_id is indexed to the date it was taken.

lam going to include a caption with these photos which is how the title is working at the minute, the title and date will be the header of the page showing the thumbnails?

I just want the caption to show when i roll over the thumbs and display in a <p> on bigphoto.php.

I should of explained this properly before, sorry about that..

Cheers

[kromey]

Ah, gotcha. I misunderstood - I thought title and date were the title and date of the photo, not of the gallery.

First, Database Normalization 101: Since title and date will be repeated numerous times, better to break them off into their own table. One reason is to simplify updates - if for some reason you want to change the title of a gallery, under your current schema you'd need to update numerous rows; if instead you have a gallery table then you only need to update a single row. There are numerous other advantages to normalizing your database, but the rest are pretty abstract and somewhat difficult to grasp.

I propose:
Table gallery
gallery_id - integer, primary key
title - varchar - the title of the gallery
date - date, index - the date of the gallery

Table photos
photo_id - integer, primary key
gallery_id - integer, foreign key - what gallery this photo belongs to
caption - varchar - the photo's caption

This also simplifies what you need to do to display each gallery:
Pull the title and date from the gallery table and display it at the top of the page
Pull every photo from the photos table that match the current gallery_id and display each one

In your case a normalized database is quite logical: one table defines the galleries, a separate table defines the photos and links them to the galleries. I very highly recommend that you normalize your database in this fashion, as this is best practice and will simplify things greatly for you.

[computerbarry]

Yes exactly what i thought, I had a thread yesterday http://www.sitepoint.com/forums/showthread.php?t=485633 just had some different views, so i thought that would be best to break it down into one table. But I did a bit of reading and recognized the NF issue and how much easier it would be for me..

I will put this together and get back to you, thanks see you tomorrow if your about cheers!

[computerbarry]

Ok, changed the above in two tables, didn't really need the gallery_id because the gallery_id really = the date of the gallery.

Table gallery
date - date, primary key - the date of the gallery
title - varchar - the title of the gallery

Table photos
photo_id - integer, primary key
date - date, foreign key - what date this photo belongs to
caption - varchar - the photo's caption

The code below works fine now, showing the title and date just once and a big selection of thumbs, but would it be easier with a inner join?

<?php
    require_once('includes/mysql_connect.inc.php');

    $sql = "SELECT title, DATE_FORMAT(date, '%M %D %Y') AS dr FROM gallery WHERE date='" . mysql_real_escape_string($_GET['date']) . "'";
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['title'] . '</h5><p>' . $row['dr'] . '</p>';
    }
    $sql = "SELECT photo_id, caption FROM photos WHERE date='" . mysql_real_escape_string($_GET['date']) . "'";
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<a href="bigimage/' . $row['photo_id'] . '"><img src="thumbs/170307/' . $row['photo_id'] . '.jpg" title="' . $row['caption'] . '"</a>';
    }
 ?>

Also having problems understanding the foreign key? cant seem to find it in my php admin like I can primary,unique, fulltext etc, I understand what it does just wondering is that just a term used for the column relation?

Thanks - cb

[kromey]

Unless you're using the InnoDB engine (not the default, so if you didn't tell it to use InnoDB you're not using it), you can't enforce foreign key constraints. Although even when I do use InnoDB (my default, since I often make use of transactions), I don't use database-level foreign key constraints and instead enforce them in my application. Means you have to be a little more careful in your data manipulation, but so long as you keep in mind what's related to what you should be fine.

That's a long way of saying, "Just make photos.date an INDEX and leave it at that." Now that I've said that, Rudy's probably gonna come in here and beat me over the head!!

If you are using InnoDB and you do wish to enforce database-level foreign key constraints, it's kinda funky doing it in phpMyAdmin. I'd suggest looking up how to define foreign keys in the MySQL manual and just issuing the commands directly, either via phpMyAdmin or your command-line client.

As for the issue of using a JOIN, this is one of those cases where I would say that I think you are in fact better off using separate queries, since JOINing them would serve no purpose other than to increase the memory requirements of your script (since you would be repeating all the gallery-level metadata for each and every thumbnail, data which is held in memory while the script executes). When displaying the larger image, however, I would JOIN the two tables since you are pulling only a single row and still (at least, I presume) want the gallery metadata with it.

[computerbarry]

Cheers for getting back!

Your right, not using 'InnoDB' am using MyISAM, but I did read that InnoDB is a better table type than MyISAM, MyISAM is 'my' default. So shouldn't worry to much, not that clued up yet on the benefits of the storage engines.

..but so long as you keep in mind what's related to what you should be fine.

for now maybe A lot more reading involved I think..

When displaying the larger image, however, I would JOIN the two tables since you are pulling only a single row and still (at least, I presume) want the gallery metadata with it.

Thats what I was going to ask next, could you edit my bigphoto.php snippet to get this to function correctly(meaning how would i put the join together)?

Thanks

[kromey]

"Gallery metadata" = gallery title and date.
"Photo metadata" = gallery it belongs to, and caption.

The JOIN for the big image and the gallery would look like so:

$sql = "SELECT title, date, caption
FROM photos
LEFT JOIN gallery
    ON gallery.date = photos.date
WHERE photo_id = '".mysql_real_escape_string($_GET['photo_id'])."'";

Once you run that through mysql_query, var_dump the first (and only) row and you'll see what you have and from there can figure out how to display it to suit your needs.

[computerbarry]

Thanks kromey but really don't understand about the var_dump, where to put it and what It will show me? Sorry to keep on at this, still learning greatly here like you've probably guessed?

My code now:

    $sql = "SELECT title, DATE_FORMAT(date, '%M %D %Y') AS dr, caption
FROM photos
LEFT JOIN gallery
    ON gallery.date = photos.date
WHERE photo_id = '".mysql_real_escape_string($_GET['photo_id'])."'";
$result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h5>' . $row['title'] . '</h5><p>' . $row['dr'] . '</p><a href="javascript:history.go(-1)"><img src="images/170307/' . $row['photo_id'] . '.jpg" title="' . $row['title'] . '"></a>';

    } 

Error: Column 'date' in field list is ambiguous

thanks for the meta example

thanks

[kromey]

Since this query is returning one and only one row, ditch the while loop. Instead, your code should look like this after you run the query:

$row = mysql_fetch_array($result);
var_dump($row); 

The var_dump will tell you exactly what is in the variable $row - what type it is, what value it is - and from there you'll be able to understand what you need to do to make the output look like how you want it. That said, however, a glance at your code looks like you already are outputting what you want.

Oh, you'll also see why I use mysql_fetch_assoc instead of mysql_fetch_array when you do the above var_dump...

[computerbarry]

Thanks understanding what you mean now, but with the code below, still gives the error:

<?php
    require_once('includes/mysql_connect.inc.php');

    $sql = "SELECT title, DATE_FORMAT(date, '%M %D %Y') AS dr, caption
    FROM photos
    LEFT JOIN gallery
    ON gallery.date = photos.date
    WHERE photo_id = '".mysql_real_escape_string($_GET['photo_id'])."'";
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    $row = mysql_fetch_array($result);
    var_dump($row);

 ?>

Error: Column 'date' in field list is ambiguous

Cheers

[kromey]

Oh, sorry, my mistake - completely overlooked your mention of the error.

Change date to gallery.date and that will fix the error.

[computerbarry]

Cheers, now it says - bool(false) ?

Thanks

[kromey]

photo_id is an INTEGER column, isn't it? Ditch the single quotes around it, and instead of running it through mysql_real_escape_string enforce the integer type:

"...WHERE photo_id = ".((int)$_GET['photo_id']); 

Not sure if that will solve the problem, though. You're seeing bool(false) because you're not getting any records (this is why this assignment works inside of a while loop's conditional).

[computerbarry]

Yes photo_id is an INTEGER column, still showing bool(false) no records ?

<?php
    require_once('includes/mysql_connect.inc.php');

    $sql = "SELECT title, DATE_FORMAT(gallery.date, '%M %D %Y') AS dr, caption
    FROM photos
    LEFT JOIN gallery
    ON gallery.date = photos.date
    WHERE photo_id = ".((int)$_GET['photo_id']);
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    $row = mysql_fetch_array($result);
    var_dump($row);

 ?>

thanks

[kromey]

Add the line var_dump($sql) after it and let's see if that leads us to a solution.

[computerbarry]

..
var_dump($row);
var_dump($sql); 

bool(false) string(156) "SELECT title, DATE_FORMAT(gallery.date, '&#37;M %D %Y') AS dr, caption FROM photos LEFT JOIN gallery ON gallery.date = photos.date WHERE photo_id = 0"

thanks

Update: When i set photo_id up, I didnt give int(a value) 11 was just put there by default, if that helps
Type Null Default
int(11) No 0

[kromey]

Yup, exactly what I thought - notice that your photo_id you're looking for is 0. I'll bet dollars to donuts that you don't have a photo with the ID 0. In fact, I'd bet that when calling this script your URL doesn't have a '?photo_id=17030702' there, do you? In such case $_GET['photo_id'] is undefined and thus the query returns 0 results.

Add the query parameter to your URL and load the page that way.

[computerbarry]

Forgot to update my .htaccess file:

I had ?id instead of ?photo_id

RewriteRule ^/?bigimage/([_a-zA-Z0-9-]+)$ bigimage.php?photo_id=$1 [L]

Even that didn't solve the problem though, i needed to $sql = SELECT photo_id, .. as well for the image to display.. below:

..and yes i did have a photo_id=17030702

Also, wont I need the . mysql_real_escape_string with $_GET['photo_id']).. (sercurity reaosns, or is that only needed for strings if so how do you protect the int from injections?)?

<?php
    require_once('includes/mysql_connect.inc.php');
    
    $sql = "SELECT photo_id, title, DATE_FORMAT(gallery.date, '%M %D %Y') AS dr, caption
    FROM photos
    LEFT JOIN gallery
    ON gallery.date = photos.date
    WHERE photo_id = ".((int)$_GET['photo_id']);
    $result = @mysql_query($sql) or die('Error: ' . mysql_error());
    while ($row = mysql_fetch_array ($result)) {
    echo '<h1>' . $row['title'] . '</h1><p>Date: ' . $row['dr'] . '</p><a href="javascript:history.go(-1)"><img src="images/170307/' . $row['photo_id'] . '.jpg" title="' . $row['caption'] . '"></a><h5>' . $row['caption'] . '</h5>';

    } 

 ?>

Regarding the "Gallery metadata" how can I get that to display in my title(address bar)?

I tried inserting a basic

<?php echo  '<h1>' . $row['title'] . '</h1>' ?>

into my <title>..</title> tag, but just didn't show anything, is that because the snippet was below and had no values yet?

and what is the main reason of using:

..
var_dump($row);
var_dump($sql); 

Big Thanks