[RESOLVED] Finding a Row Number in a query result...

_sh

I simply need to know the Row Number that a specific column value exists in, I know that MySQL doesn't necessarily have 'Row numbers' but I'm looking for the numeric that increments per row in a recordset, if such a value exists?

IS there an easier way to do this quickly, rather than creating the recordset, then looping through it checking the value of the ID on each loop?

This is the best way I can work out so far, unless there's a way to read the specific row number from the DB?

			$TheCounter = 0;
			$sql = "SELECT PhotoID FROM tbl_members_photos WHERE PhotoAlbumID IN (SELECT PhotoAlbumID FROM tbl_members_photos WHERE PhotoID = " . mysql_real_escape_string($ThePhotoID) . ") ORDER BY PhotoDateAdded DESC";
			$RS_GrabPhotoComments_Query = mysql_query($sql, $conn) or header("Location: offline.php?!=DatabaseTableUnavailable");

			while ($RS_GrabPhotoComments = mysql_fetch_array($RS_GrabPhotoComments_Query)) {

				$TheCounter = $TheCounter + 1;

				 if ($RS_GrabPhotoComments['PhotoID'] == "5") {
				 $ThePhotoID = $TheCounter
				 }

			}

laserlight

What exactly do you mean by "row number"? If you want to count how many photos are listed before the given photo when you order by PhotoDateAdded, just use a where clause that specifies PhotoDateAdded less than the given.

_sh

Sorry if I was unclear - basically I have a query that will retrieve some results - I also have a pagination script and need to pass to it the position within the recordset that a specific image appears in. Therefore if you look at my example, it achieves the effect, but I'm wondering if there is a better way to do this?

i.e. A way in MySQL where you can retrieve a record count, rather than me having to increment a counter, as I have in my example? So...I loop through to find the matching ID, does MySQL give me any kind of integer to relate to that position in the recordset?

laserlight

I think you want to do this:
1. Select the PhotoAlbumID and PhotoDateAdded corresponding to the photo with the given PhotoID.
2. Select the number of photos with the retrieved PhotoAlbumID such that the PhotoDateAdded is less than the retrieved PhotoDateAdded.
3. Add one to the result.

This can be accomplished with a single SQL statement, e.g.,

SELECT COUNT(*) + 1
FROM tbl_members_photos A, tbl_members_photos B
WHERE A.PhotoID=:PhotoID AND A.PhotoAlbumID=B.PhotoAlbumID AND B.PhotoDateAdded < A.PhotoDateAdded;

where 😛hotoID is a placeholder for the given PhotoID.

By the way, if $ThePhotoID is an integer, you should cast it to int instead of using mysql_real_escape_string().

_sh

laserlight;10903713 wrote:
I think you want to do this:
1. Select the PhotoAlbumID and PhotoDateAdded corresponding to the photo with the given PhotoID.
2. Select the number of photos with the retrieved PhotoAlbumID such that the PhotoDateAdded is less than the retrieved PhotoDateAdded.
3. Add one to the result.

This can be accomplished with a single SQL statement, e.g.,
SELECT COUNT(*) + 1
FROM tbl_members_photos A, tbl_members_photos B
WHERE A.PhotoID=:PhotoID AND A.PhotoAlbumID=B.PhotoAlbumID AND B.PhotoDateAdded < A.PhotoDateAdded;
where 😛hotoID is a placeholder for the given PhotoID.

By the way, if $ThePhotoID is an integer, you should cast it to int instead of using mysql_real_escape_string().

Thanks for your time on this! I think your solution is close to what I need, I'll explain further the system.

I have two tables, one for Photos, one for Albums - within the Photos table is a column to associate each picture with an album.

I also have a neat Pagination script so as for the user to page through the photos - this script requires a 'page' parameter so as to jump to a specific point in the photo recordset, filtered by the AlbumID.

If the user starts at the beginning of the Album, I can simply create the recordset and let them paginate through BUT to start the user midway through the album, jumping straight to a specific photo, I need to know at what stage that photo appears in the recordset (that is ALWAYS sorted DESC by PhotoDateAdded).

Therefore, what I'm trying to achieve is a way to find the Row Number where that photo appears, starting from a base of '1', so as to be able to pass a page number to my pagination script.

The result of this script is there...

$page (the rowid of the relevant photo within the recordset)
$albumID (I need to get this out of the recordset for the pagination SQL)

Does that make sense? Can I achieve all of this within one SQL script?

Thanks again for your help!

p.s. What does the := do in SQL?

laserlight

ashleyhall wrote:
Therefore, what I'm trying to achieve is a way to find the Row Number where that photo appears, starting from a base of '1', so as to be able to pass a page number to my pagination script.

Modify my example to do that, keeping in mind that you are sorting in descending order according to PhotoDateAdded. Post your attempt.

_sh

I've tried but doesn't seem to be working.

There are twelve photos in the album, I'm trying to grab the photo that appears in the third row of the recordset's result, but my output is 11!?

I'm not sure if I entirely understand what the SQL you wrote is doing? I can't quite get my poor head around it lol!

laserlight

ashleyhall wrote:
I've tried but doesn't seem to be working.

What did you try?

ashleyhall wrote:
There are twelve photos in the album, I'm trying to grab the photo that appears in the third row of the recordset's result, but my output is 11!?

I think that you are using my example as-is. My example assumed that you were retrieving the results in ascending order.

ashleyhall wrote:
I'm not sure if I entirely understand what the SQL you wrote is doing? I can't quite get my poor head around it lol!

Instead of SELECT COUNT() + 1, use SELECT so that you can more clearly see what is being counted.

By the way, can two photos in the same album have the same PhotoDateAdded?

_sh

I don't think I've explained myself clearly enough to be honest - I did take your example and added DESC ordering, but still get '11' as the result.

In this context, there won't ever be two DateAdded's of the same value as I'm using MySQL timestamp.

I don't know how to explain this any more simply really.

I have a PhotoID from a table Photos - each Photo entry has an PhotoAlbumID and PhotoDateAdded column.

The Photo table will always be sorted in DESC order based on PhotoDateAdded.

Once the PhotoID is passed, I need to filter the database to not only locate that PhotoID and retrieve the appropriate PhotoAlbumID that it is from, but also find its numerical index amongst the other photos within that Album, i.e. with the recordset sorted in DESC order, where in the recordset this photo would appear (if there are 13 photos, it would be position 1 to 13?) - so I need to someone increment a counter either within SQL, or within PHP itself (as in my first example).

laserlight

ashleyhall wrote:
I don't think I've explained myself clearly enough to be honest

You have, since your new explanation adds nothing to what you have already explained 🙂

ashleyhall wrote:
I did take your example and added DESC ordering, but still get '11' as the result.

Eh, you are supposed to cater for the DESC ordering, not add a DESC ordering. It does not matter how the rows are ordered when all you want is a count of how many there are. What matters is whether you are counting the rows with PhotoDateAdded greater than or less than the PhotoDateAdded of the given photo.

ashleyhall wrote:
In this context, there won't ever be two DateAdded's of the same value as I'm using MySQL timestamp.

That is good, because it simplifies the modification you need to do to get both the album id and the count:

SELECT A.PhotoAlbumID, COUNT(*)
FROM tbl_members_photos A, tbl_members_photos B
WHERE A.PhotoID=:PhotoID AND A.PhotoAlbumID=B.PhotoAlbumID AND B.PhotoDateAdded >= A.PhotoDateAdded
GROUP BY A.PhotoAlbumID;

Notice that I changed COUNT(*) + 1 to COUNT(*), and that instead of B.PhotoDateAdded < A.PhotoDateAdded I wrote B.PhotoDateAdded >= A.PhotoDateAdded. It is this change from "less than" to "greater than" that accounts for the DESC ordering. The check for equality includes the given photo into the count, hence there was no need to add 1 to the count.

_sh

You are a genius, from first look its giving me the result I need - thank you very much, I'll have a go at implementing this tomorrow - many thanks!

laserlight

You're welcome 🙂
If you have no further question on this, remember to mark this thread as resolved using the thread tools.

_sh

Thanks again.