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

Question

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.

Number's is an alias for count so he actually has to do HAVING NUMBER>=10 — pabrantes
– pabrantes, Commented Aug 24, 2018 at 9:14

amku91 · Accepted Answer · 2018-08-24 09:23:08Z

3

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.

answered Aug 24, 2018 at 9:23

amku91

1,0481 gold badge11 silver badges22 bronze badges

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

Comments

pabrantes · Accepted Answer · 2018-08-24 09:15:39Z

2

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

answered Aug 24, 2018 at 9:15

pabrantes

2,1811 gold badge30 silver badges37 bronze badges

Comments

AymDev · Accepted Answer · 2018-08-24 09:27:50Z

1

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

edited Aug 24, 2018 at 9:27

answered Aug 24, 2018 at 9:22

AymDev

7,7234 gold badges34 silver badges59 bronze badges

Comments

bls1999 · Accepted Answer · 2018-08-24 09:22:29Z

0

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.

answered Aug 24, 2018 at 9:22

bls1999

995 bronze badges

Comments

amku91 · Accepted Answer · 2018-08-24 10:27:06Z

0

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

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

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.

edited Aug 24, 2018 at 10:27

amku91

1,0481 gold badge11 silver badges22 bronze badges

answered Aug 24, 2018 at 9:18

Sagi

1272 silver badges9 bronze badges

1 Comment

Arie Over a year ago

Indeed, I am looking for option 2 but I don't know how to do that

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related