How to get data from mysql database based on array

Question

So I have table called paint with data like below

id            name               allow_on
------------------------------------------
1             Avian           Wall|Roof|Wood
2             Avitex          Wall|Roof|Iron
3             Nippon          Floor|Iron
4             Some            Plastic|Cloth|other

And how to get field based on allow_on column, like "I want paint which can be used on wall" , so far my code look like this

$paints = Paint::get();
foreach($paints as $paint)
{
     $exp = explode("|", $paint->allow_on);
     foreach($exp as $pnt)
     {
         if(in_array($request->paint_name,$pnt))
         {
            var_dump($pnt)
         }
     }
}

I'm using Laravel 5.4 but i can't make it working, any solution? Thanks in advance.

actually, I didn't know how to query data based on request what I explained above, (I want paint whic can be used on wall), so I need suggestion how to query that data :D — Rizal Fakhri
– Rizal Fakhri, Commented Mar 7, 2017 at 13:49
Your problem stems from bad database design. Putting data in a column as a delimited list. It makes querying that data far more complex. — RiggsFolly
– RiggsFolly, Commented Mar 7, 2017 at 13:51

Jomoos · Accepted Answer · 2017-03-07 13:59:19Z

1

$want    = 'Wall';
$allowed = Paint::where('allow_on', 'like', '%'.$want.'%')->get();

foreach ($allowed as $allowedPaint) {
    echo $allowedPaint->name;
}

The above will get you all paints which can be used on wall. If you need to retrieve only the paint names, you can use:

$allowed = Paint::where('allow_on', 'like', '%'.$want.'%')->pluck('name');
var_dump($allowed);

edited Mar 7, 2017 at 13:59

answered Mar 7, 2017 at 13:48

Jomoos

13.1k10 gold badges57 silver badges92 bronze badges

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

3 Comments

Rizal Fakhri Over a year ago

But i got an empty object :/

Jomoos Over a year ago

@RizalFakhri $allowed in the first case will be an eloquent collection and in the second case will be an array. Check the updated answer.

Rizal Fakhri Over a year ago

Sorry my mistake >_< Case Sensitive, Thx it's works :D

RiggsFolly · Accepted Answer · 2017-03-07 13:48:07Z

1

You are confusing the use of the needle and haystack when using in_array()

The haystack should be an array i.e. the $exp array in your example

$paints = Paint::get();
foreach($paints as $paint)
{
     $exp = explode("|", $paint->allow_on);

    if(in_array($request->paint_name,$exp)) {
            // yes paint_name is in the array
     }
}

answered Mar 7, 2017 at 13:48

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Collectives™ on Stack Overflow

How to get data from mysql database based on array

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related