SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 31
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    PHP Code:
    #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:
    PHP Code:
    #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

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    <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).
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..?

    PHP Code:
    $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

  4. #4
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Integrated into your code, it should look like this:
    Code PHP:
    $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:
    Code PHP:
    $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'] . '" />';
        }
    Last edited by kromey; Jun 18, 2007 at 13:09. Reason: typ
    PHP questions? RTFM
    MySQL questions? RTFM

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by computerbarry; Jun 18, 2007 at 13:58.

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could somebody please show me how to stop my title and date from repeating it's self with every thumbnail it shows?

    Thank You

  7. #7
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code HTML:
    <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.
    PHP questions? RTFM
    MySQL questions? RTFM

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP questions? RTFM
    MySQL questions? RTFM

  10. #10
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  11. #11
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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

  12. #12
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP questions? RTFM
    MySQL questions? RTFM

  13. #13
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by computerbarry; Jun 19, 2007 at 14:10.

  14. #14
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "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:
    Code PHP:
    $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.
    PHP questions? RTFM
    MySQL questions? RTFM

  15. #15
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
        $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

  16. #16
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    $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...
    PHP questions? RTFM
    MySQL questions? RTFM

  17. #17
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks understanding what you mean now, but with the code below, still gives the error:

    PHP Code:
    <?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

  18. #18
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, sorry, my mistake - completely overlooked your mention of the error.

    Change date to gallery.date and that will fix the error.
    PHP questions? RTFM
    MySQL questions? RTFM

  19. #19
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers, now it says - bool(false) ?

    Thanks

  20. #20
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    "...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).
    PHP questions? RTFM
    MySQL questions? RTFM

  21. #21
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes photo_id is an INTEGER column, still showing bool(false) no records ?

    PHP Code:
    <?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

  22. #22
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Add the line var_dump($sql) after it and let's see if that leads us to a solution.
    PHP questions? RTFM
    MySQL questions? RTFM

  23. #23
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    ..
    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

  24. #24
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP questions? RTFM
    MySQL questions? RTFM

  25. #25
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Forgot to update my .htaccess file:

    I had ?id instead of ?photo_id

    Code:
    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 Code:
    <?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 Code:
    <?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:

    PHP Code:
    ..
    var_dump($row);
    var_dump($sql); 
    Big Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •