Ideas for a simple script

get the 5 most ranked locations....

[omoutop]

Hi to all!

I have the following script which displays the locations as well as the scores (avg) regarding to a topic.....take a look:
/////////////////////////////////////////////////////////
include "dbconnect.php";
$topic = ($_REQUEST ['topic']);
if (!$topic)
{
bla bla bla (Presenting a simple form to user.....)
}
else
{
$CountLocations = "select count(distinct Location) as Location from Scores where Flag='A'";
$CountLocations2 = mysql_query($CountLocations);
$CountLocations3 = mysql_result($CountLocations2, 0, "Location");
echo "Total Locations are: <strong> $CountLocations3 </strong>";
echo '<p>';
$Get_Locations = "select distinct Location from Scores where Flag='A'";
$Get_Locations2 = mysql_query($Get_Locations);
while ($rowLocations = mysql_fetch_assoc($Get_Locations2))
{
$location = $rowLocations ['Location'];
echo $location;
$query1Town_Vil = "select sum($topic) as $topic from Scores where Location = '$location' and Flag = 'A'";

$query2Town_Vil = "select count($topic) as $topic from Scores where Location = '$location' and $topic > '0' and Flag = 'A'";

$result1Town_Vil = mysql_query($query1Town_Vil);

$result2Town_Vil = mysql_query($query2Town_Vil);

$resTown_Vil = mysql_fetch_assoc($result2Town_Vil);

$resTotalVTown_Vil = mysql_result($result1Town_Vil, 0, "$topic");

$resTotalPTown_Vil = $resTown_Vil["$topic"];

$Avg1Town_Vil = $resTotalVTown_Vil / $resTotalPTown_Vil;

$AvgTown_Vil = round($Avg1Town_Vil, 1);
echo '<br>';
echo $AvgTown_Vil;
echo '<br>';

};
}
////////////////////////////////////////////////////////
this script works fine and presents data like this:
/////////////////////////////////////////////////////////
Total Locations are: 41
Mykonos
1
Athens
2
Ithaca
4.5
Santorini
1
////////////////////////////////////////////////////
etc.....

HOW CAN I GET THE 5 MOST RATED LOCATIONS INTO DESC ORDER?
i dont want to display all of them there is no point.....

ANY IDEAS?

Thx in advance

[mcibor]

I'm not sure in this case, but usually it is done with:

SELECT rank, location FROM ranks ORDER BY rank DESC LIMIT 5;

Michal Cibor

I don't know your sql structure.

[chriswragg]

I beleive this might work:

Replace:
echo $location;
With:
$addlocation = $location;
$newlocation = array_merge((array)$newlocation, (array)$addlocation);

Replace:
echo '<br>';
echo $AvgTown_Vil;
echo '<br>';
With:
$addscore = $AvgTown_Vil;
$newscore = array_merge((array)$newscore, (array)$addscore);

And insert this after the end of the while loop:
foreach ($newscore as $key => $val) {
$$val = $key;
}
rsort($newscore);
$i = 0;
foreach ($newscore as $key => $val) {
if($i < 5){
echo $newlocation[$$val]."<br>".$val."<br>";
$i++;
}else{
continue;
}
}

As I said it "MIGHT" work, but I can't test it actually with the database. Give it a try and post again.

[omoutop]

Thx for everything guys...
It works my friend But it displays the first location 2 times instead of 1....ANY IDEAS?
I have also imported some code to display ranks and locations in graphs....

/////////////////CODE////////////////////
<?
include "dbconnect.php";
$topic = ($_REQUEST ['topic']);
if (!$topic)
{

}
else
$limit=5;
include('phpHtmlChart.php');
{
$CountLocations = "select count(distinct Location) as Location from Scores where Flag='A'";
$CountLocations2 = mysql_query($CountLocations);
$CountLocations3 = mysql_result($CountLocations2, 0, "Location");
echo "Total Locations are: <strong> $CountLocations3 </strong>";
echo '<br>';
echo "Topic is: <strong> $topic</strong>";
echo '<p>';

$Get_Locations = "select distinct Location as Location from Scores where Flag='A' limit $limit";

$Get_Locations2 = mysql_query($Get_Locations);
while ($rowLocations = mysql_fetch_assoc($Get_Locations2))
{
$location = $rowLocations ['Location'];
$addlocation = $location;
$newlocation = array_merge((array)$newlocation, (array)$addlocation);

$query1Town_Vil = "select sum($topic) as $topic from Scores where Location = '$location' and Flag = 'A'";

$query2Town_Vil = "select count($topic) as $topic from Scores where Location = '$location' and $topic > '0' and Flag = 'A'";

$result1Town_Vil = mysql_query($query1Town_Vil);

$result2Town_Vil = mysql_query($query2Town_Vil);

$resTown_Vil = mysql_fetch_assoc($result2Town_Vil);

$resTotalVTown_Vil = mysql_result($result1Town_Vil, 0, "$topic");

$resTotalPTown_Vil = $resTown_Vil["$topic"];

$Avg1Town_Vil = $resTotalVTown_Vil / $resTotalPTown_Vil;

$AvgTown_Vil = round($Avg1Town_Vil, 1);
$addscore = $AvgTown_Vil;
$newscore = array_merge((array)$newscore, (array)$addscore);

echo '<div align=center>';

};
foreach ($newscore as $key => $val) {
$$val = $key;
}
rsort($newscore);
$i = 0;
foreach ($newscore as $key => $val) {
if($i < 5){
echo $newlocation[$$val]."<br>".$val."<br>";
$aGraphData = Array

(array($newlocation[$$val], $val, '/10'));

echo phpHtmlChart($aGraphData, 'H', "Average $topic Scores", '<span class= greeka>Average <strong>Scores</strong></class>', '10pt', 400, 'px', 15, 'px');

$i++;

}else{
continue;

}
}

}

?>

[omoutop]

I am sorry guys....my results are not OK...I am still trying but nothing...I have made the changes to the code (excluded graph funtion, and limit in location query) to see if chriswragg's suggestion could work....unfortunately I get only the same location with the highest rank dislpayed 5 times.....but anyway THX FOR EVERYTHING!

/////////////CURRENT CODE///////////////////

<?
include "dbconnect.php";
$topic = ($_REQUEST ['topic']);
if (!$topic)
{

}
else

include('phpHtmlChart.php');
{
$CountLocations = "select count(distinct Location) as Location from Scores where Flag='A'";
$CountLocations2 = mysql_query($CountLocations);
$CountLocations3 = mysql_result($CountLocations2, 0, "Location");
echo "Total Locations are: <strong> $CountLocations3 </strong>";
echo '<br>';
echo "Topic is: <strong> $topic</strong>";
echo '<p>';

$Get_Locations = "select distinct Location as Location from Scores where Flag='A'";

$Get_Locations2 = mysql_query($Get_Locations);
while ($rowLocations = mysql_fetch_assoc($Get_Locations2))
{
$location = $rowLocations ['Location'];
$addlocation = $location;
$newlocation = array_merge((array)$newlocation, (array)$addlocation);

$query1Town_Vil = "select sum($topic) as $topic from Scores where Location = '$location' and Flag = 'A'";

$query2Town_Vil = "select count($topic) as $topic from Scores where Location = '$location' and $topic > '0' and Flag = 'A'";

$result1Town_Vil = mysql_query($query1Town_Vil);

$result2Town_Vil = mysql_query($query2Town_Vil);

$resTown_Vil = mysql_fetch_assoc($result2Town_Vil);

$resTotalVTown_Vil = mysql_result($result1Town_Vil, 0, "$topic");

$resTotalPTown_Vil = $resTown_Vil["$topic"];

$Avg1Town_Vil = $resTotalVTown_Vil / $resTotalPTown_Vil;

$AvgTown_Vil = round($Avg1Town_Vil, 1);
$addscore = $AvgTown_Vil;
$newscore = array_merge((array)$newscore, (array)$addscore);

echo '<div align=center>';

};
foreach ($newscore as $key => $val) {
$$val = $key;
}
rsort($newscore);
$i = 0;
foreach ($newscore as $key => $val) {
if($i < 5){
echo $newlocation[$$val]."<br>".$val."<br>";
/*
$aGraphData = Array

(array($newlocation, $val, '/10'));

echo phpHtmlChart($aGraphData, 'H', "Average $topic Scores", '<span class= greeka>Average <strong>Scores</strong></class>', '10pt', 400, 'px', 15, 'px');
*/
$i++;

}else{
continue;

}
}

}

?>

[chriswragg]

Hmmm.. When I tested this just by adding example strings, I worked fine.

You do not need the LIMIT in the MYSQL query, because as you are not sorting the query, this will just return the first 5 results, and not the top 5. <EDIT> Did not see your previous post ;)</edit>

Could you also post the data that is presented (like in the first post)

[omoutop]

Thx again...
Limit is deleted....
current code is:

<?
include "dbconnect.php";
$topic = ($_REQUEST ['topic']);
if (!$topic)
{

}
else

include('phpHtmlChart.php');
{
$CountLocations = "select count(distinct Location) as Location from Scores where Flag='A'";
$CountLocations2 = mysql_query($CountLocations);
$CountLocations3 = mysql_result($CountLocations2, 0, "Location");
echo "Total Locations are: <strong> $CountLocations3 </strong>";
echo '<br>';
echo "Topic is: <strong> $topic</strong>";
echo '<p>';

$Get_Locations = "select distinct Location as Location from Scores where Flag='A'";

$Get_Locations2 = mysql_query($Get_Locations);
while ($rowLocations = mysql_fetch_assoc($Get_Locations2))
{
$location = $rowLocations ['Location'];
$addlocation = $location;
$newlocation = array_merge((array)$newlocation, (array)$addlocation);

$query1Town_Vil = "select sum($topic) as $topic from Scores where Location = '$location' and Flag = 'A'";

$query2Town_Vil = "select count($topic) as $topic from Scores where Location = '$location' and $topic > '0' and Flag = 'A'";

$result1Town_Vil = mysql_query($query1Town_Vil);

$result2Town_Vil = mysql_query($query2Town_Vil);

$resTown_Vil = mysql_fetch_assoc($result2Town_Vil);

$resTotalVTown_Vil = mysql_result($result1Town_Vil, 0, "$topic");

$resTotalPTown_Vil = $resTown_Vil["$topic"];

$Avg1Town_Vil = $resTotalVTown_Vil / $resTotalPTown_Vil;

$AvgTown_Vil = round($Avg1Town_Vil, 1);
$addscore = $AvgTown_Vil;
$newscore = array_merge((array)$newscore, (array)$addscore);

echo '<div align=center>';

};
foreach ($newscore as $key => $val) {
$$val = $key;
}
rsort($newscore);
$i = 0;
foreach ($newscore as $key => $val) {
if($i < 5){
echo $newlocation[$$val]."<br>".$val."<br>";
$i++;
}else{
continue;
}
}

/*
$aGraphData = Array

(array($newlocation, $val, '/10'));

echo phpHtmlChart($aGraphData, 'H', "Average $topic Scores", '<span class= greeka>Average <strong>Scores</strong></class>', '10pt', 400, 'px', 15, 'px');
*/
}
?>
///////////////////////////////////////////
Results in the BROWSER:

Total Locations are: 41
Topic is: Beach

Halkidiki
10
Halkidiki
10
Halkidiki
10
Halkidiki
10
Halkidiki
10
///////////////////////////////////
presents the highest ranked location along with the avg score 5 times....
I am trying to fix it...

[chriswragg]

Found a problem. If two locations have the same rating, the the statement:

foreach ($newscore as $key => $val) {
$$val = $key;
}

does not set two different variables for each place, but overwrites the first variable. So in you case, you must has 5 or more places that scored 10

[omoutop]

I will check it out immediatelly...thx
I'll post again

[omoutop]

u r right!
unfortunately I can not change any scores...is there any way I can bypass this problem and make it work?

Thx again, really appreciate your help!

[chriswragg]

Sorted it!

Replace:
foreach ($newscore as $key => $val) {
$$val = $key;
}
rsort($newscore);
$i = 0;
foreach ($newscore as $key => $val) {
if($i < 5){
echo $newlocation[$$val]."<br>".$val."<br>";
$i++;
}else{
continue;
}
}

With:
array_multisort($newscore, SORT_DESC, $newlocation);
$i = 0;
foreach ($newscore as $key => $val) {
if($i < 5){
echo $newlocation[$key]."<br>".$val."<br>";
$i++;
}else{
continue;
}
}

It actually ends up being simpler than in the first place ;)

[omoutop]

U R GREAT!
IT WORKS
THX FOR EVERYTHING MY FRIEND....

i'll try to integrate the charts now to see how it works!

[omoutop]

I need your help one more time if u can...u have already done TOO MUCH and I thank you for that...i am trying to import the graph fuction in my script:
/////////////////////////////////////////////
$aGraphData = Array

(array($newlocation[$key], $val, '/10'));

echo phpHtmlChart($aGraphData, 'H', "Average $topic Scores", '<span class= greeka>Average <strong>Scores</strong></class>', '10pt', 400, 'px', 15, 'px');
/////////////////////////////////////////////////
however, the array I have is not good and diaplys each locatio-rating on a different graph!

is there any way u can help me fix it?

/////////////////COMPLETE CODE///////////////////
<?
include "dbconnect.php";
$topic = ($_REQUEST ['topic']);
if (!$topic)
{

}
else

include('phpHtmlChart.php');
{
$CountLocations = "select count(distinct Location) as Location from Scores where Flag='A'";
$CountLocations2 = mysql_query($CountLocations);
$CountLocations3 = mysql_result($CountLocations2, 0, "Location");
echo "Total Locations are: <strong> $CountLocations3 </strong>";
echo '<br>';
echo "Topic is: <strong> $topic</strong>";
echo '<p>';

$Get_Locations = "select distinct Location as Location from Scores where Flag='A'";
$Get_Locations2 = mysql_query($Get_Locations);
while ($rowLocations = mysql_fetch_assoc($Get_Locations2))
{
$location = $rowLocations ['Location'];
$addlocation = $location;
$newlocation = array_merge((array)$newlocation, (array)$addlocation);

$query1Town_Vil = "select sum($topic) as $topic from Scores where Location = '$location' and Flag = 'A'";

$query2Town_Vil = "select count($topic) as $topic from Scores where Location = '$location' and $topic > '0' and Flag = 'A'";

$result1Town_Vil = mysql_query($query1Town_Vil);

$result2Town_Vil = mysql_query($query2Town_Vil);

$resTown_Vil = mysql_fetch_assoc($result2Town_Vil);

$resTotalVTown_Vil = mysql_result($result1Town_Vil, 0, "$topic");

$resTotalPTown_Vil = $resTown_Vil["$topic"];

$Avg1Town_Vil = $resTotalVTown_Vil / $resTotalPTown_Vil;

$AvgTown_Vil = round($Avg1Town_Vil, 1);
$addscore = $AvgTown_Vil;
$newscore = array_merge((array)$newscore, (array)$addscore);

echo '<div align=center>';

};
array_multisort($newscore, SORT_DESC, $newlocation);
$i = 0;
foreach ($newscore as $key => $val) {
if($i < 5){
echo $newlocation[$key]."<br>".$val."<br>";

$i++;
$aGraphData = Array

(array($newlocation[$key], $val, '/10'));

echo phpHtmlChart($aGraphData, 'H', "Average $topic Scores", '<span class= greeka>Average <strong>Scores</strong></class>', '10pt', 400, 'px', 15, 'px');

}else{
continue;
}
}

}
?>

[chriswragg]

In the Array:

$aGraphData = Array(array($newlocation[$key], $val, '/10')); 

$newlocation[$key] is specifically the location in that particular loop and $val is the score. For all of the locations and scores, you can just use $newlocation and $newscore. However this will include every single location and rating. Is this what you want, or do you want just the top 5?

[omoutop]

Just the top 5 my friend...but the proble is that when i insert the chart function within the loop i get 5 different charts with one location each...if i throw it outside the loop i get nothing...

[chriswragg]

I think if you just place this:

$addt5location = $newlocation[$key];
$t5location = array_merge((array)$t5location, (array)$addt5location);
$addt5score = $val;
$t5score = array_merge((array)$t5score, (array)$addt5score);

inside the loop, just under $i++; then you can use $t5location and $t5score in the array. The array however must be outside of the loop, or you will get 5 graphs.

[omoutop]

It was not working with the last bit of code....however...it works like that:

//////////////////CODE//////////////////
<?
include "dbconnect.php";
$topic = $HTTP_POST_VARS['topic'];
//$topic = ($_REQUEST ['topic']);
if (!$topic)
{
?>
<div align=center>
<form name="form2" action="Test2.php" method="post">
<p>
<select name="topic">

<option value="Town_Vil" selected>Towns/Villages</option>
<option value="Archi">Architecture</option>
<option value="Beach">Beaches</option>
<option value="LandSc">Landscapes</option>
<option value="TreeCov">Tree Cover</option>
<option value="SightSe">Sightseeing</option>
<option value="Night">Nightlife</option>
<option value="Restau">Restaurants</option>
<option value="PeaAQu">Peace & Quiet</option>
<option value="Tourist">Tourist</option>
<option value="Accom">Accommodation</option>
<option value="People">People</option>
<option value="Nud">Nudity</option>
<option value="Activ">Activities</option>
<option value="Spor">Sports</option>
</select>
<input type="submit" name="Submit" value="Go!">

</form>

<?
}
else
{
$topic2 = str_replace('Town_Vil', 'Towns/Villages', $topic);

include('phpHtmlChart.php');
$CountLocations = "select count(distinct Location) as Location from Scores where Flag='A'";
$CountLocations2 = mysql_query($CountLocations);
$CountLocations3 = mysql_result($CountLocations2, 0, "Location");
echo '<div align=center>';
echo "Total Locations are: <strong> $CountLocations3 </strong>";
echo '<br>';
echo "Topic: <strong> $topic2 </strong>";
echo '<p>';

$Get_Locations = "select distinct Location as Location from Scores where Flag='A'";
$Get_Locations2 = mysql_query($Get_Locations);
while ($rowLocations = mysql_fetch_assoc($Get_Locations2))
{
$location = $rowLocations ['Location'];
$addlocation = $location;
$newlocation = array_merge((array)$newlocation, (array)$addlocation);

$query1Town_Vil = "select sum($topic) as $topic from Scores where Location = '$location' and Flag = 'A'";

$query2Town_Vil = "select count($topic) as $topic from Scores where Location = '$location' and $topic > '0' and Flag = 'A'";

$result1Town_Vil = mysql_query($query1Town_Vil);

$result2Town_Vil = mysql_query($query2Town_Vil);

$resTown_Vil = mysql_fetch_assoc($result2Town_Vil);

$resTotalVTown_Vil = mysql_result($result1Town_Vil, 0, "$topic");

$resTotalPTown_Vil = $resTown_Vil["$topic"];

$Avg1Town_Vil = $resTotalVTown_Vil / $resTotalPTown_Vil;

$AvgTown_Vil = round($Avg1Town_Vil, 1);
$addscore = $AvgTown_Vil;
$newscore = array_merge((array)$newscore, (array)$addscore);

echo '<div align=center>';

};
array_multisort($newscore, SORT_DESC, $newlocation);

$i = 0;
foreach ($newscore as $key => $val) {

if($i < 5){
/*

?>

<table width="15%" border="1" cellspacing="0" cellpadding="0">
<tr>
<td width="75%"> <font color="#FFFFFF"><strong><? echo $newlocation[$key]?></strong></font> </td>
<td width="25%"> <div align="right"><font color="#FFFFFF"><strong><? echo $val?>/10</strong></font></div></td>
</tr>
</table>
<?
*/
$aGraphData{"$i"} = array($newlocation[$key], $val);

$i++;

}else{

continue;
}

}

echo phpHtmlChart($aGraphData, '', "", '', '10pt', 200, 'px', 10, 'px');

?>

Thx for everything again....!