How To Count Rows In DB Where Cell Value = Duplicate?

[Sussex_Chris]

I have a DB full of IP addresses, each is a unique row ID, nothing is grouped.

How would I query the database to use each IP as a query to count the appearences?

E.g. So I have my DB as it is with thousands of results and it would echo out:

IP Address - Appearences
127.0.0.1 - 10
32.333.33 - 4

[Old Pedant]

SELECT ipaddress, COUNT(*) AS appearances
FROM yourtable
GROUP BY ipaddress
ORDER BY COUNT(*) DESC

The ORDER BY is of course optional. The GROUP BY is not.

[Sussex_Chris]

And that would be echoed out with $sql['appearences'];?

I have changed it slightly to fit my DB:

$sql = mysql_query("SELECT ip, COUNT(*) AS appearances FROM traffic GROUP BY ip ORDER BY COUNT(*) DESC");

But I am not too sure how I would echo it out. Some areas of PHP I am good with but MySQL I still have a lot of problems with. OReilly's Guide is starting to get a bit confusing lol!

[seco]

this should do it for you.
$query = "SELECT ip, COUNT(*) AS appearances FROM traffic GROUP BY ip ORDER BY COUNT(*) DESC";

$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
echo $row['id']. " - ". $row['appearances']. "<br />";
}

[Sussex_Chris]

That worked perfectly, except I changed $row['id'] to $row['ip'] :)

Thanks a lot for your help :)

[Coyote6]

OReilly's Guide is starting to get a bit confusing lol!

I believe O'Reilly books are great for intermediate programmers. If you are just beginning or having trouble understanding O'Reilly's I would suggest Visual Quickstart Guides. I think they are better for beginners. Just a thought.

[Sussex_Chris]

I believe O'Reilly books are great for intermediate programmers. If you are just beginning or having trouble understanding O'Reilly's I would suggest Visual Quickstart Guides. I think they are better for beginners. Just a thought.
Thanks for your input. I have read quite a bit into the PHP/Java/MySQL guide so far as I already had a little PHP knowledge and can code up simple scripts but it is just small processes like this thread that I have trouble with.

When I code things up I tend to lookup a lot of it on Google, turn it into a function and then forget how it works until I need to use it again which is what I want to get out of. Instead of doing it this way around I decided to go through the full guide but as you say, parts of the guide have rather large jumps / gaps missing and the explanation seems to get a bit confusing as the more technical terms are being used more and more often without having enough time to take in the simpler ones!!!

[Coyote6]

Nothing wrong with googling though. There are lots of great codes out there that can help you develop too. I normally do that when I get stuck or want to do research on accomplishing an new task that I've never done before, but it is always nice to have a good base to build off of.

[seco]

http://www.tizag.com helped me when i was starting a while ago, its mostly intermeadiate stuff but he explains everything very well.

[Old Pedant]

I would say, though, that googling can also turn up some of the purest and absolute crap answers possible. Generally, I'd try to find at least two or more authorities agreeing on an answer. Or try to find sites/people with high reputations.

[Sussex_Chris]

This I managed to write from my own knowledge within an hour of playing around, not too sure if that is good or not lol, for me it is quite complex though.

It grabs data from a cell of countries, E.g. us,uk,aus. It queries geoip.dat for the users country. It then searches all the rows in my offers table to see if the users country is applicable with the offer (Randomizes the rows) and if ok then gets the offer URL or goes to a fallback link:

<?php
include ("config.php");
// Dummy Data
$geolocation = "us";
$result=mysql_query("SELECT * FROM `offers` ORDER BY RAND()");
while($row=mysql_fetch_array($result)){
$arr_my_array = explode(',', $row['geolocation']);
$numofarray = count($arr_my_array);
$i=0;
while ($i<=$numofarray){
if ($geolocation==$arr_my_array[$i]){
$offer1 = $row['offerurl'];
break;
}else{
$i++;
}
}
}
// Fallback URL:
if ($offer1==""){$offer1="http://fallbacklink.com/subid/";}
$user="1171";
$offer = str_replace(subid,$user,$offer1);
echo $offer;
?>

So my knowledge of PHP/MySQL is not completely basic but still, OReillys guides seem to push you through steps before properly implementing the previous steps. I have decided to write up a site/script as I go with the knowledge that I get from the guide as I go along, this way I feel that I would learn best and I am sure that it is not the fastiest way to go about learning these languages but it is probably the best way.

[Old Pedant]

Not a PHP person.

But if I read that code correctly, you have a single field in the table ("geolocation") that contains a delimited list of values (e.g., "australia,uk,us,mexico"). Yes??

First of all that's very bad DB design. Should never put a delimited list into a single field. Period.

You should split that out into a separate many-to-one table.

But that's for later.

For now, let's get rid of all that PHP code and replace it with a query. Please remember I'm not a PHP user. If I typo on the PHP, please fix it.



<?php
include ("config.php");
// Dummy Data
$geolocation = "us";
$sql = "SELECT offerurl FROM offers "
. " WHERE CONCAT(',',geolocation,',') LIKE '%," . $geolocation . "%' "
. " ORDER BY RAND( ) LIMIT 1"
$result=mysql_query("SELECT * FROM `offers` ORDER BY RAND()");
// Fallback URL:
$offer1="http://fallbacklink.com/subid/";
if ( $row=mysql_fetch_array($result) )
{
$offer1 = $row['offerurl']; // replace the fallback
}
$user="1171";
$offer = str_replace("subid",$user,$offer1);
echo $offer;
?>

(Pretty sure your str_replace on next to last line was wrong...I added the quotes around "subid".)

HOW IT WORKS:

$sql = "SELECT offerurl FROM offers "
. " WHERE CONCAT(',',geolocation,',') LIKE '%," . $geolocation . "%' "
. " ORDER BY RAND( ) LIMIT 1"

The CONCAT converts your delimited list (example, "uk,us,italy") to have commas on front and back (example, ",uk,us,italy,").

We also add commas to the front and back of the country you are looking for.

So we end up doing (effectively)
WHERE ',uk,us,italy,' LIKE '%,us,%'

We needed to add the commas front and back so we didn't get any false matches (e.g., if we had a field such as 'austria,italy' and tried to do
WHERE 'austria,italy' LIKE '%us%'
we would get a false match on the "us" in the middle of "austria". The commas prevent this.

So this means that we will *ONLY* find records that indeed have "us" as one of the countries in the geolocation field.

Then we apply your "ORDER BY RAND()" to indeed get those matches in random order.

Finally, we can do "LIMIT 1" to get only one record. No point in sending more than that back to PHP, since PHP only wants a single valid record. (And if we don't get that one record, then there were no records with the given country and we use the fallback.)

Notice that instead of "SELECT *" we only SELECT the one and only field we need to get!

Okay? Still not as good as if you would NORMALIZE your database, but should be a big improvement.

[Sussex_Chris]

Damn, my code got ripped to shreds in seconds lol. I swear I could actually see the time difference in using the edited code.

So, the difference between mine and yours would be that I slow down the process a LOT by looping over and over until something occurs whereas you are searching the DB beforehand and finding xxx without looping any data?

I haven't seen CONCAT before, guess I either overlooked it, it's not in the guide I'm reading or I haven't got that far yet lol!

[Coyote6]

What is "subid" in your second to last line?

Also if you are storing multiple values for the location inside of one field then you should break it out and place it into its own table.

So you would have an offers table without the geolocation field.

Then have two tables like so.
geolocations
id | name

offer_geolocations
id | offer_id | geolocation_id

So you would get a one to many reference inside the offer_geolocations...


<?php
include ("config.php");
// Dummy Data
$geolocation = "us";
$result=mysql_query("SELECT * FROM `offers` ORDER BY RAND()");
while($row=mysql_fetch_array($result)){
// Query the database again to get all of the locations offered.
$result_2=mysql_query("SELECT g.`geolocation_name` FROM `geolocations` as g, `offer_geolocation` as og WHERE og.`offer_id`={$row['id']}");
// Place them into an array.
$row_2 = mysql_fetch_array($result_2);
// Use the in array function to check if it is in the array.
if (in_array ($geolocation, $row_2)) {
$offer1 = $row['offerurl'];
}else{
$i++;
}
}

// Fallback URL:
if ($offer1==""){$offer1="http://fallbacklink.com/subid/";}
$user="1171";
$offer = str_replace(subid,$user,$offer1);
echo $offer;
?>

[Sussex_Chris]

Subid is the user ID so E.g.
http://google.com/subid/

Would be replaced with:
http://google.com/1171/ (In the case that $user="1171").

It is mainly for tracking purposes, the user will never see the number.

My offers table will be containing a max of 15 rows, in which the geolocation will have no more than 3 values in each and the others will be "worldwide" which will also be the fallback URL.

Would a table this small still be better off splitting into more tables?

[Coyote6]

Yes for good database design. Someday on down the road you may wish to expand this and if so then you would not have to change your code if you do it correctly the first time.

Oh I see now, I missed that in your code. Since subid is a string then it needs to be put into quotes otherwise PHP will look for a constant named subid and not the string and you will get an error if error reporting is turned on.


$offer = str_replace('subid',$user,$offer1);

[Old Pedant]

Can't agree with Coyote's answer. Sorry.

Yes you SHOULD create the separate table, as he said. Standard normalization.

But then your code would look like this:

<?php
include ("config.php");

$geolocation = "us";
$sql = "SELECT O.offerurl FROM offers AS O, geolocations AS G "
. " WHERE O.offer_id = O.offer_id "
. " ORDER BY RAND() "
. " LIMIT 1"
$result=mysql_query( $sql );

$offer1="http://fallbacklink.com/subid/";
if ($row=mysql_fetch_array($result))
{
$offer1 = $row['offerurl'];
}
$user="1171";
$offer = str_replace(subid,$user,$offer1);
echo $offer;
?>

Again, you SELECT only the one field you need. And you should never fetch a set of records inside a loop on another set of records if there is ANY way to avoid it. So JOIN the two tables, as shown, and get just one record. Logic is same as my other answer, only the table usage has now been corrected.

[Coyote6]

Oh no worries pedant... Your query was much cleaner than mine... I was typing mine when you first replied.

I don't understand part of your query though:

$sql = "SELECT O.offerurl FROM offers AS O, geolocations AS G "
. " WHERE O.offer_id = O.offer_id "
. " ORDER BY RAND() "
. " LIMIT 1"


Why the O.offer_id=O.offer_id? And where are you finding out if it is in the US?

[Old Pedant]

LOL! See *I* was typing too fast!!!

DOH and DOUBLE DOH on me!


$sql = "SELECT O.offerurl FROM offers AS O, geolocations AS G "
. " WHERE O.offer_id = G.offer_id "
. " AND G.geolocation_name = '" . $geolocation . "' "
. " ORDER BY RAND() "
. " LIMIT 1"

It must be getting late out.

[Coyote6]

:thumbsup: Haha Yep it's a Monday alright...

[Sussex_Chris]

Great responses guys, thanks a lot for your help :)