Display X Items per page with sort option?

[yellow1912]

Ok, lets say I have 1000 items in my database (mysql) and want to
display X items per page. I learned to do it by querying with the LIMIT
constrain (something like this
http://www.snipe.net/content/view/12/35/). The problem is I cant sort
using this algorithm (or maybe I just dont know how to do it). ORDER BY
didnt do the trick for me :(.
The only way I can think of is to pass all the item keys/names/anything
I want to sort into an array, sort them there then pass the array
between pages. But I really dont like this idea. I know there must be
better way to do this, anyone can help a newbie?

Thanks a bunch!!!!!

[Barry]

Re: Display X Items per page with sort option?

you should be able to use ORDER BY
SELECT * FROM table ORDER BY name LIMIT 1, 10

by using ORDER BY like this you will be keeping your paging intact.

what criteria are you using to sort by ?

yellow1912 wrote:[color=blue]
> Ok, lets say I have 1000 items in my database (mysql) and want to
> display X items per page. I learned to do it by querying with the LIMIT
> constrain (something like this
> http://www.snipe.net/content/view/12/35/). The problem is I cant sort
> using this algorithm (or maybe I just dont know how to do it). ORDER BY
> didnt do the trick for me :(.
> The only way I can think of is to pass all the item keys/names/anything
> I want to sort into an array, sort them there then pass the array
> between pages. But I really dont like this idea. I know there must be
> better way to do this, anyone can help a newbie?
>
> Thanks a bunch!!!!!
>[/color]

[Sandman]

Re: Display X Items per page with sort option?

In article <1126148269.374 612.295050@g49g 2000cwa.googleg roups.com>,
"yellow1912 " <yellow1912@yah oo.com> wrote:
[color=blue]
> Ok, lets say I have 1000 items in my database (mysql) and want to
> display X items per page. I learned to do it by querying with the LIMIT
> constrain (something like this
> http://www.snipe.net/content/view/12/35/). The problem is I cant sort
> using this algorithm (or maybe I just dont know how to do it). ORDER BY
> didnt do the trick for me :(.
> The only way I can think of is to pass all the item keys/names/anything
> I want to sort into an array, sort them there then pass the array
> between pages. But I really dont like this idea. I know there must be
> better way to do this, anyone can help a newbie?[/color]

You should use "order by" for this and let MySQL do the trick. Can't say why it
failed, but any given query could look like this:

select * from table order by date limit 25

For page navigation:

<?
if (!$_GET["start"]) $_GET["start"] = 0;
if (!$_GET["order"]) $_GET["order"] = 'date';

$sql = "select * from table order by $_GET[order] limit $_GET[start],25";
?>


--
Sandman[.net]

[yellow1912]

Re: Display X Items per page with sort option?

Thanks a bunch!!!

But I ran into this:
For example I have a column "NAME" contains name:
Name 1
Name 2
.....
Name 99

If I ORDER BY NAME DESC I will get something like this:
Name 99
Name 98
......
Name 90
Name 8 //The problems is here
Name 89
Name 88
......

The problem, I believe, is because of the way mysql compares the
strings. Hence make this sort kinda weird! How can I fix this, any
walk-around?

[muldoonaz]

Re: Display X Items per page with sort option?

yellow1912 wrote:[color=blue]
> Thanks a bunch!!!
>
> But I ran into this:
> For example I have a column "NAME" contains name:
> Name 1
> Name 2
> ....
> Name 99
>
> If I ORDER BY NAME DESC I will get something like this:
> Name 99
> Name 98
> .....
> Name 90
> Name 8 //The problems is here
> Name 89
> Name 88
> .....
>
> The problem, I believe, is because of the way mysql compares the
> strings. Hence make this sort kinda weird! How can I fix this, any
> walk-around?
>[/color]

add a 0 to the 8 so it shows 08 in the DB. That'll make it order them
right.

[Zoe Brown]

Re: Display X Items per page with sort option?


"yellow1912 " <yellow1912@yah oo.com> wrote in message
news:1126205568 .698426.143650@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> Thanks a bunch!!!
>
> But I ran into this:
> For example I have a column "NAME" contains name:
> Name 1
> Name 2
> ....
> Name 99
>
> If I ORDER BY NAME DESC I will get something like this:
> Name 99
> Name 98
> .....
> Name 90
> Name 8 //The problems is here
> Name 89
> Name 88
> .....
>
> The problem, I believe, is because of the way mysql compares the
> strings. Hence make this sort kinda weird! How can I fix this, any
> walk-around?[/color]

make sure your feild type is set to integer ?

[yellow1912]

Re: Display X Items per page with sort option?

Big thanks guys!
But what if the field must be string, and I cant really control what
user input?
They can input Item 1, or Item 01, .... There's no way ?

[muldoonaz]

Re: Display X Items per page with sort option?

yellow1912 wrote:[color=blue]
> Big thanks guys!
> But what if the field must be string, and I cant really control what
> user input?
> They can input Item 1, or Item 01, .... There's no way ?
>[/color]
you can control what you put in the db though. There's a php function
that'll float the number to any given number of leading 0's.

I wrote some code that helps me move things around in my knowledge base
when incidents or articles are added.

$code = "FF00512"; // KB code, pulled from the end of the table
$prefix = substr($code,0, 2); // output: "FF"
$suffix = substr($code,2, 5)+1; // "00512" + 1, output: "513"
$suffix = sprintf("%05d", $suffix); // output: "00513"
$solutionid = $prefix.$suffix ; // combine the prefix and the suffix

echo $solutionid; // output: "FF00513"

you could change the %05d to %03d, it'll make sure the integer is 3
numbers long and add leading zero's until it matches that. %05d will be
5 numbers, %07d 7 numbers and so on...

im sure there are easier ways to do this, but this was my solution.

[yellow1912]

Re: Display X Items per page with sort option?

Thanks everyone for your kind help and suggestion! I will surely try
all the solution suggested by you