Need Help Please

[factoring2117]

Hi,

 

I have a table in my database where I store the referral keywords from where my visitors came from. I am trying to fetch those results, count the number of time a user came from a keyword and enter these results into a table.

 

Here is my code:

 

$select = mysql_query("SELECT * FROM visitors");
        while($row = mysql_fetch_array($select)) {
            
            $keyword = strtolower($row['referral']);
           
           $sel = mysql_query("SELECT * FROM visitors WHERE referral = '".$keyword."'");
           $number = mysql_num_rows($sel);
           
           $array[$keyword] =  $number;
       
// also tried 
// $array[] = array($keyword => $number);

print_r($array);

 

I need remove the duplicate keywords and sort them by most occurrences, but the issue is that the function array_unique() and asort() are not working correctly.

 

When I use array_unique() I get only 6 keywords when there are over 300 unique keyword combinations and the asort function is just not working.

 

Is there something that I am doing wrong or does someone have a better solution?

 

Thanks

 

Andrew

[ameyemad]

try...

 

$na[$keyword] = array();

 

and change $array[$keyword] =  $number;

 

to array_push($na[$keyword],$number);

 

then loop through the arrays...

foreach($na AS $key => $value){

//coding

}

[factoring2117]

Thank you. That seems to work like a charm. Here are some of the results.

 

How would I sort these to get the keyword listed most at the top?

 

Array

(

    [annuity] => Array

        (

            [0] => 2

        )

 

    [annuities] => Array

        (

            [0] => 7

        )

 

    [settlement] => Array

        (

            [0] => 12

        )

 

   

)

[ameyemad]

looking again, you prob don't need to do all that lol

 

$select = mysql_query("select count(*) as count,referral from visitors group by referral order by count");

while($row = mysql_fetch_array($select)) {

//coding eg $row[referral] has count $row[count]

}

 

maybe that's a better alternative?

[factoring2117]

Thanks for your help so far. The issue that I am running into now is that I need to count multiple rows and display them.

 

Here is what my table looks like.

 

id   referral        conversion
1   annuity  	0
2   annuity         1
3   annuities        1
4   annuities        1
5   annuity          0
6   settlement      0
7   settlement      0

 

I want my table to look like this:

 

keyword     Number of Prospects        Conversion %
annuity       3                                  33%
annuities     2                                   100%
settlement   2                                  0%

 

Here is the code that I have so far:

 

$table = '<table>';
$table .= '<tr><th class="sortable">Keyword</th><th class="sortable">Number of Prospects</th><th>Conversion</th></tr>';


$select = mysql_query("select count(*) as count,referral from visitors group by referral order by count") or die(mysql_error());


while($row = mysql_fetch_array($select)) {
    
   
    
$table .= '<tr><td>'.substr($row['referral'],0,35).'</td><td>'.$row['count'].'</td><td>'.$row['short'].'</td></tr>';

}
$table .= '</table>';

 

Does anyone have any suggestions?

[sasa]

try

select count(*) as count,referral, AVG(conversion) as conversions from visitors group by referral order by count