[selowitch]

After I have successfully run a query and sorted it, how do I then retrieve the row number of a record in a result set according to its id number? In other words: "Tell me the row number of the record in the result set whose id is [x]?"

[Oisín]

I'm assuming you have an ‘id’ field in your table here, which holds the number you're after, and a 'name' field where you have the value you call [x].

If your query is as follows, for instance:

Code:
$sql = mysql_query('SELECT * FROM [tablename] ORDER BY [something] ASC');

– you do this:

Code:
$row = mysql_fetch_array($sql, MYSQL_ASSOC);

You can now access the individual fields in the table via $row['aaa'] (aaa being the field name in the table).

So, you can do this if, for instance, you want to display the name and number together in a div:

Code:
function writethenumber($name) {
while($row['name'] == $name) {
  echo '<div>Name: ' . $row['name'] . '. Number ' . $row['id'] . '.</div>';
  }
}

... and pass that to some input field or something, where you can type in the name. You can use it any other way you want as well, of course.

[Note: I’m a PHP n00b, I wrote this in about two minutes, and I didn’t test it... but it should work. I think.]

[selowitch]

Thank you, this is an excellent start. My ultimate aim is to get the row number of the current id so I can also compute the row numbers of the immediately previous record. I can then run a query with a LIMIT clause that will fetch the id's of the previous, current, and next records in the set, so I can create the appropriate navlinks.

How should i do this?

So far I have:

Code:
$sql = "SELECT artists.first_name, artists.last_name, artists.nationality, artists.lifespan, items.artist_id, items.item_id, items.leading_article, items.title, items.medium, items.dimensions, items.lot_number, items.low_estimate,items.high_estimate, items.notes, items.ebay_id, items.hammer_price, items.sold_after FROM items, artists WHERE items.artist_id = artists.artist_id AND items.hidden = 'no' AND items.item_id = $id";

$row = mysql_fetch_array($sql, MYSQL_ASSOC);

Do I next loop through the array assigning sequential numbers to each row in the array? I'm a little unsure of my next step. I realize I'll need to conditionalize it so that if the record is the first in the recordset, I offer no Previous link, and if it's the last I'll offer no Next link, but I consider that part to be easy.

You might have guessed that this is a detail page to which i have passed a particular item_id in a query string of the URL.

[registered_user]

Originally Posted by selowitch

Thank you, this is an excellent start. My ultimate aim is to get the row number of the current id so I can also compute the row numbers of the immediately previous record. I can then run a query with a LIMIT clause that will fetch the id's of the previous, current, and next records in the set, so I can create the appropriate navlinks.

How should i do this?

So far I have:

Code:
$sql = "SELECT artists.first_name, artists.last_name, artists.nationality, artists.lifespan, items.artist_id, items.item_id, items.leading_article, items.title, items.medium, items.dimensions, items.lot_number, items.low_estimate,items.high_estimate, items.notes, items.ebay_id, items.hammer_price, items.sold_after FROM items, artists WHERE items.artist_id = artists.artist_id AND items.hidden = 'no' AND items.item_id = $id";

$row = mysql_fetch_array($sql, MYSQL_ASSOC);

Do I next loop through the array assigning sequential numbers to each row in the array? I'm a little unsure of my next step. I realize I'll need to conditionalize it so that if the record is the first in the recordset, I offer no Previous link, and if it's the last I'll offer no Next link, but I consider that part to be easy.

You might have guessed that this is a detail page to which i have passed a particular item_id in a query string of the URL.

I think you want a join.

$sql = "
SELECT artists.name, artists.etc, items.title, items.etc
FROM items
LEFT JOIN artists ON artists.artist_id = items.artist_id
WHERE items.item_id = $id
GROUP BY items.item_id
";

It's a touch easier, and you can grab all the stuff you need in one query without pulling extra stuff.

If that doesn't work for you, there are many php functions that handle arrays, including in_array() that would get you closer to where you want to be.

[selowitch]

I think I need to back up a bit here and explain what I'm doing from the beginning. Please bear with me!

On my homepage, I generate a list of paintings sorted by artist last name and then by title using this query:

Code:
SELECT artists.first_name, artists.last_name, artists.nationality, artists.lifespan, items.artist_id, items.item_id, items.leading_article, items.title, items.medium, items.dimensions, items.lot_number, items.low_estimate,items.high_estimate, items.notes, items.hammer_price, items.sold_after FROM items, artists WHERE items.artist_id = artists.artist_id AND items.hidden = 'no' AND auction_year = '2006' ORDER BY artists.last_name, items.title ASC

As you can see by looking at the page, each painting links to the same enlarge.php page to which I pass the id variable so that it can fetch the individual row/record for that particular painting. So far so good.

The thing is, I want to include next/previous links on that enlarge.php page. But of course, that has to be done according to the position of the paintings in the original sorted list (by artist last name and then painting title), so an ordinary sequential numerical comparison of the id numbers will not work. Unfortunately, until the above query is executed, there's no sense in retrieving the row number for the current id because the list hasn't been sorted yet.

All I would need is a three-record slice of a two-dimensional array that would have a row number (1, 2, 3, etc.) in one column and id numbers (321, 161, 203, etc.) in another. The sequence of id numbers would be identical to the original list on the homepage, and finding my previous, next, and current would simply be a matter of an array operation in PHP. Then I'd be able to generate the next and previous links by appending them to the result of PHP_SELF plus ?id=, then the number. Piece of cake.

The obvious thing to do is to dump the results of my original query into an array in PHP, find the spot in the array where a record containing the current id is, then seek one step down and one step forward in the array to find my next and previous ids. But try as I might, I can't figure out how to do this.

Does this clarify my problem? It would be great if I could somehow do this in a MySQL query (say that fetches the previous, current, and next ids in one shot), but I will use PHP to process an array if that's my only option.

Thanks for all your input. If you don't mind, I'd love to hear your responses to this deeper explanation.
Hats off to ya!

[deuce868]

I think what you might want to look at is to actually calc. the next/prev on the homepage while you have all of the records there. Then in your link pass in next=XX&prev=XX and then use those on your zoomed in page. In this way you're not generating a bunch of extra queries just to get the ids of the next/prev links. You already know the next/prev ids on the homepage. Use them there.

So your links on the homepage for each image become
http://mydomain.com/zoom.php?current=10&next=11&prev=9

(as an example)

[selowitch]

Oops. Duplicate post. Delete me.

[selowitch]

Originally Posted by deuce868

I think what you might want to look at is to actually calc. the next/prev on the homepage while you have all of the records there. Then in your link pass in next=XX&prev=XX and then use those on your zoomed in page. In this way you're not generating a bunch of extra queries just to get the ids of the next/prev links. You already know the next/prev ids on the homepage. Use them there.

So your links on the homepage for each image become
http://mydomain.com/zoom.php?current=10&next=11&prev=9

(as an example)

I considered doing that but there's one very good reason not to: there are links to my dynamic pages in Google (or other search engines) that would get screwed up.

enlarge.php?id=330
enlarge.php?id=331
enlarge.php?id=332

etc.

The list changes so often (and it's dynamic), so that wouldn't work. The next/previous links must be generated on the fly.

[deuce868]

Then just use two queries. For the prev link you would just
SELECT id FROM table WHERE id < $curr LIMIT 1 ORDER BY id DESC

and then get the next link the same way
SELECT id FROM table WHERE id > $curr LIMIT 1 ORDER BY id ASC

It won't matter if the number hops, you'll always get the next largest number. You can tack on some filters for WHERE Year = 2006 or whatever. You'll only get back the id which is all you need to build your links I would think.

[selowitch]

Originally Posted by deuce868

Then just use two queries. For the prev link you would just
SELECT id FROM table WHERE id < $curr LIMIT 1 ORDER BY id DESC

and then get the next link the same way
SELECT id FROM table WHERE id > $curr LIMIT 1 ORDER BY id ASC

It won't matter if the number hops, you'll always get the next largest number. You can tack on some filters for WHERE Year = 2006 or whatever. You'll only get back the id which is all you need to build your links I would think.

I thought of this, too, but it doesn't work because the previous, current, and next ids are not sequential by id:

23
24
25

they are more like this:

315
221
13

this is because the result set is sorted not by id number but by artist last name, artist first name, and finally by title. I did think of the technique you mentioned, but this aspect prevents it from working.

[selowitch]

OK, back up, backup. Wipe the slate.

I've worked on this a bunch and now I can reframe the PHP question.

I have a new query that gives me results with an additional field called "RANK" that corresponds to the row number and is just a straight sequence of positive integers (1, 2, 3, 4, 5). So all I have to do now in PHP is find out the row number of the record containing the present id and I'm golden:

After that, all I have to do is seek downward by one along the rank field of the array to get my previous record, and up by one to get the next record -- which would be brilliant.

But what's the PHP for that? Do I use mysql_data_seek()? If so, how? How do I get the answer the question "What is the rank of the record with id of x?"

[moodymonster]

SELECT rank (and whatever else) WHERE id=X

$the_array = mysql_fetch_assoc($query_result);

forward query

'SELECT * WHERE rank > ' . $the_array['rank'] . ' ORDER BY rank ASC LIMIT 1';

note that if you put an array (like above) into a quoted query it goes wierd, hence ' . $value . ' string'

back query:

'SELECT * WHERE rank < ' . $the_array['rank'] . ' ORDER BY rank DESC LIMIT 1';

or am I misunderstanding.

btw mysql_fetch_assoc is the same as mysql_fetch_array($sql, MYSQL_ASSOC). Both are faster than the making an array without the assoc parameter.

[selowitch]

moodymonster:

We're definitely on the right track. What I may not have clearly explained before was that I've already done a query to create the result so I don't think i can run a query on the results (or can I?) unless I turn it into a compound query.

If I understand correctly, we can't run this query because the rank column isn't in the database's table, it's just created as part of the query results.

Here's the original query:

Code:
SELECT a1.artist_id, a1.last_name, a1.first_name, i1.item_id, i1.title, 
1 + 
(SELECT COUNT(DISTINCT i2.item_id) FROM items i2, artists a2 
WHERE i2.artist_id = a2.artist_id AND i2.hidden = 'no'  
AND i2.auction_year = '2006'  
AND (a2.last_name < a1.last_name OR  
(a2.last_name = a1.last_name AND a2.first_name < a1.first_name) OR 
(a2.last_name = a1.last_name AND a2.first_name = a1.first_name AND i2.title < i1.title) )) 
AS rank 
 
FROM items i1, artists a1 
WHERE i1.artist_id = a1.artist_id AND i1.hidden = 'no'  
AND i1.auction_year = '2006'  
ORDER BY a1.last_name, a1.first_name, i1.title ASC

This gives me a series of results like this:

item_id: 315 rank: 1
item_id: 222 rank: 2
item_id: 108 rank: 3
etc.

How then do I traverse the array to a) find the row number of the current id, and then seek to that row number minus one, then the row number plus one. Make any sense?

Another approach to consider might be: Once I know the row number (represented by the "rank" alias), I could just run a follow-up MySQL query with a LIMIT row_number, 3 clause, which would fetch me the previous, current, and next ids.

[moodymonster]

the data is sorted as you want it to appear already, so you may be able to do it without the rank field

you could run it off into a for loop making an array of the id numbers and use the for loop's index to track the item_ids' in the order they appear eg

Code:
$mysql_num = mysql_num_rows($mysql_result);
//finding the number of rows outside of the loop is faster
for($i=0;$i<$mysql_num;$i++){
    $mysql_array = mysql_fetch_assoc($mysql_result);
    //you could alias the results eg i1.item_id AS item_id_out in the actual query
    //here we put the item id into our id_tracker array
    $id_tracker[$i] = $mysql_array['i1.item_id'];
    $output_array[$i]['i1.item_id'] = $mysql_array['i1.item_id'];

    $output_array[$i]['a_first_name'] = $mysql_array['a1.first_name'];
    //^do this bit for all the output, changing the array tags to match
}

you now have an array of the ids that can be referred to inside the array eg

Code:
$id_tracker[1] = 315;
$id_tracker[2] = 222;
$id_tracker[3] = 108;

this would then work eg

current_id = 222, therefore current_key = 2

therefore previous key is 1, and next key is 3

so for the final output you'd do this:

Code:
foreach($output_array as $loop_id => $data_array){
    //loop_id is the $i value
    //data array is because we've made an array inside to store the info
    //
    //we then search the $id_tracker array for the current item_id
    //this then returns the current key (ie rank)
    $current_key = array_search($data_array['i1.item_id'], $id_tracker);
    //once we have this we can add or subtract 1 from it to get the ids above and below
    $next_id = $id_tracker[$current_key+1];
    $prev_id = $id_tracker[$current_key-1];
    //you can then put them into links
    //and output the rest of the data eg
    echo $data_array['a_first_name'];
    //or whatever else you were going to do
}

hopefully that's what you're after, and it's not too hard to follow

[selowitch]

Cool! I'll try it. Thanks so much, moodymonster. I will report back...