www.webdeveloper.com
Results 1 to 11 of 11

Thread: Finding Result Row Index?

  1. #1
    Join Date
    Dec 2010
    Posts
    232

    Finding Result Row Index?

    In my site I have an image gallery that shows all of the thumbnails from an album and an enlarged photo of the photo the user has selected.

    I have a query which selects all the photos (objects) from the album the user is currently viewing :-

    Code:
    $result = mysql_query("SELECT objects.object_id FROM objects
    LEFT JOIN objects_albums
    ON(objects_albums.object_id = objects.object_id)
    LEFT JOIN albums
    ON(objects_albums.album_id = albums.album_id)
    WHERE albums.album_id = ?");
    and a query which selects the current photo from the objects table and all it's details based on what thumbnail the user has clicked:-

    Code:
    $result2 = mysql_query("SELECT objects.object_id, object_image FROM objects
    WHERE objects.object_id = ? ");
    
    $row2 = mysql_fetch_array($result2);
    This works fine and allows users to click thumbnails to navigate the photos however I want to display photo indexes i.e. if the user goes to the 10th photo in an album of 17 photos "10 of 17" should be displayed.

    What is the best/most efficient way to achieve this?

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Use the recordcount as the total number of images, and as the code iterates through all the records you can assign the currentrow as the index. Unless the currentrow is a zero-based index, then it would be currentrow+1. Then, when the thumbnail is clicked, you can pass that index as a parameter.

  3. #3
    Join Date
    Dec 2010
    Posts
    232
    Thanks wolfshade do you know of any examples of that by any chance?

    I basically have my thumbnails at the top populated by the album query followed by my photo populated by my photo query.

    Can this be done in one query then?

  4. #4
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Are you using a pop up to display the selected image? Or is it appearing in a blank area of the same document?

  5. #5
    Join Date
    Dec 2010
    Posts
    232
    Within the same document at the moment

  6. #6
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    I assume that you are passing some sort of ID in the link that makes the larger image appear (database query id?) I also assume you are using JavaScript for that. You should be able to pass two more arguments in that JavaScript - currentRow and recordCount.

    But I have very little experience in PHP, and slightly more in Classic ASP. I don't know which you are using.

    In classic ASP, I would open the db connection, run the query, insert the records into an array, close the db connection, and then loop through the array to display the data. If this is the case, before the loop you can set a variable to 1, and then increment it at the end of each loop to get the current index, then just include the total number of records (I think it's count()?) as the total number of images.

    If it's classic ASP and you are opening the db connection, grabbing the data, then looping through that to display before closing the connection, I'm sure there is a similar way to do it.

    PHP, I couldn't say anything, right now, other than I'm sure there is a similar way to do it.

  7. #7
    Join Date
    Dec 2010
    Posts
    232
    Yep I know what you mean. The method I have in place at the moment is similar to what you described I think.

    The way it works is users go to a 'photos' section and all the albums are displayed, then when they click on an album all of the photos in that album are displayed as large thumbnails. When they click on one of these thumbnails it goes to my photo detail page which is where my problems start:-

    At the top of the photo details page I have all of my aforementioned thumbnails (small versions of them) and below that I have left and right arrows allowing users to navigate the album via either next and previous photo or by clicking the thumbnails. The photo number in the album (e.g. 3 of 14) is shown.

    When the user navigates the photos by either next/previous or by selecting a thumbnail the image displaying the large photo within this same screen/document changes. No javascript just php.

    Here is how I get all of the above via php:-

    Code:
    //This gets the specific photo being viewed
    	$result = mysql_query("
    
    SELECT objects.object_id
    FROM objects
    WHERE objects.object_id = '".$_GET["photoid"]."'
    	
    	");
    
    	$row = mysql_fetch_array($result);
    
    //This gets the album and photo ranks
    	$result2 = mysql_query("
    
    SELECT @rank:=@rank+1 AS rank, object_id, album_title FROM(
    
    SELECT objects.object_id, albums.album_title FROM objects
    LEFT JOIN objects_albums
    ON(objects_albums.object_id = objects.object_id)
    LEFT JOIN albums
    ON(objects_albums.album_id = albums.album_id)
    WHERE albums.album_id = '".$_GET["albumid"]."'
    
    ) objects, (SELECT @rank:=0) AS result;
    
    	
    	");
    
    $row2 = mysql_fetch_array($result2);
    
    //This gets the photo position in the album
    do{ 
        if ($row['object_id'] == $row2['object_id']) { $rankid = $row2['rank']; }; 
    }while($row2 = mysql_fetch_array($result2));  
    mysql_data_seek($result2, 0);//Resets the pointer of the result set back to 0 so it can be used again in another do while loop
    
    //Gets the previous photo
    do{ 
        if ($row2['rank'] == ($rankid - 1) ) { 
    	
    		$prevphoto = $row2['object_id']; 
    	
    	}; 
    }while($row2 = mysql_fetch_array($result2));  
    mysql_data_seek($result2, 0);
    
    //Gets the next photo
    do{ 
        if ($row2['rank'] == ($rankid + 1) ) { $nextphoto = $row2['object_id']; }; 
    }while($row2 = mysql_fetch_array($result2));
    mysql_data_seek($result2, 0);
    
    
    //Gets the last photo if the previous photo is null (user is viewing the first photo)
    if ( $prevphoto == NULL ){
    
    	do{ 
    		if ($row2['rank'] == mysql_num_rows($result2) ) { $prevphoto = $row2['object_id']; }; 
    	}while($row2 = mysql_fetch_array($result2));
    	mysql_data_seek($result2, 0);
    	
    }
    
    //Gets the first photo if the next photo is null (user is viewing the last photo)
    if ( $nextphoto == NULL ){
    
    	do{ 
    		if ($row2['rank'] == 1 ) { $nextphoto = $row2['object_id']; }; 
    	}while($row2 = mysql_fetch_array($result2));
    	mysql_data_seek($result2, 0);
    	
    }
    Is there a better way to do this?

  8. #8
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Yes, with a copious amount of javascript. Unfortunately, I do not currently have the time to go into a lot of detail (don't want the boss giving me the hairy eyeball.)

    Basically, if you store the images on the webserver (as opposed to inside the database) in both thumbnail and large format, you can get path/filename information from the database for displaying all of the thumbnails, and use a JavaScript function to display the selected (or next/prev clicked) image - pre-loading ALL of the images will speed things up, instead of having each image downloaded from the server one at a time. You can also set the JS function so that once it reaches the end of the album (assuming one is advancing - or the beginning of the album, if receding) it will loop around and start at the beginning/end. Much simpler and it saves bandwidth by having the client browser do all the work.

  9. #9
    Join Date
    Dec 2010
    Posts
    232
    Sounds about right but I personally wouldn't know where to start with that. I'll look into it. Thanks for all the help and suggestions

  10. #10
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    No problem. Check back on Sunday; if I have time on Saturday, I'll try to put some more detail into it, maybe have some sample code.

  11. #11
    Join Date
    Dec 2010
    Posts
    232
    Thanks very much. Only if you have time though.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles