Sorting an array in PHP vs fetching data from SQL

Question

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?

What table do tag_names and main_tag reside in?

Thorsten Kettner
– Thorsten Kettner

2015-09-23 11:42:24 +00:00
Commented Sep 23, 2015 at 11:42 — Thorsten Kettner
– Thorsten Kettner, Commented Sep 23, 2015 at 11:42

fpietka · Accepted Answer · 2015-09-23 12:29:14Z

4

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;

edited Sep 23, 2015 at 12:29

fpietka

1,0411 gold badge10 silver badges24 bronze badges

answered Sep 23, 2015 at 11:40

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andreas Uldall Leonhard Over a year ago

Oh god thank you so much. I'm starting to like SQL more and more. Unfortunately I find more and more code I've written that is completely useless. At least I am learning from my mistakes :)

Collectives™ on Stack Overflow

Sorting an array in PHP vs fetching data from SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related