Dynamically insert row numbers into database results

shaneshack

I haven't been able to find a satisfactory answer for this one.

I have a database - company.csv using ODBC System DSN using Microsoft Text Driver. There is a unique field called stock which acts as a primary key. The problem is the stock numbers are not sequential - 1, 2, 3, 4, etc. - but are more like - 8012, 4567, 9043, 1238, etc. Since the stock numbers are not sequential, my pagination results are skewed and do not display correctly. Here's my sql statements for the pagination:

$query = "SELECT * FROM company.csv WHERE make = 'Chevrolet'";
$num_result = odbc_exec($db, $query);
$numrows =  inv_rows($num_result);
$make = odbc_result($num_result, "make");
echo '<p>There are currently '.$numrows.' '.$make.' products in our inventory.</p>';

$limit = 10;
$limitvalue = $page * $limit - ($limit);
$limitnew = $limitvalue + $limit;

$sql = "SELECT TOP $numrows * FROM (SELECT TOP $numrows * FROM company.csv WHERE stock between '$limitvalue' and '$limitnew' AND make = 'Chevrolet' ORDER BY model, year, sellingprice ASC) ORDER BY model, year, sellingprice ASC";
$result = odbc_exec($db, $sql);
while(odbc_fetch_row($result)){

inv_rows() is a function defined earlier in the page

As I said, my pagination returns results, but in a rather random and chaotic order, and it also does not include every available record in the results. My question is this: How do I create a row number on the fly for each record, which I can then use in the above sql statement in place of "stock"? I think doing so will cause my pagination results to display correctly. Fast help would be appreciated. Thanks in advance.

Piranha

The solution you suggest is no good. You should not need to change all the rows in the database just because you insert or delete one. Another thing is that if you do as you suggest you need one column for each way you need to order the results, for example one for the brand, one for the model and one for each car.

It is better to do a real pagination. You should use the suggestion from Roger Ramjet in this post.

shaneshack

I wouldn't be asking this if I hadn't already done all the things Roger Ramjet suggested. His answer got me to this point. Before, my pagination didn't work at all and he got me to a point where it would do what it does now.

Now I need some more direction. I have googled all day long to find an answer that would suit my need, to no avail. That's why I'm here again.

So if my suggestion isn't any good, please offer me something to work with, not redirection to something I've already tried. (Surely you noticed that I asked the question in the link you offered?)

Piranha

I did notice that. And I thought that you were as many others that ask questions here, if you get an answer that you don't like start a new thread. Obviously this is not true for you, with your answer I am sure that you have tried out his solution.

But still you don't seem to want to solve the pagination problem, you want to do a hack to avoid it.

I think that the biggest problem is that you try to select where stock between something that have to do with limits. That will mess up the results.