Solved

Sort by most views

Posted on 2004-09-18
12
165 Views
Last Modified: 2008-03-03
Hey guys,
My problem is i have a page that displays files for download, and i need to make it so the user can select how they want to sort the downloads. One of the options is to sort them by the ones with the most hits appearing at the top and the lowest hits appearing at the bottom. I have written some code for it but it doesnt work because i am stuck with a problem, i will try and make it as clear as possible:

Example:

$table = array("1","8",6","9");
// So element 2 of $table is 8, and element 2 of $hits is 3, so table 8 has 3 hits, so i can use it when getting the file from the database.
$hits = array("7","3","4","5");

rsort($hits); // Sort $hits in order from lowest to highest, in this case 3457
$hits = array_reverse($hits); // Reverse $hits so now it is from highest to lowest, in this case 7543

But now it has scrambled things, because $hits is fine but it has been reversed, so meanwile element 2 of $table is 8 and element 2 of $hits is 5, witch is wrong, table 8 actully has the file of hits 3, but because things have been reversed in order to display highest to lowest it changes the correspondance between the 2 arrays elements. Understand? Please help... i need a simple as possible example or solution to this problem. Thanks
0
Comment
Question by:trent21
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 4

Expert Comment

by:cachedVB
ID: 12094047
are you loading this from a database? if so, you can add SORT BY hits DESC to the query
0
 

Author Comment

by:trent21
ID: 12094100
Yes i am loading it from a database, heres what needs to happen, i have the html to display each download stored in a database column called downloads, i store the download display html in the row named downloadtext and the hits for each download in the row hits and the id of the download in the row downloadid heres what i want to happen.

1. Get all downloads from the database and display them by the most hits at the top of the page and the lowest hits at the bottom.

So basicly do you mean, i have the query to get the hits witch is $query = "SELECT hits FROM downloads WHERE downloadid='$i'"; and $i is the download id witch has been iterated through with a for statement. So in order to get the hits and the downloadtext from the download id witch contains the most hits then display them by the highest hits first, i would do ?
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12094775
SELECT hits, downloadid, downloadtext FROM downloads ORDER BY hits DESC

You then iterate through the results displaying all your downloads order by hits, highest hits first.
0
 

Author Comment

by:trent21
ID: 12095128
Your code does not work, it simply orders them by there normal order.
0
 
LVL 49

Accepted Solution

by:
Roonaan earned 125 total points
ID: 12095283
Are you really sure? If I may ask, can you post a snippet of your code for us?

It should look something like:
<?php

$query_results = mysql_query('SELECT `hits`, `downloadid`, `downloadtext` FROM `downloads` ORDER BY `hits` DESC');

if(mysql_error())
{
  echo '<code>An error occured while processing your searchquery</code>';
}
else
{
  echo '<table>';
  echo '<tr><th>Hits</th><th>Id</th><th>Text</th></tr>';
  while($row = mysql_fetch_assoc($query_results))
  {
     echo '<tr><td>'.implode('</td><td>', $row).'</td></tr>';
  }
  echo '</table>';
}

And should result in an table displaying downloads ordered by hits. no doubt about it actually.

-r-
0
 
LVL 9

Expert Comment

by:techtonik
ID: 12099787
array_multisort($hits, SORT_ASC, $table);
// flag can also be SORT_DESC
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:trent21
ID: 12100277
It definetly does not work with what you gave Roonan, it displays them all in wrong error, here is what happens when i trim it down to just hits:

Hits Id Text
7
6
2
2
10
0
0
0

You can see its obviously in wrong order...
0
 

Author Comment

by:trent21
ID: 12100298
Here is the complete code, nevermind log.php, it has nothing to do with the problem...

<?php

include("log.php");

// Database
$db = mysql_connect("localhost","profchemx");
$que = "SELECT * FROM downloads";
$res = mysql_query($que);
$rows = mysql_num_rows($res);
$rowhits = array();
$tablenum = array();
// Database

for($i = 0; $i<($rows + 1); $i++)
{
// Database 2
$query = "SELECT hits, downloadid, downloadtext FROM downloads WHERE downloadid='$i' ORDER BY hits DESC";
$result = mysql_query($query);
$hits = mysql_result($result, 0);

$rowhits[$i] = $hits;
$tablenum[$i] = $i;

// Database 2
}


natsort($rowhits);
$rowhits = array_reverse($rowhits);

// Scan through array and print all its elements.
for($i = 0; $i<count($rowhits); $i++)
{
$quer = "SELECT downloadtext FROM downloads WHERE hits='$rowhits[$i]'";
$resul = mysql_query($quer);
$dcode = mysql_result($resul,0);

if(!$resul)
{
print mysql_error();
}
else
{
$dhits = getdownloads($i);
eval("\$dcode = \"$dcode\";");
print $dcode;

}
}
// Scan through array and print all its elements

?>

Now this code i wrote works, it displays downloads by there highest to lowest, but it displays them twice well allot of them, and we no as i said this is because the correspondance between the arrays tablenum and rowhits change when the array is reversed. Hope this can help...
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12100327
Yeah, but want I ment is that you have somewhat of an overkill on your queries, because afterall you would like to show ALL downloads available and not just a selection. Therefor you could just request them from the database in the correct ordering (order by hits in your/our case). This is done by using the code I posted earlier, but I suppose I am seeing things not that clearly this early in the morning.

What I see is happening in your code is this:
1. Retrieve all downloads
2. For each download retrieve the number of hits (which are already in the results done by the query at step 1)
3. Fill 2 paralel arrays with this data.
4. Do somekind of phpbased sorting (whereas sorting on mysql-level/step 1 should do the trick.)
5. Try to rematch the relation between the two arrays at stage 3.
6. Output things.

My suggestion would then be:
1. Retrieve all downloads ordered by hits
6. Output things.

possibly you could explain things in a non code matter like the stageplan I wrote, for me?

regards

-r-
0
 

Author Comment

by:trent21
ID: 12100864
Thats correct friend, you understand it well... i know the order should be right, but maybe it is the server im testing it on? It is a Lycos server, although the code you posted earlier doesnt print them by the highest first. This is very weird, it should but it doesnt, trust me, i no your right but what other options can we do here? What other SQL query can i try?
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12101053
You could use usort thats is easier than the way you are using now, and hopefully will work:

usort is used to execute a custom ordering using a callback function. in this callback function we compare the hits field and return -1 when a<b, 0 when a=b, 1 when a>b.

I'm not familiar with the php-limitations there might or might not be on lycos systems, so I can't say something usefull about that issue.

<?php
/*connect first*/

//query db
$q = mysql_query('select * from `downloads`);
if(mysql_error()) exit('DB flaw');

//fetch records
$records = array();
while($record = mysql_fetch_assoc($q))
  $record = $records;

//sort records
usort($records, 'sortbyhit');

echo '<div>We have '.count($records).' records.</div>';

//output data
echo '<table>';
foreach($records as $record)
{
  echo '<tr><td>'.implode('</td><td>', $record).'</td></tr>';
}
echo '</table>';
//supportive methods below
function sortbyhit($rec1, $rec2)
{
  if(!is_array($rec1) || !is_array($rec2)) return 0;

  if ($rec1['hits'])  == $rec2['hits']) {
       return 0;
   }
   return ($rec1['hits'])  < $rec2['hits']) ) ? -1 : 1;
}

?>
0
 
LVL 4

Expert Comment

by:cachedVB
ID: 12101250
Or if it isnt too much trouble move to another free php/sql host (like codewarz.net, the one i use)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

709 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now