Help. How do I get result number from a query in mysql and php?

[nicknax]

Hi,

I am confusing myself slightly.

I have a query that is being run on a mysql database and it is filtering and ordering the data.

What I am trying to do is display the result number next to the item.

For example:-
I have a database of different pets, 4 are being picked up.. Cat, Dog, Rabbit and Hamster.

What I am trying to do is put the result number next to the pet.

i.e.. cat would say... cat = 1st result out of 4
dog would say... dog = 2nd out of 4
rabbit 3rd out of 4

etc...

Anyone have any ideas? Is this even possible in php and mysql? 😕

Many thanks in advance.

edit: the order may change from time to time, so the 3rd result may come 2nd after a while, this means it isn't really practical to add a resultid column or anything manually, as that would mean having to keep changing the value of the cell and obviously with a lot of hits this could kill bandwidth?

[NogDog]

Simplest thing probably would be to use a counter variable in the PHP

$counter= 1;
$total = mysql_num_rows($result);
while($row = mysql_fetch_assoc($result))
{
   printf("<p>%s (%d of %d)</p>\n", $row['animal'], $counter++);
}

It would output something like:

rabbit (1 of 4)
hamster (2 of 4)
cat (3 of 4)
coelacanth (4 of 4)

[nicknax]

Thank you very much for your help,

This is just what I'm looking for.

However.. Is there a way to have this so I can have the one pet on each page and it still pick up the result number from the query for that pet?

I.e.. on one page (rabbit) I want it to just say rabbit (1 of 4) and on another, possibly the cat page, I would want it to say cat (3 of 4)

For this method I think I have to have a full list of the pets on one page?

Thanks.

[messels]

nicknax wrote:

Is there a way to have this so I can have the one pet on each page and it still pick up the result number from the query for that pet?

I.e.. on one page (rabbit) I want it to just say rabbit (1 of 4) and on another, possibly the cat page, I would want it to say cat (3 of 4)

For this method I think I have to have a full list of the pets on one page?

Thanks.

if i understand what you're asking correctly (and know hwow to do it!), then you should do something like:

<?php
/ code from nodog /

print ("<a href=\"animial.php?id=$row['animal']\"> click here to go to animal page </a>");
?>

then on the animal.php you would use a $GET:
<?php
if (isset($GET['animal']))
{
print ("approriate layout here");
}
?>

i'm pretty new to all this but i would start w/ something like that... :bemused:

[nicknax]

Hi,

Sorry I think I'm confusing everyone!

Basically, what I want is.... Items in the database have such a value. The mysql query has an order in.
It orders the values in descending order.

What I want is to have a page for each item (animal) and it will tell you its overall position out of all items.

I.e. say there are the 4 animals:-
rabbit
hamster
cat
coelacanth

If hamster has the highest value at the time the query was ran, then that would then show (on the hamster page) as:-
position: 1 of 4.

If dog was second highest, on the dog's page it would show:-
position: 2 or 4.

Any ideas?

Thanks again.

[messels]

nicknax wrote:

Basically, what I want is.... Items in the database have such a value. The mysql query has an order in.
It orders the values in descending order.

oh, okay.

then in the $query = "SELECT FROM ... SORT BY /whatever value you have*/

that should help.

[nicknax]

Hi,

I already have that bit.

Here is an example of what i'm trying to do.

e.g.

Let's say I have a database and it has three columns.. id, animal, value (value being a number that changes depending on what is happening elsewhere on the website).

I can do a query with just the animals I want to display and can order by id no problem, but what I want to do is have a page for each animal (I can use $_GET['animal']) and I want to be able to print out in text, the row/result number of the animal, so the 1st result (with the highest value) should be 1, lowest 4 (or however many animals there are).

On the page that's specific to the animal should be the: 1 of x - where x is the number of animals included in the query.

It sounds quite complex. I'm sure there's a way to do it, but I'm struggling to explain what I mean.

[nicknax]

Anybody got any ideas?

Should I try and do this a different way?

[leatherback]

Hi NickNax,

It is certainly possible. You want to show each animal on its own page? Assuming you start of from the main resul dataset, you COULD just pass in the link the position & animal number to show. It depends a lot on what exactly is the sort of query yu are running and how you would like to navigate. You can use persistent queries, but I find them confusing, and do not use them 🙂

What I would do if it is just a matter of opening a page from an overview: Pass the position & id of the animal you would like to show on the page through the url:

showpage.php?animalid=2&resultorder=1

If yu want to be able to navigate through the results from the individual animal, you will need to pass the query itself too, either through the url or through sessions:

showpage.php?animalid=2&resultorder=1&searchfor=bluerabbit

[nicknax]

Hi,

this is similar but I want, but it's still quite different.

OK, basically what I want is a league type thing... I have got a points column and a league table. This is coded fine and all works.

What I want is, in this case, if you click on the animal, it will tell you its place in the league table out of how many animals are competing.

I do not have an animalid column, but as I say, I have a points column.

I assume I would have to run two mysql queries and use php looping?, one query to count how many animals are competing and one to count it's way through the results until the animal matches the position it will be located depending on points?

The value of points changes, so the position will change. Also, if ORDER BY is used, the amount of total entries, will be reduced aswell.

Am I doing this the right way?

Sorry for all the confusion and many thanks.