Resolved sort and list variable by total count

sonny · 12-08-2012, 02:17 AM

Hi, I am trying to sort results by total counts, first time trying to
do something like this, example I have 3 variables I need to
display by highest count order? or do I need to keep the name
and numbers separate?

PHP Code:


			
$google = "25 Google";

$bing = "55 Bing":

$yandex = "75 Yandex":

can someone give a basic array sort example how to do this?

Thanks
Sonny

Inigoesdr · 12-08-2012, 03:20 AM

Here is a simple way to do it with an array:

PHP Code:


			
$input = array(
                '25 Google'
                ,'75 Yandex'
                ,'55 Bing'
            );


var_dump($input);

/* Output:

array(3) {
 [0]=>
 string(9) "25 Google"
 [1]=>
 string(9) "75 Yandex"
 [2]=>
 string(7) "55 Bing"
}

*/

natsort($input);

var_dump($input);

/* Output:

array(3) {
 [0]=>
 string(9) "25 Google"
 [2]=>
 string(7) "55 Bing"
 [1]=>
 string(9) "75 Yandex"
}
*/

$descending = array_reverse($input);
var_dump($descending);

/* Output: 

array(3) {
 [0]=>
 string(9) "75 Yandex"
 [1]=>
 string(7) "55 Bing"
 [2]=>
 string(9) "25 Google"
}*/

sonny · 12-08-2012, 07:20 PM

Hi
I'm really bad with arrays, Can someone please show how I can properly and efficiently
echo these results below in high to low count order?. I tried using those example arrays
above by putting these echos in a $var, but cannot get it right.

PHP Code:


			
$result1 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%Googlebot/%'"); 

$google = mysql_num_rows($result1);

if ($google) echo "<b>".$google."</b> Google<br>";



$result2 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%yandexbot%'"); 

$yandex = mysql_num_rows($result2);

if ($yandex) echo "<b>".$yandex."</b> Yandex<br>";



$result3 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%bingbot%'"); 

$bing = mysql_num_rows($result3);

if ($bing) echo "<b>".$bing."</b> Bing<br>";

Thanks
Sonny

Redcoder · 12-09-2012, 11:48 AM

Instead of making something new, I'll just combine what Inigoesdr wrote.

The natsort function is the most important thing here.

PHP Code:


			
$result1 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%Googlebot/%'"); 
$google = mysql_num_rows($result1);


$result2 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%yandexbot%'"); 
$yandex = mysql_num_rows($result2);


$result3 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%bingbot%'"); 
$bing = mysql_num_rows($result3);

$input[0] = $google." google";

$input[1] = $yandex." yandex";

$input[2] = $bing." bing";


var_dump($input);

/* Output:

array(3) {
 [0]=>
 string(9) "25 Google"
 [1]=>
 string(9) "75 Yandex"
 [2]=>
 string(7) "55 Bing"
}

*/

natsort($input);

var_dump($input);

/* Output:

array(3) {
 [0]=>
 string(9) "25 Google"
 [2]=>
 string(7) "55 Bing"
 [1]=>
 string(9) "75 Yandex"
}
*/

$descending = array_reverse($input);
var_dump($descending);

/* Output: 

array(3) {
 [0]=>
 string(9) "75 Yandex"
 [1]=>
 string(7) "55 Bing"
 [2]=>
 string(9) "25 Google"
}*/

To get your desired for mat you'll just use a foreach loop to iterate through the array. E.g

PHP Code:


			
foreach($descending as $key=>$value)
{

echo $value."<br />";

}
/*This echoes out to the browser

75 Yandex
55 Bing
25 Google
*/

Junsee · 12-09-2012, 03:26 PM

I would tackle this in the select statement myself. It seems like a lot of work putting this into arrays and stuff.

however I am taking some guesses, but I think this should do it

PHP Code:


			
Select ua, count(ua) FROM test WHERE ua LIKE '%Googlebot%'

union

Select ua, count(ua) FROM test WHERE ua LIKE '%yandexbot%'

union

Select ua, count(ua) FROM test WHERE ua LIKE '%bingbot%'

GROUP BY ua 

ORDER BY count(ua) DESC;

the output will result in two columns and the full name of first find (for example)
[ua] [count(ua)]
[Googlebot1] [6]
[Yandex] [5]
[Bing] [3]

EDIT just double check the spelling of the search LIKE

sonny · 12-09-2012, 09:15 PM

Ok got it, thanks

PHP Code:


			
natsort($input);

$descending = array_reverse($input);

foreach ($descending as $bot) {

echo $bot;

}

Sonny

sonny · 12-10-2012, 07:33 PM

Quote:

Originally Posted by Junsee

I would tackle this in the select statement myself. It seems like a lot of work putting this into arrays and stuff.

however I am taking some guesses, but I think this should do it

PHP Code:


			
Select ua, count(ua) FROM test WHERE ua LIKE '%Googlebot%'

union

Select ua, count(ua) FROM test WHERE ua LIKE '%yandexbot%'

union

Select ua, count(ua) FROM test WHERE ua LIKE '%bingbot%'

GROUP BY ua 

ORDER BY count(ua) DESC;

the output will result in two columns and the full name of first find (for example)
[ua] [count(ua)]
[Googlebot1] [6]
[Yandex] [5]
[Bing] [3]

EDIT just double check the spelling of the search LIKE

Hi,

Your way sounds good as well, but in my case I am checking more
then just these bots posted based on a lot more criteria then listed
like host for crawl, and many combinations of different scenarios that
match and don't, trying too weed out real bots best I can, and identify
bad ones, I even check for ua string length. length(ua) < '40'

doing individual query's., was the only way to go in my case, I am a one
script do 20 things efferent thinking type of person, but it aint happening
with all that.

Sonny

Fou-Lu · 12-10-2012, 07:40 PM

You still don't need to query multiple times.
Use multiple conditions to pull what you need. I wouldn't bother with a union here, but a simple WHERE condition = x OR condition = y OR condition LIKE z is the approach you can take.
Not sure a count would help you here. The original code blocks don't indicate that you actually count anything within the database, rather its stored as a string (don't know why it would be, but that's what it looks like).

sonny · 12-10-2012, 08:05 PM

Quote:

Originally Posted by Fou-Lu

You still don't need to query multiple times.
Use multiple conditions to pull what you need. I wouldn't bother with a union here, but a simple WHERE condition = x OR condition = y OR condition LIKE z is the approach you can take.
Not sure a count would help you here. The original code blocks don't indicate that you actually count anything within the database, rather its stored as a string (don't know why it would be, but that's what it looks like).

Don't forget you are really good at this, I don't know a tenth of what you do, query
examples mentioned here are to get counts, but the conditions are the key for the way I
went about doing things (not mentioned). my initial post was nothing more then a basic
example to get an answer on method. basically asking how to best sort.

also at the end of the day, basically speaking, you'll always have to place
the $count along with the name string anyway. even if it could be done, it
would look like one big bowl of spaghetti. not to mention updating it, in the
future.

Thanks
Sonny

Fou-Lu · 12-10-2012, 08:28 PM

I'm referring to this though:

PHP Code:


			
$result1 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%Googlebot/%'"); 
$google = mysql_num_rows($result1);
if ($google) echo "<b>".$google."</b> Google<br>";

$result2 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%yandexbot%'"); 
$yandex = mysql_num_rows($result2);
if ($yandex) echo "<b>".$yandex."</b> Yandex<br>";

$result3 = mysql_query("SELECT ua FROM test WHERE ua LIKE '%bingbot%'"); 
$bing = mysql_num_rows($result3);
if ($bing) echo "<b>".$bing."</b> Bing<br>";

You can simply use:

PHP Code:


			
$sQry = "SELECT ua FROM test WHERE ua LIKE '%googlebot%' OR ua LIKE '%yanderexbot' OR ua LIKE '%bingbot%'";

The sorting is still an issue of course.

Given your second post here, you can use the count (I read this wrong, the count should be what you want as you've used num_rows where I thought you pulled a fetch).

Code:

SELECT ua, count(ua) AS cnt
FROM test
WHERE ua LIKE '%googlebot%' OR ua LIKE '%yanderexbot' OR ua LIKE '%bingbot%'
GROUP BY ua
ORDER BY cnt ASC

MySQL indicates it will allow the use of an alias in group by, order by, and having clauses.
If you want to use more refinement based on count, you can make use of aggregate HAVING clauses. Structure is always FIELDS, WHERE, GROUP BY, HAVING, ORDER. So if you only want where count < 40, that would simply take the having:

Code:

SELECT ua, count(ua) AS cnt
FROM test
WHERE ua LIKE '%googlebot%' OR ua LIKE '%yanderexbot' OR ua LIKE '%bingbot%'
GROUP BY ua
HAVING cnt < 40
ORDER BY cnt ASC

For example.

So yeah, my bad you need to use the count() option here if you want to do it within a query.

sonny · 12-10-2012, 09:09 PM

My conditions are far more complex then looking for just google, yandex or bing
like I said big bowl of spaghetti, any other way then the way I did it. see below

NOTE the conditions,, that's the reason for
doing it this way. also easy to edit or add.

PHP Code:


			
// specific conditions do not want to bore...

$google = mysql_num_rows($result1);

if ($google) $input[] = "$google."</b> Google";



// specific conditions do not want to bore...

$yandex = mysql_num_rows($result2);

if ($yandex) $input[] = $yandex."</b> Yandex";



// specific conditions do not want to bore...

$bing = mysql_num_rows($result3);

if ($bing) $input[] = $bing."</b> Bing";



// specific conditions do not want to bore...

$bad = mysql_num_rows($result4);

if ($bad) $input[] = $bad."</b> Bad Bot";



natsort($input);

$descending = array_reverse($input);

foreach ($descending as $bot) {

echo "<font size=3 color=red><b>$bot <br>";

}