CODEWITHSUNDEEP
phpmysqlsqlarraysdatabase
Andreas Uldall Leonhard
Andreas Uldall Leonhard

Reputation: 315

Sorting an array in PHP vs fetching data from SQL

I am using the following php method to find the top 10 occuring elements in an array and then returning a new array with those 10 elements along with how many times they appear. This is fine for rather small arrays. But I get my data from a database and the arrays can easily be over 100000 in size. This causes this method to be extremely slow. The code is as follows:

function getTopTenSelects($array) {
    $result = [];
    for ($x = 0; $x <= 10; $x++) {
        $count = countArray($array);
        $mostOccuring = (key($count));
        $arrayReturn[$x] = $mostOccuring;
        array_push($result, [$mostOccuring,$count[$mostOccuring]]);
        foreach ($array as $temp) {
            if (($key = array_search($mostOccuring, $array)) !== false) {
                // Cuts the key from the array foreach time it appears so we can find the next most occuring value
                unset($array[$key]);
            }
        }
    }
    return $result;
}

Now. I get my array through the following SQL query. $hashtag is a variable that contains a piece of string. 

SELECT tag_names
FROM soc_stat
JOIN tags on soc_stat.insta_id = tags.insta_id
WHERE main_tag = $hashtag

Is there an efficient way of doing it in php, or is there a way to get what I need through an SQL query?

Upvotes: 0

Views: 84

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Of course you should retrieve only the data from the database that is really needed in order to have small data exchange. In your case the top 10 tag names:

SELECT tag_names, count(*)
FROM soc_stat
JOIN tags on soc_stat.insta_id = tags.insta_id
WHERE main_tag = $hashtag
group by tag_names
order by count(*) desc limit 10;

Upvotes: 4

Related Questions