Jump to content

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/177065-need-help-please/
Share on other sites

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

        )

 

   

)

Link to comment
https://forums.phpfreaks.com/topic/177065-need-help-please/#findComment-933601
Share on other sites

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?

Link to comment
https://forums.phpfreaks.com/topic/177065-need-help-please/#findComment-933602
Share on other sites

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?

Link to comment
https://forums.phpfreaks.com/topic/177065-need-help-please/#findComment-934645
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.