SELECT DISTINCT Problem

[Weirfire]

I have created my query

$resulta = mysql_query("SELECT DISTINCT(*)) FROM Refferrer");

and want to extract each row from the table by using

while ($get_info = mysql_fetch_row($resulta)){

But I get this error

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/weirfir/public_html/Statistics.php on line 88



Any ideas on how to fetch the data?

[mnemtsas]

Try putting a column name into the query rather than the wildcard.

[Weirfire]

Got it working

I'm such a novice. I had DISTINCT(Referrer) instead of DISTINCT Referrer. Mixing my COUNT with my DISTINCTs.

:o

[mnemtsas]

No, the proper SQL syntax is the first one. It will return one column of all the unique values in the column referrer in the table referrer.

[draculus]

$resulta = mysql_query("SELECT DISTINCT(*)) FROM Refferrer");

This line has more close brackets than open brackets. You also need to specify what fields you want to be distinct. You're probably getting an empty result set back at present.

$resulta = mysql_query("SELECT DISTINCT <field_name, [more_fields]> FROM Refferrer");

Would be better.

[Weirfire]

Thanks Draculas. MY next problem is how to get the count of these Referrals and display them in descending order. Sometimes I think I just like causing myself problems.

[mnemtsas]

Try

SELECT DISTINCT(referrer) as referrer,COUNT(referrer) as referrerCount from referrer GROUP BY referrer ORDER BY COUNT(referrer) DESC;

This should give you a two column set of records with the referrer in the first column and the number of referrals in the second.

Mark

[Weirfire]

Should it not be something like COUNT(DISTINCT Referrer) ?

I presume you cant do a mysql_fetch_row on a COUNT?

What I want is the Referrer name and then the number of times it has been used displayed beside it. Might have to do some sort of multiple query.

[mnemtsas]

The SQL I posted will give you what you want in two columns. Count(DISTINCT Referrer) will simply give you a count of the number of different referrers. Do you want the results in one column?

[Weirfire]

I think I need the results in 2 columns. 1 displaying the referrer name and another displaying the count of each distinct referrer name. I believe it's my fetching of the values that is perhaps causing the problems.

Here is my exact code


$resulta = mysql_query("SELECT DISTINCT Referrer, COUNT(DISTINCT Referrer) from Refferrers GROUP BY Referrer");

while ($get_info = mysql_fetch_row($resulta)){ //WHILE A
$i=0;
foreach ($get_info as $field)
{

if($i==0)
{
print "<tr><td><a href='$field'>".$field."</a> </td><td>";
}
if($i==1)
{
print $field."</td></tr>";
}

$i++;
}
}

[mnemtsas]

TO me the fetching looks OK, try doing some debug code using:

<?php
$resulta = mysql_query("SELECT DISTINCT(refferrers) as referrer,COUNT(refferrer) as referrerCount from refferrers GROUP BY referrer ORDER BY COUNT(refferrer) DESC;");
if (!$resulta) {
echo 'Could not run query: ' . mysql_error();
exit;
}
$row = mysql_fetch_row($resulta);

echo $row[0]; // the referrer name
echo $row[1]; // the referrer count (note the array offset)
?>

[Weirfire]

Well for one my table isnt called Refferrers, it's called Refferrer. Let me try that and see if it makes any difference.

Ok it's displaying a list of results but the Count for each referrer is 1.

www.weirfire.co.uk/Statistics.php

grrr

[mnemtsas]

lol ooops my bad I thought referrer had one f :(

[Weirfire]

Yeah that was my fault in the first place though. :o

Sloppy stuff from Weirfire. I really should stop hacking code together.

Any ideas how I can get the right count for each referral?

[mnemtsas]

Can you post the exact code here pls.

[Weirfire]

$resulta = mysql_query("SELECT DISTINCT Referrer, COUNT(DISTINCT Referrer) AS count from Refferrer GROUP BY Referrer ORDER BY count DESC");

while ($get_info = mysql_fetch_row($resulta)){ //WHILE A
$i=0;
foreach ($get_info as $field)
{

if($i==0)
{
print "<tr><td><a href='$field'>".$field."</a></td>";
}
if($i==1)
{
print "<td>".$field."</td></tr>";
}

$i++;
}
}


I think I want to do something like SELECT DISTINCT Referrer.R1, COUNT(R1) but I'm not sure. It's a lot easier plugging away at SQL with Oracle.

[mnemtsas]

Get rid of the DISTINCT in the COUNT brackets

[melfan]

I suggest use only GROUP BY statement and omit DISTINCT (You can only use one column when using DISTINCT statement)

SELEC Referrer, Count(*) as Count from Refferrer
GROUP BY Refferrer

[Weirfire]

Thanks melfan and mnemtsas for your help. Got it working perfectly.

I used melfans query without any distincts. I take it that the GROUP BY Referrer takes a unique referrer?

[melfan]

I take it that the GROUP BY Referrer takes a unique referrer?

Yes, it should be.