Welcome Guest, Not a member yet? Register   Sign In
confusion with where query and arrays
#1

[eluser]martynrlee[/eluser]
Hi All,

Just struggling with a database query and hoping someone might be able to help:

I am trying to do the following :

Code:
$this->db->where_in('style', $styleArray);

and

$this->db->where_in('paint', $paintArray);

$this->db->get('gallery');


I have tried lots of methods but can only seem to get or_where_in working I guess I need an equivalent method to 'and_where_in'

Any ideas?

Thanks, Martyn.
#2

[eluser]mddd[/eluser]
If you do multiple where() or where_in() statements they are always 'and'.
Code:
$this->where('id', 1);
$this->where('active', 1);
// this gives:
WHERE `id`=1 AND `active`=1
Same thing for where_in.
#3

[eluser]bretticus[/eluser]
Either turn on profiling or print out the result of $this->db->last_query() to see what query is actually being built and sent to the database service.

CodeIgniter Profiling, etc.
#4

[eluser]martynrlee[/eluser]
Thanks mddd,

That works, think I am just over complicating things as I am stuck again - trying to do the following:

I have an array of colours received through ajax. So, (blue, green, red) for example.

I have three columns in a database. colour1, colour 2, colour 3. I am trying to do

$this->db->where_in('colour1', $colourArray);
$this->db->or_where_in('colour2', $colourArray);
$this->db->or_where_in('colour3', $colourArray);

I also need to do the following (if anything contained in the arrays):

$this->db->where_in('style', $styleArray);

and

$this->db->where_in('paint', $paintArray);

However as soon as I use 'or_where_in' this will reference against the 'style' and 'paint'.

The idea is that the user can drill down through a menu in real time selecting either by colour, style or paint. I guess my main issue is that there can be up to 3 colours and only 1 style and 1 paint type.

I have also tried putting all 3 colours into a comma separated field but then couldnt work out how to do:

$this->db->where_in($colourColumnArray, $colourArray);

What do you think?

All help with this most appreciated.

Martyn.
#5

[eluser]mddd[/eluser]
In that kind of case, I would use a link table to store the relationships between the items and the colours. That solves the problem. It gives you more flexibility in searching and storing (e.g. you can store any number of colors for an item).
#6

[eluser]martynrlee[/eluser]
Appreciate your help mddd - can you give me any more info with the link tables.

I cant quite work out how it would be different even if I had a separate table for colours and each row in the main table linked to the other table.

Would it not be like this..

Colour table:
id - 1, colour - blue
id - 2, colour - green
etc

Main table:

id -1, colours - 1,2.


Any more advice?
#7

[eluser]mddd[/eluser]
You almost got it. The idea is to have one line for each 'link'.
So you would have the 'colours' table like you show. And you would have the 'items' table containing all the other info about each item (its style, description, whatever). And you have the link table:
Code:
table 'items_colours'

id        item_id   color_id
1         123       1
2         123       2
3         456       1
In this example, item 123 is blue and green, item 456 is only blue.

If you wanted to find all the items that are blue, you would search for all item_ids from the link table where color_id=1.
And you'd probably do that in a join query which joins the items_colors to the items table so you'd get all the item information at the same time as finding those item_ids.
#8

[eluser]martynrlee[/eluser]
Oh I see, that makes sense.

Thanks very much for your help. Will let you know how I get on.

Martyn.




Theme © iAndrew 2016 - Forum software by © MyBB