CODEWITHSUNDEEP
phpmysqlarrays
Arie
Arie

Reputation: 371

How to check if a value is greater than within an array

I have the following SQL statement:

$query  = "SELECT item, COUNT(*) as number FROM shop GROUP BY item";

This will give me the following result:

item    number
item1       23
item2       15
item3        4

I want to use this to make menu items, so normally the menu would look:

item1
item2
item3

But I want to do a check if an item has less than 10 records, that I don't want to display this item.

So in this example, the menu would be like:

item1
item2

Any idea how to achieve this?

I would like to do this in PHP because I need all the items in the query but will only want to show them which are greater then 10 and need the other items later on.

Upvotes: 1

Views: 8252

Answers (5)

Sagi
Sagi

Reputation: 127

There is two options to filter the data so only the rows with more then 10 will appear.

  1. At the SQL query

__

SELECT item, COUNT(*) as number FROM shop GROUP BY item HAVING number > 9

This will cause you to recieve only the requested rows from the database

  1. Filter with PHP - every time you want to print the menu or testing it out, just can the value of 'number' in the array reutrned from the query. You can also allocate new array and insert all the values that contains 'number' that bigger then 10.

Upvotes: 0

AymDev
AymDev

Reputation: 7554

As you really need to perform this in PHP you could use array_filter() which, using a closure, will remove items which number is less than 10:

$more_than_ten = array_filter($items, function ($i) { return $i['number'] >= 10; });

Doing it with SQL would be a better solution (about performances). In case you'd need it, you could use the HAVING clause (you can't perform a WHERE number >= 10):

SELECT
    item,
    COUNT(*) as number
FROM shop
GROUP BY item
HAVING number >= 10

Upvotes: 1

amku91
amku91

Reputation: 1038

If you want to do this in PHP then you can do like this

function filterArray($value){
    return ($value.number > 10);
}

$filteredArray = array_filter($yourDBArray, 'filterArray');

foreach($filteredArray as $k => $v){
    //your desired array
}

In terms of speed Mysql option is good as suggested above.

Upvotes: 3

bls1999
bls1999

Reputation: 99

I noticed php is tagged. For the sake of options, here's how I'd go about separating the unneeded data in php if you were to get it from the database as-is:

foreach ($data as $item) {
    $num = (int) $item['number']; // force of habit
    if ($num >= 10) {
        // display it
    }
}

I'd probably separate the data at the database step, but this works if it's the route you want to take.

Upvotes: 0

pabrantes
pabrantes

Reputation: 2181

Just change your query from

SELECT item, COUNT(*) as number FROM shop GROUP BY item

to

SELECT item, COUNT(*) as number FROM shop GROUP BY item HAVING number>=10

Upvotes: 2

Related Questions