...

View Full Version : How do I use PHP to filter out these extra rows ?



jeddi
01-18-2010, 05:02 PM
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.



$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 :o .... 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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum