simples way to sort a mysql result

[somaBoy MX]

This may be a stupid question, but what is the easiest way to sort a mysql
resultset?

For example I have a resulset containing a colomn 'product_name_e nglish' and
'product_name_f rench' and want to sort the result on one of these colums
based on a language parameter.

I know how to do this in a query ("ORDER BY") but how can I do this after
the query has been executed?


..soma

[Alexandre Lahure]

Re: simples way to sort a mysql result

> This may be a stupid question, but what is the easiest way to sort a[color=blue]
> mysql
> resultset?
>
> For example I have a resulset containing a colomn 'product_name_e nglish'
> and
> 'product_name_f rench' and want to sort the result on one of these colums
> based on a language parameter.
>
> I know how to do this in a query ("ORDER BY") but how can I do this after
> the query has been executed?
>
>
> .soma[/color]

You can use mysql_fetch_arr ay(), then use all the functions related to
arrays


--
Alexandre Lahure
Point 52, Solutions Internet "Ready to Start"

http://www.point52.com/

"Computers are like air conditioners,
They don't work when you open windows"

[David Mackenzie]

Re: simples way to sort a mysql result

On Fri, 7 Nov 2003 13:52:35 +0100, "somaBoy MX" <none@nonesuch. net>
wrote:
[color=blue]
>This may be a stupid question, but what is the easiest way to sort a mysql
>resultset?
>
>For example I have a resulset containing a colomn 'product_name_e nglish' and
>'product_name_ french' and want to sort the result on one of these colums
>based on a language parameter.
>
>I know how to do this in a query ("ORDER BY") but how can I do this after
>the query has been executed?[/color]

You're always best to get the DBMS to do the sorting if you can, as it
is optimised for that sort of thing (no pun intended :-) :

$sql = "select column from table where condition";

switch ( $lang )
{
case "english":
$orderby = "product_name_e nglish";
break;

case "french":
$orderby = "product_name_f rench";
break;

default:
$orderby = "product_name_e nglish";
break;
}

$sql .= $sql." order by ".$orderby;

Although from what you have described it sounds like your database
isn't fully normalised. If you decide to add another language later on
you'll need to alter your database schema and update your code in the
relevant places.

Create a product table, a language table and a productlanguage table
that links product and language, e.g.

PRODUCT
pid, price, etc.

LANGUAGE
lid, name
1 English
2 French

PRODUCTLANGUAGE
pid, lid, description
1 1 Widget
1 2 French Widget
2 1 Gizmo
2 2 French Gizmo

and so on.

--
David ( @priz.co.uk )
The Internet Prisoner Database: http://www.priz.co.uk/ipdb/
The Tarbrax Chronicle: http://www.tarbraxchronicle.com/

[somaBoy MX]

Re: simples way to sort a mysql result

"David Mackenzie" <me@privacy.net > wrote ...

: [...] Although from what you have described it sounds like your database
: isn't fully normalised. If you decide to add another language later on
: you'll need to alter your database schema and update your code in the
: relevant places.[...]


Thanks for the e x t e n s i v e information, David !

However, I didn't fully normalize the db because it really isn't that big an
application I'm building. It doesn't need "mission-critical solidness" or
anything like that. Also (and mostly), I'm no dba genius. I've been meaning
to look into advanced db design techniques, but you know how it is...

I was really looking for a function to sort the associative array extracted
from a mysql result with mysql_fetch_ass oc().

But I went with your first suggestion, that works fine too.

Thanks,


..soma

[David Mackenzie]

Re: simples way to sort a mysql result

On Fri, 7 Nov 2003 16:33:49 +0100, "somaBoy MX" <none@nonesuch. net>
wrote:
[color=blue]
>"David Mackenzie" <me@privacy.net > wrote ...
>
>: [...] Although from what you have described it sounds like your database
>: isn't fully normalised. If you decide to add another language later on
>: you'll need to alter your database schema and update your code in the
>: relevant places.[...]
>
>
>Thanks for the e x t e n s i v e information, David ![/color]

Ta!
[color=blue]
>However, I didn't fully normalize the db because it really isn't that big an
>application I'm building.[/color]

You say that now, but when someone asks for German translations...
[color=blue]
>It doesn't need "mission-critical solidness" or
>anything like that. Also (and mostly), I'm no dba genius. I've been meaning
>to look into advanced db design techniques, but you know how it is...[/color]

I was taught DB design at Uni, but forgot most of it after the exam.
It wasn't until I'd been involved in the design of a couple that I got
the hang of it again.

I'm no DBA either; I can manage third normal form and simple joins but
I still get confused over left/right inner/outer joins. I always need
to ask for assistance with these.
[color=blue]
>I was really looking for a function to sort the associative array extracted
>from a mysql result with mysql_fetch_ass oc().[/color]

Look into the functions that mysql offers such as SUM(), MIN(), MAX()
etc (just a glance through them will do) as well as the functions for
formatting dates and numbers, and the string slicing functions, just
so that you are aware of how much can be done by the database.

--
David ( @priz.co.uk )
The Internet Prisoner Database: http://www.priz.co.uk/ipdb/
The Tarbrax Chronicle: http://www.tarbraxchronicle.com/

[somaBoy MX]

Re: simples way to sort a mysql result


Thanks for the advice. I think I'll get me that O'Reilly "MySQL cookbook"
after all :))

..soma
(PS: I too often have to look up the syntax for complex query's. Luckily I
usually code in Dreamweaver, which has a neat little built-in SQL reference
cheat panel :))