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 1 of 1
  1. #1
    Senior Coder
    Join Date
    May 2006
    Posts
    1,680
    Thanks
    28
    Thanked 4 Times in 4 Posts

    How do I use PHP to filter out these extra rows ?

    Hi,
    I think that maybe this needs some PHP processing to complete
    the filtering I have been trying with pure sql but haven't been able to do it.


    I want to get a subset from my table that includes rows that have an
    item (cb_id) with a unique ip address ( ip_adr).

    To be in the subset there should be at least two rows and if there are
    more than four rows then the rows above the forth should be filtered out.

    eg. table contains:

    EBFORTUNE, IP add: 55.167.889.182
    EBFORTUNE, 78.167.109.182
    FEDYDE, : 85.106.68.197
    FEDYDE, 85.176.68.227
    FEDYDE, 89.106.68.127
    FHHHH45,78.167.109.182 // only one - not enough - filter out
    P97GFRH, 56.107.9.182 // only one - not enough - filter out
    RSEDE38M, 78.167.17.182
    RSEDE38M, 78.167.109.182
    RSEDE38M, 23.167.109.182 // 7 the extra 3 rows must be filtered out
    RSEDE38M, 17.167.19.2
    RSEDE38M, 712.17.39.182
    RSEDE38M, 78.16.143.182
    RSEDE38M, 78.19.109.102
    GGG72FHH, 178.7.109.18 // only one - not enough - filter out


    Then the result I want to get is (min 2, max 4):

    EBFORTUNE,55.167.889.182
    EBFORTUNE, 78.167.109.182
    FEDYDE, 85.106.68.197
    FEDYDE,85.176.68.227
    FEDYDE, 89.106.68.127
    RSEDE38M, 17.167.19.2
    RSEDE38M, 712.17.39.182
    RSEDE38M, 78.16.143.182
    RSEDE38M, 78.19.109.102


    I am quite close to this but
    I just need to take out the extra entries where the number
    of rows exceeds the ceiling.


    PHP Code:
    $days7ago $today-604800// Also data must be within last 7 days.
    $min_hits 2;
    $ceiling 4;

    echo 
    "<br>Min_hits: $min_hits, Ceiling: $ceiling<br>Having sql:<br>";

    $min_hits $min_hits-1// because I use the less than operator.
      
     
    $sql "SELECT DIC.ip_adr, COUNT(*) AS howmany FROM (SELECT DISTINCT ip_adr, cb_id FROM cb_promo WHERE link_dt BETWEEN $days7ago and $today)
      AS DIC    GROUP BY DIC.cb_id HAVING COUNT(*) > $min_hits"
    ;
      
     
    $result mysql_query($sql)    or die("could not find PROMO"mysql_error());  
     while( 
    $row mysql_fetch_assoc($result)) {
      
    print_r($row);
      echo 
    "<br>";
     } 
    Result:

    Min_hits: 2, Ceiling: 4
    Having sql:
    Array ( [ip_adr] => 478.167.109.182 [howmany] => 6 )
    Array ( [ip_adr] => 78.14.109.162 [howmany] => 2 )

    Actually I am not very sure how I get my 8 rows
    out of this result .... but it looks like I am close.


    So I think that I need to get rid of extra rows from the array
    that exceed the ceiling number.

    But not sure how to do that one.

    Any ideas on how I can complete my filtering ?

    Either by expanding the query or by processing the resulting array
    with PHP.

    It must be possible to do this, I just can not figure it out

    Thanks
    Last edited by jeddi; 01-18-2010 at 04:05 PM.
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.


 

Posting Permissions

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