Member Avatar for lse123 
<cfquery datasource="yyyyyy" name="getData1" > 
SELECT Category FROM Products WHERE Product_ID=#displayImg2#
</cfquery>
<cfset PosInCatOfImg= > // how learn position of this Product_ID in getData1 query result data of Products ?

eg is the 13th row ???

Edited by peter_budo because: Keep It Clear - Do wrap your programming code blocks within [code] ... [/code] tags
  • 3 Contributors
  • 6 Replies
  • 190 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by jsmall26

Dani AI

Generated

— you need the index of that Product_ID inside the ordered set of products in the same category so you can suppress/show the prev/next arrows. The single-row query you showed can’t tell you position. Two practical approaches follow: fetch the ordered ID list in CF and locate the ID, or ask the database for the row number (recommended for very large categories).

ColdFusion (simple, good for small/medium categories): query the Product_IDs for the category using the same ORDER BY your gallery uses, then use ValueList() + listFind() to get the position and recordCount for the total.

<cfquery name="qIds" datasource="yourDSN">
  SELECT Product_ID
  FROM Products
  WHERE Category = <cfqueryparam value="#category#" cfsqltype="cf_sql_varchar">
  ORDER BY DisplayOrder, Product_ID
</cfquery>

<cfset pos = listFind(valueList(qIds.Product_ID), displayImg2)>
<cfset total = qIds.recordCount>

If pos EQ 1 hide the left/prev arrow; if pos EQ total hide the right/next arrow. Handle pos EQ 0 (not found) gracefully.

SQL-only (better for very large categories): ask the DB for the row number so you don’t fetch every ID. In MySQL 8+ use ROW_NUMBER():

SELECT rn
FROM (
  SELECT Product_ID, ROW_NUMBER() OVER (ORDER BY DisplayOrder, Product_ID) AS rn
  FROM Products
  WHERE Category = 'theCategory'
) t
WHERE Product_ID = 12345;

For older MySQL use a correlated COUNT() subquery that counts rows that come before the target (pos = 1 + count):

SELECT 1 + (
  SELECT COUNT(*) FROM Products p
  WHERE p.Category = t.Category
    AND (p.DisplayOrder < t.DisplayOrder
         OR (p.DisplayOrder = t.DisplayOrder AND p.Product_ID < t.Product_ID))
) AS position
FROM Products t
WHERE t.Product_ID = 12345;

Notes: always use the exact same ORDER BY used in the gallery (include a stable tie-breaker such as Product_ID), use cfqueryparam for safety, and pick the CF method for simplicity or the SQL method for scale. ’s pagination advice is relevant — if you already paginate server-side, use that paging logic to derive prev/next. ’s pointer to ColdFusion tutorials can help implement the CF pattern.

Recommended Answers

All 6 Replies

Member Avatar for arrgh

The query is already filtered by a Product_ID. So it's not clear what value are you referring to.

If you could explain why you need to determine the row number, and what db you're using we may be able to suggest a better option.

Member Avatar for lse123

I use MySQL, I want the position eg 7th row, of the only one returned row, in the resultset...?

Member Avatar for arrgh

I want the position eg 7th row, of the only one returned row,

It's still not making sense.

How can there be 7 rows in a query that only returns 1 row? Why do you need to know the row position? ie What are you using it for

Member Avatar for lse123

use to appear if needed: back/fwd Photo arrows/links (for go to next/previous Photo only in particular cat photos) in a individual photo page, Products table include Photos from all cat so, I need position in resultset NOT to appear if first/last record the left go / right go arrows...

Member Avatar for arrgh

If you're trying to do pagination (be it 1 item per page or 10), this seems like the wrong approach ie too complicated. Do a search on +ColdFusion +pagination. There's plenty of working examples.

Member Avatar for jsmall26

check easycfm.com for a good example. There's a lot of tutorials in there for a lot of different stuff too.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.