Sorting Problem (PHP+SQL)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

PHPMatt
Forum Newbie
Posts: 8
Joined: Fri Feb 26, 2010 7:38 pm

Sorting Problem (PHP+SQL)

Post 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
Attachments
liste-meilleurs-casinos_sql.rar
This is the entire file
(1.28 KiB) Downloaded 217 times
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Sorting Problem (PHP+SQL)

Post 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
PHPMatt
Forum Newbie
Posts: 8
Joined: Fri Feb 26, 2010 7:38 pm

Re: Sorting Problem (PHP+SQL)

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Sorting Problem (PHP+SQL)

Post 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 :)
PHPMatt
Forum Newbie
Posts: 8
Joined: Fri Feb 26, 2010 7:38 pm

Re: Sorting Problem (PHP+SQL)

Post 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 :)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Sorting Problem (PHP+SQL)

Post 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
PHPMatt
Forum Newbie
Posts: 8
Joined: Fri Feb 26, 2010 7:38 pm

Re: Sorting Problem (PHP+SQL)

Post 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! :)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Sorting Problem (PHP+SQL)

Post by mikosiko »

De rien

:wink:
PHPMatt
Forum Newbie
Posts: 8
Joined: Fri Feb 26, 2010 7:38 pm

Re: Sorting Problem (PHP+SQL)

Post 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!
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Sorting Problem (PHP+SQL)

Post 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
PHPMatt
Forum Newbie
Posts: 8
Joined: Fri Feb 26, 2010 7:38 pm

Re: Sorting Problem (PHP+SQL)

Post 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!
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Sorting Problem (PHP+SQL)

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
PHPMatt
Forum Newbie
Posts: 8
Joined: Fri Feb 26, 2010 7:38 pm

Re: Sorting Problem (PHP+SQL)

Post 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 !
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Sorting Problem (PHP+SQL)

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Sorting Problem (PHP+SQL)

Post 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
Post Reply