Jump to content


Photo

Help with sorting in order please


  • Please log in to reply
15 replies to this topic

#1 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 03 July 2006 - 03:17 PM

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.

#2 birdie

birdie
  • Members
  • PipPipPip
  • Advanced Member
  • 65 posts
  • LocationBirmingham UK

Posted 03 July 2006 - 03:34 PM

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..

$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

Hope that helps ;D

#3 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 03 July 2006 - 03:56 PM

You can do something like this:

<?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);};
?>
 

Orio.
Think you're smarty?

(Gone until 20 to November)

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 03 July 2006 - 04:57 PM

Incorporate this:

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;

Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 03 July 2006 - 06:51 PM

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.

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

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.

#6 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 July 2006 - 11:19 AM

Can anyone help a PHP n00b  ???

Thanks in advance  ;)

#7 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 July 2006 - 02:04 PM

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!!!

#8 zawadi

zawadi
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationChippenham UK

Posted 04 July 2006 - 02:10 PM

can you tell me if there is a new row for each MoTM for each player?
That's the beauty of asymmetric warfare. You don't need a lot of money, or an army of people. You can do it from the comfort of your living room, with a beer in your hand.

#9 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 July 2006 - 02:13 PM

Hi,

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



#10 zawadi

zawadi
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationChippenham UK

Posted 04 July 2006 - 02:34 PM

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  ???
That's the beauty of asymmetric warfare. You don't need a lot of money, or an army of people. You can do it from the comfort of your living room, with a beer in your hand.

#11 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 04 July 2006 - 02:41 PM

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)

$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");

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

$motm = mysql_num_rows($motm1_query) + mysql_num_rows($motm2_query);

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..

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


#12 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 05 July 2006 - 10:05 AM

Is there anyone who can help?

I'm struggling with my temporary tables  :'(

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

#13 heckenschutze

heckenschutze
  • Members
  • PipPipPip
  • Advanced Member
  • 257 posts
  • LocationAustralia

Posted 05 July 2006 - 11:23 AM

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..:

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

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)..


#14 PHP12345

PHP12345
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 05 July 2006 - 01:06 PM

Hi,

Thanks for that,

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


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


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

#15 SunD

SunD
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 20 July 2006 - 06:08 AM

Tables:

tmp_Location (LocationID, Location)
tmp_User (UserID, UserName, LocationID)


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

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


Figure it out.

#16 SunD

SunD
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 20 July 2006 - 06:11 AM

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

Tables:

tmp_Location (LocationID, Location)
tmp_User (UserID, UserName, LocationID)


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

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


Figure it out.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users