mySQL row number

Anon

how can I get the number of the current row in mySQL?

Let's say I'm running the following query:

SELECT * FROM mytable WHERE url='/index.php';

As result I just get back 1 row as url is my PRIMARY KEY. How do I get the row-number from the postition of '/index.php' in mytable?

thanx
philippo

Anon

You don't. MySQL does not have row numbers.
Use and auto_increment field to identify your records.

Anon

I don't really like AUTO_INCREMENT for what I'm trying to do.

I'm working with two different versions of mySQL - at home I got 3.23.29gamma and on my server I got 3.22.25
"mySQL 3.23 introduced the new AUTO_INCREMENT behaviors of not reusing sequence numbers..." - without deleting all rows I'm not able to reassign the numbers. They must be 1,2,3,4,... without any lack of numbers, even if I delete some rows.

Actually, all I want to do is, order my rows and then create a new sequence of numbers. Any idea, how to do this without AUTO_INCREMENT ?

Philippo

Anon

What do you use the numbers for? If it is to identify records, you are doing things way wrong. if it is to print a nice list, use PHP to give the results numbers.

Anon

ok, I try to explain...
I'm writing a counter with the following two tables: logs, mpdl - where mpdl stores the hits of various url's or downloads:

CREATE TABLE mpdl (
type enum('mp','dl') DEFAULT 'mp' NOT NULL,
url varchar(255) NOT NULL,"
hits int(8) unsigned DEFAULT '0' NOT NULL,
since timestamp(8) NOT NULL,"
PRIMARY KEY (url)
);

to sort the table:
ALTER TABLE mpdl ORDER BY type,hits desc;

in my other table "logs" I store unique hits on my site with a column, that contains the entrypage - the url where the user has entered my site. To visualize this, I want to print different colored .gif's
All I need to do, when I show the results, is, to look for the entrypage (e.g. "/test.php") in the mpdl table and print the gif, e.g. 5.gif

got it?
hard to explain...

if there is no "clean" solution, tell me at least how to create a column with 1,2,3,4,... in it. it's not to identify records!

thanx
Philippo

Anon

So what you need is a resultcounter?
how about looping through the resultset and just adding 1 to a counter?

Anon

... what????
what are you talking about?
I don't think you got my question.

you don't need to know what I use it for. - my question is simple as that:
How do I create a column with the following content: 1, 2, 3, 4, ... mysql_num_rows($res)-1

???????

please try it one more time!!
🙂

Anon

And my question is, what do you need it for?

I've seen a few people here go mad trying to create tables filled with fancy data just because they wanted to give each result from a query a number so it looked good in html, when they could also just add a tiny counter to their loop.

But hey, if you want a column:
alter table bla add column something mediumint not null;

and to fill it, just loop through each and every record and update the column to the correct value.
Takes a few minutes, but then you have a nice gapless sequence of numbers.
Ofcourse you will have to do this each and ever time a record is added or deleted, but that's your problem.

Hmm, I'm glad Xmas is over, now I can be grumpy again!

Anon

hey vincent - you still don't need to know what I use it for! - The point is: I need it!!

that was my solution:
pretty slow and not really clean, but it works...
I fill the whole column into an array and then call the get_gif_no - function every time I need to get the number.

do you know any better way to do it?
...no, I don't want to bother you anymore!! 🙂

Philippo

$mp_sql = "SELECT url,hits from ".$id."_mpdl WHERE type='mp' ORDER BY hits DESC;";
$mp_res = mysql_query($mp_sql);
$m = 1; $mp_no = array();
while ($row = mysql_fetch_array($mp_res)) {
$mp_no[$m] = $row["url"];
$m++;
}

function get_gif_no($entry) {
global $mp_no;
for ($i = 1; $i < count($mp_no); $i++) {
if($entry == $mp_no[$i]) return $i;
}
return "other";
}