Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts

    sort and list variable by total count

    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
    Last edited by sonny; 12-09-2012 at 10:30 PM.

  • #2
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    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"
    }*/ 

  • Users who have thanked Inigoesdr for this post:

    sonny (12-09-2012)

  • #3
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder Redcoder's Avatar
    Join Date
    May 2012
    Location
    /dev/null
    Posts
    334
    Thanks
    2
    Thanked 48 Times in 47 Posts
    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
    */ 
    Last edited by Redcoder; 12-09-2012 at 10:42 PM.

  • Users who have thanked Redcoder for this post:

    sonny (12-09-2012)

  • #5
    Junsee
    Guest
    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 uacount(uaFROM test WHERE ua LIKE '%Googlebot%'
    union
    Select ua
    count(uaFROM test WHERE ua LIKE '%yandexbot%'
    union
    Select ua
    count(uaFROM test WHERE ua LIKE '%bingbot%'
    GROUP BY ua 
    ORDER BY count
    (uaDESC
    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
    Last edited by Junsee; 12-09-2012 at 04:30 PM.

  • #6
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Ok got it, thanks

    PHP Code:
    natsort($input);
    $descending array_reverse($input);
    foreach (
    $descending as $bot) {
    echo 
    $bot;

    Sonny
    Last edited by sonny; 12-09-2012 at 10:27 PM.

  • #7
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Junsee View Post
    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 uacount(uaFROM test WHERE ua LIKE '%Googlebot%'
    union
    Select ua
    count(uaFROM test WHERE ua LIKE '%yandexbot%'
    union
    Select ua
    count(uaFROM test WHERE ua LIKE '%bingbot%'
    GROUP BY ua 
    ORDER BY count
    (uaDESC
    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

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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).

  • #9
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    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
    Last edited by sonny; 12-10-2012 at 09:16 PM.

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #11
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    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."</bGoogle";

    // specific conditions do not want to bore...
    $yandex = mysql_num_rows($result2);
    if ($yandex) $input[] = $yandex."
    </bYandex";

    // specific conditions do not want to bore...
    $bing = mysql_num_rows($result3);
    if ($bing) $input[] = $bing."
    </bBing";

    // specific conditions do not want to bore...
    $bad = mysql_num_rows($result4);
    if ($bad) $input[] = $bad."
    </bBad Bot";

    natsort($input);
    $descending = array_reverse($input);
    foreach ($descending as $bot) {
    echo "
    <font size=3 color=red><b>$bot <br>";



  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •