Page 1 of 2

Sorting Problem (PHP+SQL)

Posted: Fri Feb 26, 2010 7:53 pm
by PHPMatt
HI,

- First I've attached the original PHP file, which you will need. This file is included in a page.
- Second, sorry for my english, this is not my primary language :P

Ok, the page I've attached currently dont sort the casinos: http://www.mescasinos.net/meilleurs-casinos.php

In my webpage, you can see the rating with the stars, this rating is made by mathematic (line 45):

 

Code: Select all

    $number = ($row['note_rep'] + $row['note_graph'] + $row['note_bmatch'] + $row['note_bmax'] + $row['note_bgratuit'] + $row['note_jackpot'] + $row['note_varjeux'] + $row['note_languages'] + $row['note_bloyauter'] + $row['note_jeuxmulti'] + $row['note_metpaiement'] + $row['note_metretrait'] + $row['note_support'])/13;
 
I want to get my casinos sorted DESC by the average off all the ['note_'].

Normaly, to get something sorted i use:

Code: Select all

$result = mysql_query("SELECT * FROM casinos ORDER BY something DESC");
Here the average is $number but i can't use it.

This is how i think the things must be but im not sure if thats the good way to make it:

make the average of all the fields "note_" for each casinos in my table named "casinos" and sort them after

Example:

Code: Select all

Table "casinos"
 
nom            note_rep          note_graph           note_support                 [...]
-------------------------------------------------------------------------------------
tropez             9                 6                    6
-------------------------------------------------------------------------------------
rushmore           8                 7                    9
-------------------------------------------------------------------------------------
bellini            9                 9                    9
-------------------------------------------------------------------------------------
 
The average of tropez is 7.6
The average of rushmore is 8
The average of bellini is 9
so in my webpage the casinos will be sorted:

bellini
rushmore
tropez

I hope you guys understand what i want say :P

Re: Sorting Problem (PHP+SQL)

Posted: Fri Feb 26, 2010 9:17 pm
by mikosiko
Alternatives:
- Use an aggregate Mysql function
http://www.learn-mysql-tutorial.com/Aggregation.cfm

- Before display your records + calculated $number load them in an array and then use array_sort()
http://php.net/manual/en/function.asort.php

Miko

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 5:12 pm
by PHPMatt
mikosiko wrote:Alternatives:
- Use an aggregate Mysql function
http://www.learn-mysql-tutorial.com/Aggregation.cfm

- Before display your records + calculated $number load them in an array and then use array_sort()
http://php.net/manual/en/function.asort.php

Miko
o0o i see.. I understand the main idea but I'm really not good with array. And I dont understand how to get the average of all [note_] for all casinos and then put all of this in an array.

Wow.. and dont think i'll be able to make it. I tried but nothing good! lol

Thank you for your reply dude, all is making sens but the problem is to code it now

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 5:29 pm
by mikosiko
mikosiko wrote:o0o i see.. I understand the main idea but I'm really not good with array. And I dont understand how to get the average of all [note_] for all casinos and then put all of this in an array.
My fault... I wasn't clear enought telling you that those are 2 different alternatives... you dont have to mix them if you don't need.

Using Mysql Agregate Function you can do per example:

Code: Select all

Select *,
         AVG(note_field1 + note_field2 + note_field3 + note_fieldN) as CasinoAverage
from casino
order by casino_name;
Using an Array... is for you to study :)

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 5:49 pm
by PHPMatt
mikosiko wrote:
mikosiko wrote:o0o i see.. I understand the main idea but I'm really not good with array. And I dont understand how to get the average of all [note_] for all casinos and then put all of this in an array.
My fault... I wasn't clear enought telling you that those are 2 different alternatives... you dont have to mix them if you don't need.

Using Mysql Agregate Function you can do per example:

Code: Select all

Select *,
         AVG(note_field1 + note_field2 + note_field3 + note_fieldN) as CasinoAverage
from casino
order by casino_name;
Using an Array... is for you to study :)
I see.. this is not really hard and i understand good. The problem is how to Agregate all the casinos without make a Agregate for ALL of the casinos. I mean i need to make the note_ + note_ + note_ etc.. for all the casinos.

My website is automated for when i add a new casinos in my databse, all is compatible with all php pages so If i make it for all the casinos, each time i'll add a casino in the databse, i'll have to add it in this file too.. which is not good.

I hope you can undertsand what i mean.. :P if not really sorry, just tell me i'll try in other words :)

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 8:53 pm
by mikosiko
PHPMatt wrote:......I see.. this is not really hard and i understand good. The problem is how to Agregate all the casinos without make a Agregate for ALL of the casinos. I mean i need to make the note_ + note_ + note_ etc.. for all the casinos.

I hope you can undertsand what i mean.. :P if not really sorry, just tell me i'll try in other words :)
My friend... the select that I posted for you do exactly what you want... it is calculating the average for ALL the casinos in your table... if you add another casino you don't have to modify anything...

Only instance that you will need to modify that select is if you add a new field in your casino's table.

the select just need to be modified to order by the AVG field... and that is.

if it is not what you want... please explain it again... (I speak Spanish too if that is your first language).

Miko

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 9:54 pm
by PHPMatt
mikosiko wrote:
PHPMatt wrote:......I see.. this is not really hard and i understand good. The problem is how to Agregate all the casinos without make a Agregate for ALL of the casinos. I mean i need to make the note_ + note_ + note_ etc.. for all the casinos.

I hope you can undertsand what i mean.. :P if not really sorry, just tell me i'll try in other words :)
My friend... the select that I posted for you do exactly what you want... it is calculating the average for ALL the casinos in your table... if you add another casino you don't have to modify anything...

Only instance that you will need to modify that select is if you add a new field in your casino's table.

the select just need to be modified to order by the AVG field... and that is.

if it is not what you want... please explain it again... (I speak Spanish too if that is your first language).

Miko
No no my first language is French, but i think both of us understand each other difficulties to be undertsanded well sometimes, this is why i wish to thank you for your patience! :) Really apreciated!

Second, this is what i need, maybe i just not understand the coding, arrays are scaring me i dunno why.. maybe i never took the time to learn them well!!

Anyways.. i'll try to play with this i give you news! :)

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 10:14 pm
by mikosiko
De rien

:wink:

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 10:18 pm
by PHPMatt
mikosiko wrote:De rien

:wink:
o0o you doin' great!

I'm still tryin' but i dont think i'll be able to make it..

I'm good to many of php stuff.. but arrays.. i never touched that.

I know that the code you gave me calculate the AVG of all note_, but the problem is.. how can i take after this all the note_ AVG separately and after this ORDER my list on my webpage in DESC AVG ?

Sry i feel like kind noob!

Re: Sorting Problem (PHP+SQL)

Posted: Tue Mar 02, 2010 11:11 pm
by mikosiko
PHPMAtt..

My apologizes but the select that I gave to you before doesn't work in the way that I told you...

in Mysql the avg aggregation function (and others of the same type) by definition return only one value after processing a set of record, therefore if you apply the select that I wrongly gave to you you will get only one result, therefore change the select and add a group by name at the end

Code: Select all

Select *,
         AVG(note_field1 + note_field2 + note_field3 + note_fieldN) as CasinoAverage
from casino
group by casino_name;
and for order the resuts

Code: Select all

Select *,
         AVG(note_field1 + note_field2 + note_field3 + note_fieldN) as CasinoAverage
from casino
group by casino_name order by  AVG(note_field1 + note_field2 + note_field3 + note_fieldN) DESC;
Miko

Re: Sorting Problem (PHP+SQL)

Posted: Wed Mar 03, 2010 3:33 pm
by PHPMatt
mikosiko wrote:PHPMAtt..

My apologizes but the select that I gave to you before doesn't work in the way that I told you...

in Mysql the avg aggregation function (and others of the same type) by definition return only one value after processing a set of record, therefore if you apply the select that I wrongly gave to you you will get only one result, therefore change the select and add a group by name at the end

Code: Select all

Select *,
         AVG(note_field1 + note_field2 + note_field3 + note_fieldN) as CasinoAverage
from casino
group by casino_name;
and for order the resuts

Code: Select all

Select *,
         AVG(note_field1 + note_field2 + note_field3 + note_fieldN) as CasinoAverage
from casino
group by casino_name order by  AVG(note_field1 + note_field2 + note_field3 + note_fieldN) DESC;
Miko
YES!! THAT'S WORKING!! here is the result

Code: Select all

$result = mysql_query("Select *,
         AVG(note_rep + note_graph + note_bmatch + note_bmax + note_bgratuit + note_jackpot + note_varjeux + note_languages + note_bloyauter + note_jeuxmulti + note_metpaiement + note_metretrait + note_support) as CasinoAverage
from casinos
group by nom order by  AVG(note_rep + note_graph + note_bmatch + note_bmax + note_bgratuit + note_jackpot + note_varjeux + note_languages + note_bloyauter + note_jeuxmulti + note_metpaiement + note_metretrait + note_support) DESC;");
That was really simple.. finaly! I'll just replace all the note_ buy a string which will be in my conf file! Will be easier to modify if i add a note_

A BIG thanks for all dude!! :wink: you really a nice guy thank you!

Re: Sorting Problem (PHP+SQL)

Posted: Wed Mar 03, 2010 4:05 pm
by AbraCadaver
Sorry I'm late to this, but just to help shorten. I'm pretty sure you can use the alias of the average in the order by:

Code: Select all

SELECT *,
         AVG(note_rep + note_graph + note_bmatch + note_bmax + note_bgratuit + note_jackpot + note_varjeux + note_languages + note_bloyauter + note_jeuxmulti + note_metpaiement + note_metretrait + note_support)
AS CasinoAverage
FROM casinos
GROUP BY nom ORDER BY CasinoAverage DESC

Re: Sorting Problem (PHP+SQL)

Posted: Wed Mar 03, 2010 5:09 pm
by PHPMatt
AbraCadaver wrote:Sorry I'm late to this, but just to help shorten. I'm pretty sure you can use the alias of the average in the order by:

Code: Select all

SELECT *,
         AVG(note_rep + note_graph + note_bmatch + note_bmax + note_bgratuit + note_jackpot + note_varjeux + note_languages + note_bloyauter + note_jeuxmulti + note_metpaiement + note_metretrait + note_support)
AS CasinoAverage
FROM casinos
GROUP BY nom ORDER BY CasinoAverage DESC
Mmm yes its working, dunno why ive not make it before. Thank you !

Re: Sorting Problem (PHP+SQL)

Posted: Wed Mar 03, 2010 8:46 pm
by mikosiko
PHPMatt wrote: ....Mmm yes its working, dunno why ive not make it before. Thank you !
:oops:
Sorry for not give you the short version before... but to tell you the true my mind has been thousand miles away from here... suffering for the people of the country where I was born ... so I've not thinking so clear for the last 5 days.... :? ....

Miko

Re: Sorting Problem (PHP+SQL)

Posted: Wed Mar 03, 2010 8:46 pm
by mikosiko
PHPMatt wrote: ....Mmm yes its working, dunno why ive not make it before. Thank you !
:oops:
Sorry for not give you the short version before... but to tell you the true my mind has been thousand miles away from here... suffering for the people of the country where I was born ... so I've not thinking so clear for the last 5 days.... :? ....

Miko