Jump to content

Help with sorting in order please


PHP12345

Recommended Posts

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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)..
Link to comment
Share on other sites

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).
Link to comment
Share on other sites

  • 2 weeks later...
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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.