SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 35 of 35
  1. #26
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm afraid i only read the first few posts so don't know exactly whats happening now, but this little piece of code should give you the previous and next id's in relation to the current id.

    PHP Code:
    $temp_next "#";
    $temp_prev "#";
    $thisphotoid 5// the current photo id

    $sql mysql_query("SELECT pid FROM images ORDER BY pid DESC");
    $i 0;
    while(
    $row mysql_fetch_array($sql))
      {
      if(
    $i == 1)  // if the photo has been found, then this will be the next photo
        
    {
        
    $next $temp_next;
        
    $i 0;
        }

      if(
    $row["pid"] == $thisphotoid// if this is the current photo
        
    {
        
    $i 1;
        
    $previous $temp_prev// then the prev id will be the loop before this
        
    }

       
    $temp_prev $row["pid"];  // update the temp previous id in case the next loop is of the current photo
      
      
    $i ++;

      }

    $next // will be the id of the next photo, or # if there is no photo after this
    $previous // will be the if of the previous photo, or # if this is the first photo

     // from this you should be able to generate a link... 
    (it's some code i worked out a while ago - redid it from memory so hopefully it works - should do)
    Its based on a bit of logic. Basically what it does it you give it the current photo id ($thisphotoid) and then use mysql to select all the id's of the photos you want.

    Don't know if this is exactly what you're looking for or if this is the easiest method, but it should work perfectly. Let me know if it doesn't!

  2. #27
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's just awful.
    You have no idea how to use a database in proper way. You shouldnt select all records if you need only two.

  3. #28
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry didn't think about that.

    Come to think of it when i used it i needed to get info from all the records because it stored captions, dates and other photo info in a javascript array so that you could browse through the photos without reloading the page the whole time. So i just incorporated it into getting all the photo info. There generally were at max 20 photos in an album so it wasn't a problem.

  4. #29
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you mean you have separate table for each album? Thats wrong too

  5. #30
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    Do you mean you have separate table for each album? Thats wrong too
    Shrapnel_N5, I'm all for constructive criticism, if you're going to criticise another members solution/post, please feel free to offer up reasons why you're doing so.

    If your feeling extra helpful, maybe even offer up a better solution?

    Posts like this, help no one.

    Thanks.


    Anthony.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #31
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought it's obvious. Ever heard of Database normalization?
    All similar data must be stored in the same table. To make relations possible.

    As for better solution, I offered it already.

  7. #32
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    Do you mean you have separate table for each album? Thats wrong too
    Haha no i don't have separate tables

    I have a table imageid, siteid, url, thumburl, caption, date

    the albums i was referring to are the photos for the different sites. So i select all the photos where the siteid is a particular id.

  8. #33
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Example when defining order in terms of the primary key: (only meant to select the first and previous images id - nothing more)

    Code SQL:
    CREATE TABLE images (
    	images_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    	,PRIMARY KEY(images_id)
    );

    Code SQL:
    INSERT INTO images VALUES (NULL,NULL),(NULL,NULL),(NULL,NULL),(NULL,NULL);

    Code SQL:
    SELECT
         i.images_id
         ,(SELECT images.images_id FROM images WHERE images.images_id < i.images_id ORDER BY images.images_id DESC LIMIT 1) AS previous_images_id
         ,(SELECT images.images_id FROM images WHERE images.images_id > i.images_id ORDER BY images.images_id ASC LIMIT 1) AS next_images_id
      FROM
         images i
     WHERE
         i.images_id = 2

  9. #34
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    If you would always like to have a previous and next image then the below query will make the previous image the last image when on the first and the next the first then on the last.

    Code SQL:
    SELECT
         i.images_id
         ,COALESCE((SELECT images.images_id FROM images WHERE images.images_id < i.images_id ORDER BY images.images_id DESC LIMIT 1),(SELECT images.images_id FROM images ORDER BY images.images_id DESC LIMIT 1)) AS previous_images_id
         ,COALESCE((SELECT images.images_id FROM images WHERE images.images_id > i.images_id ORDER BY images.images_id ASC LIMIT 1),(SELECT images.images_id FROM images ORDER BY images.images_id ASC LIMIT 1)) AS next_images_id
      FROM
         images i
     WHERE
         i.images_id = 1

  10. #35
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you.


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
  •