Help with sorting in order please

[PHP12345]

Hello,

I have a database with users and their location (City) and want to sort the data depending on the most popular location.

I know how to do the basic order ...ORDER BY location DESC... but I don't know how to order by the most popular location i.e. London (35), Bristol (24), Birmingham (15), Cardiff (10)...

The only way I know would list London 35 times, Bristol 24 times etc... but I only want the name to appear once with the most popular location first.

I also want to limit the output to the top 10, is there a tutorial on how to do this?

Sorry for so many questions.

Thanks in advance.

[birdie]

why dont u make a system on where..

User Clicks a location which is at 1st basically in order of which the locations were added or whatever.

Once clicked, it updates and puts that location up on top.

SELECT * FROM.. ORDER BY locationcount DESC

locationcount could be an int where each time a location is selected..

[code]
$sql = "SELECT * FROM tabe WHERE location='$location'";
$query = mysql_query($sql);
$object = mysql_fetch_object($query);
$currcount = $object->locationcount;
$currcount++;
$sql2 = "UPDATE table SET locationcount='$currcount' WHERE location='$location'";
mysql_query($sql2);
//wow i really should stop doing code for no reason
[/code]

Hope that helps ;D

[Orio]

You can do something like this:

[code=php:0]<?php //connect to db
$sql="SELECT city FROM places";
$result=mysql_query($sql);
$arr_cities=array();
while($city=mysql_fetch_array($result){
if(!isset($arr_cities[$city]){$arr_cities[$city]=1;}
else{$arr_cities[$city]++;};
};
$arr_cities=natsort($arr_cities); //not sure about this line, because I dont know if natsort rests keying or leaves them as they are.
$values=count($arr_cities);
while(count>10){array_pop($arr_cities);};//must be a better way for this but nvm
foreach($arr_cities $key=>$val){
echo($key." -> ".$val);};
?>[/code]
[hr]

Orio.

[effigy]

Incorporate this:

[code]create temporary table rankings select id, count(*) as rank from table group by id;
select id, etc from table, rankings where table.id = rankings.id order by rank;[/code]

[PHP12345]

Hi,

Thanks, that worked ;D

--

I'm trying to do something similar to another script but I can't seem to get it right  :'(

This is the original script where it lists all the Players then queries the database to see how many MoTM (man of the match awards) for each player, and puts the number in a column next to the players name.

[code]<table width="100%">
<tr>
<td colspan="2" class="dark"><div align="center"><b>Players</b></div></td>
</tr>
<tr>
<td class="dark"><b>Username</b></td>
<td class="dark"><div align="center"><b>MoTM</b></div></td>
</tr>
<?php

$member_query = mysql_query("SELECT * FROM users WHERE(accept='1') ORDER BY id ASC");
while($member = mysql_fetch_array($member_query)) {
if(get_magic_quotes_runtime())
{
$member['username'] = stripslashes($member['username']);
}

$motm1_query = mysql_query("SELECT id, clan1, clan2, date FROM fixtures_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."') ORDER BY date DESC, id DESC");
$motm2_query = mysql_query("SELECT id, clan1, clan2, date FROM cup_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."') ORDER BY date DESC, id DESC");
if(mysql_num_rows($motm1_query) > 0 or mysql_num_rows($motm2_query) > 0) {
$test1_motm['date'] = 0;
$test2_motm['date'] = 0;
$test1_motm_query = mysql_query("SELECT date FROM fixtures_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."') ORDER BY date DESC, id DESC LIMIT 1");
$test2_motm_query = mysql_query("SELECT date FROM cup_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."') ORDER BY date DESC, id DESC LIMIT 1");
if(mysql_num_rows($test1_motm_query) == 1) {
$test1_motm = mysql_fetch_array($test1_motm_query);
}
if(mysql_num_rows($test2_motm_query) == 1) {
$test2_motm = mysql_fetch_array($test2_motm_query);
}
if($test1_motm['date'] >= $test2_motm['date']) {
$motm = mysql_fetch_array($motm1_query);
$page_name = 'clan';
}
if($test1_motm['date'] < $test2_motm['date']) {
$motm = mysql_fetch_array($motm2_query);
$page_name = 'cup';
}
}
$motm = mysql_num_rows($motm1_query) + mysql_num_rows($motm2_query);
?>
<tr>
<td class="light"><a href="user_view.php?u=<?php echo $member['id']; ?>"><?php echo $member['username']; ?></a></td>
<td class="light"><div align="center"><?php echo $motm; ?></div></td>
</tr>
<?php
}
?>
</table>[/code]

Is there a simple bit of code that I could add so that the list is in order depending on how many MoTM awards the player gets (with the most MoTM awards at the top)? At the moment it lists it according to the Player ID

Can this be limited to top 10?

Thanks again in advance.

[PHP12345]

Can anyone help a PHP n00b  ???

Thanks in advance  ;)

[PHP12345]

If anyone can help I'll deposit some money to your PayPal account  ;D

I've spent ages trying to sort it out but I seem to get into a bigger mess!!!

[zawadi]

can you tell me if there is a new row for each MoTM for each player?

[PHP12345]

Hi,

Each player gets 1 row and the MoTM awards are totalled for that player in the second column)

[zawadi]

ok, if there is only 1 row for each player you could use the order by on that colum. but I dont understand what you are doing to get the MoTM  ???

[PHP12345]

Each match has a MoTM so the database is querying how many times the Member ID is listed as the MoTM (2 queries because the league & cup are logged in different tables)

[code]$motm1_query = mysql_query("SELECT id, clan1, clan2, date FROM fixtures_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."') ORDER BY date DESC, id DESC");
$motm2_query = mysql_query("SELECT id, clan1, clan2, date FROM cup_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."') ORDER BY date DESC, id DESC");[/code]

Then the MoTM is the total of both queries. This just generates a number.

[code]$motm = mysql_num_rows($motm1_query) + mysql_num_rows($motm2_query);[/code]

At the moment my script lists the Players (alphabetical order) then lists the no. of MoTM awards next to the Player name. I want the MoTM awards to be listed in order (descending order) with the player name next to it.

I've stripped a bit of the code out to make it easier on the eye..

[code]<table width="100%">
<tr>
<td colspan="2" class="dark"><div align="center"><b>Players</b></div></td>
</tr>
<tr>
<td class="dark"><b>Username</b></td>
<td class="dark"><div align="center"><b>MoTM</b></div></td>
</tr>
<?php

$member_query = mysql_query("SELECT * FROM users WHERE(accept='1') ORDER BY id ASC");
while($member = mysql_fetch_array($member_query)) {
if(get_magic_quotes_runtime())
{
$member['username'] = stripslashes($member['username']);
}

$motm1_query = mysql_query("SELECT id, clan1, clan2, date FROM fixtures_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."')");
$motm2_query = mysql_query("SELECT id, clan1, clan2, date FROM cup_results WHERE(motm1='".$member['id']."' or motm2='".$member['id']."')");
$motm = mysql_num_rows($motm1_query) + mysql_num_rows($motm2_query);
?>
<tr>
<td class="light"><a href="user_view.php?u=<?php echo $member['id']; ?>"><?php echo $member['username']; ?></a></td>
<td class="light"><div align="center"><?php echo $motm; ?></div></td>
</tr>
<?php
}
?>
</table>[/code]

[PHP12345]

Is there anyone who can help?

I'm struggling with my temporary tables  :'(

I'm quite happy to pay someone who helps me.

[heckenschutze]

You could pack it into one query,

I am no SQL god, but I do have a basic understanding... Note this is untested, and may result in SQL errors..:

[code]<table width="100%">
<tr>
<td colspan="2" class="dark"><div align="center"><b>Players</b></div></td>
</tr>
<tr>
<td class="dark"><b>Username</b></td>
<td class="dark"><div align="center"><b>MoTM</b></div></td>
</tr>
<?php

$member_query = mysql_query("SELECT *,*,COUNT(`fixtures_results.id`) AS \"fixtures_results.motm\" FROM `users`,`fixtures_results`,`cup_results` WHERE `accept`='1' ORDER BY `fixtures_results.motm` DESC") or die(mysql_error());
while($member = mysql_fetch_array($member_query))
{
if(get_magic_quotes_runtime())
{
$member['username'] = stripslashes($member['username']);
}
}

print_r($member);
?>
</table>[/code]

You could then use the same methods, to Order by COUNT(`fixtures_results.id`) + COUNT(`cup_results.id`) DESC, then by player name ASC (if there are more than one player with the same score)..

[PHP12345]

Hi,

Thanks for that,

The code you suggested is giving an error message...


[i]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*,COUNT(`fixtures_results.id`) AS "fixtures_results.motm" FROM `users`,`fixtures' at line 1[/i]


The webhost is currently running MySQL 4.1.11 (not sure if that makes a difference).

[SunD]

Tables:

[b]tmp_Location (LocationID, Location)
tmp_User (UserID, UserName, LocationID)[/b]

This is the sql statement that would produce the Location and the number of Users on that Location.

[b]Select b.Location, Count(b.Location) As LocationCount
From tmp_User a Left Outer Join tmp_Location b On a.LocationID = b.LocationID
Group By b.Location
Order By LocationCount Desc[/b]

Figure it out.

[SunD]

OOOOOOppppppppppppppsssssssss!!!!!! ;D
I forgot the top 10!!!!!

Tables:

[b]tmp_Location (LocationID, Location)
tmp_User (UserID, UserName, LocationID)[/b]

This is the sql statement that would produce the Location and the number of Users on that Location.

[b]Select Top 10 b.Location, Count(b.Location) As LocationCount
From tmp_User a Left Outer Join tmp_Location b On a.LocationID = b.LocationID
Group By b.Location
Order By LocationCount Desc[/b]

Figure it out.