MySQL: count items from first till xx

[V@no.]

Hi!
I'm trying figure out the best way to count names from first till specific name stored in the database.
I'll try explain it by example:
I have 5 words stored in the database:

1. dog
2. cat
3. bird
4. horse
5. pig

now I pull one (horse) and I'd like to know how many words (items) are stored before that word. I'd like to know how many words.
right now the only idea I have is just do regular query and then count each item using "while () {}"
so, I thought maybe there is MySQL command that can do that?
I saw in some scripts using "COUNT (*) as num" and "BETWEEN 1 to 20" but not sure if this will work, because I dont witch words would be first in the list.

hope its not too confusing...:rolleyes:
thx.

[Spookster]

Maybe you missed reading my sticky thread?

http://www.codingforums.com/showthread.php?s=&threadid=4181

I will move this to the MySQL forum...

[V@no.]

yes, I missed that.
but split PHP from MySQL is the same if u would split meat from fat...:rolleyes: that's just tooooo confusing.:mad:

[raf]

What exactly are you trying to do?
Are these values stored in seperate fields or in one field using some delimiter?

Count AS [alias] will give you the number cells in a variable 'and you can use a condition to specify which value should be in the cell.

So SELECT COUNT(*) AS [thenumber] FROM table WHERE variable='horse' GROUP BY variable;


Between is also a condtion say

select ID from table where budget between 1000 AND 2000
(also includes 1000 and 2000

What exactly do you need?

[V@no.]

Sorry, I'm too new in such a stuff, so I dont even know correct termins...
I have one table "words" that build with two fields: "ID" and "name"
so, in that table I have 5 records:

[ID] [name]
1 dog
3 cat
5 bird
4 pig
2 horse
now, by running this query (just an example):
SELECT ID, name FROM words WHERE ID=4 ORDER BY name LIMIT 1
I will have name: "pig". Now, I want find out how (this was sorted by name!) how many records are in the database from first to the one I just pulled out. In my example it will be 5th record, since "pig" would be last when sorted by alphabet.
so, is there a way to do so directly in MySQL query? otherwise I'll need pull ALL records then start scanning from first and compare with the one I pulled...that's not a very good idea when there are thousants of records..
hope this little bit more clear now and there is a sollution for it ;)
thx.

[raf]

:confused:
I don't quite understand all that. Can't you just insert an autonumber variable (like the ID column). The you can just read the ID and substract 1 of it.

like

[newID] [ID] [name]
1 1 dog
2 3 cat
3 5 bird
4 4 pig
5 2 horse


then select newID from table where name='pig'

then substract 1 from the newID value.



Or do you want to order/filter the table and then determine the rownumber of the record? (Then you need to store the view in atemporarely table and add an uautonumbervariable to it.

[V@no.]

well...it is automaticaly increase ID number, but the problem is, that in some cases I need pull records sorted by name, in other cases sorted by something else, and will make different numbers...
the MySQL command "BETWEEN" would work (I belive), if I could find out witch name will be first when it sorted...

[raf]

:confused: :confused: :confused:

Now, note that i only have limited intellectual capabilitys, but this doesn't make any sense to me. I have absolutely no idea what you're trying to do here.
3 options (in bold). Pick the one that covers your actual need.

Pure SQL

1. You have a table with records. Each has an "ID" and "name".
You want to order these records (--> = creating a view by ordering the table on a specifyed variable)
Then you want to select the top record from that view.
And then you want to know the 'how manyest record' it was in the original (no-sorted table).

Is it this that you want?
Well, then just add a variable in the original table that holds the recordnummer. Like i showed in my previous post. The ID is the the recordnumber.

Doesn't matter how you order the table, the recordnumber will always be the same for each record.


2. If you wan't to know the how-maniest record it is in the view (that is, in the internal representation of the orderd table) then you need to add an autonumbervariable to the view. So you need to store the view in a temporarely table with an autonumber variable
or
you need to use a condition to only get the records that would come before the "targettted" record and then run a Count(*) without condition
But what could that condition to create the view be? You need to be able to write a condition on only the first letter. Never heard of that in straight sql for MySQL. Maybe someone smarter did.

you then would need to be able to combine "Like" with "IN" and use a subquery (which is only available since version 4.1...) that has a > or < or between in it to limit the records in the view

maybe you sould run something like

select * from table where name like 'a%' or name like 'b%' or name like 'c%' etc.
the trick is then to automatically generate the sql statemnt, but thats scriptinglanguage material (build it with a loop)

select * from table where name like '(a-p)%' would be great, buth i dont think someting like that exists

sql with PHP
3. You need the same as 2, but don't care about how you get it. As long as it's fast enough. then you could return all records, ordered, an run through it using PHP or whatever scriptinglanguage.
Then loop through it, incrementing a counter.

I'd do that.

Or i'd add a few extra columns in the db. For instance, one with the first letter of the name. Then it's easy to make the select. If you can sort in on multiple variable, maybe add an extra variable for each variable you could possibly sort on.
The way, the responsetimes will be the lowest + wount make your db grow a lott and wount slow it down.

[V@no.]

Thx for trying to help! I really appretiate it!
but, no, neither of those "way" are sute to what I need...

let me try give actul what I want to do...

I have 100 images in one category. ( http://gallery.vano.org/c3 )
when open a category it will show u only 16 images per page.
Then, when u go on second page and click on any thumbnails, it will open details page.
Now, on left-top of the page are "your location" links, if u click on the category name, to go back to thumbnails view, it will open first page instead of second page where the image u came from is. So, to fix that, I need to know what is offset from first image in the category till the viewed one...
using IDs wont work, because all images could be moved from one category to enother...

[raf]

Can't you just append the pagenumber (where the tumbnail was in) to the querystring, and us that as a variable in the our Photo's link?

Our can't you just use a
<a href='javascript:history.back();'>
as the Our Photo's link in details.asp ?

Or set a back button there. This way you save a request on your webserver, a call to the db, building the recordset, the generating and upstreaming of the page etc.

So it saves you runtime resources and bandwith and saves the client a lot of downloading an waiting.

Just a thought ...

[V@no.]

yes, that would work, but only if u dont go <previous> <next> images.
that's the problem. when u are on details page, u can click to see previous, or next image, so, after 30 clicks on next image, u'll be already on 5th page, but if do as u saggested add page number to the query, then it will be still the page u started from.
another thing, that while u are watching "details view" u can change "image sort", that will also change position of the image in the pages...

[raf]

Didn't see the next-previous buttons (way down there ...)
Now, that could be tackled by adding the ofset and maximum of the referal to the quertstring, and then disabling the nex or previous when you reached the maximum or 1

(say each page contains 16 images (except last page probably) and i hit photo 5 of a page that contains 14 thumbnails, then i add 5 as current=5&max=14 to the querystring. And wht each hit on previous or next, i change the value for 'current' to keep trzck of the possiotion in the referal page. Before displayint the next or previous button, you compare the current with maximum (for the next button) or if it's higheer then 1 (for the previous button) and display an alternative message instead of the buttons (like 'End of page1' or 'top of 'page 1' OR you display a link to the next or previous page with thumbnails OR you also include the pagnumber in the querystring and just increment the pagename with one and set the current=1 etc etc. You get the idea?.)


About the filtering. I can see the use of that on the thumbnail-pages, buth not on the detail page. So i would only include it on the thumbnail page.

Just my personal opinion !!

[V@no.]

thank u very much for the help!
I finaly manage it.
I found out that the script already cached all the images in an array, so I used that variable. Saved some extra database query :D
thx. again!

[raf]

Glad you solved it.
Usually, if a sollution looks real complicated, you're looking in the wrong direction and there's a more easier way:D

[V@no.]

that's exactly what was here! lol