This is (probably) the story of my life.

[kaigou]

I know there's a way to do it, but I'm not sure of that exact way, and I'm pretty sure whatever I come up with is gonna be so convoluted as to be worthless in the end. GAH.

Okay, someone on my flist has got to know this one: if I have a field in a MySQL db that contains an array (say, carrots, tomatoes, squash), I've no problem getting it in and no problem getting it out. (Mostly.) What I don't know is whether the search function to find that array again -- or any like it -- would require precision or could be relatively sloppy.

I mean, if I search for all fields that contain "carrots, tomatoes, squash," would I get back every field that has any of the three, or would I get back all fields with all three only if they're in the same order, or would I get back any in any order plus fields that also contain "kumquat"? (I've always treated arrays like some kinda black hole of SQL, where you put info in and it's only good as long as you don't try to do anything else with it...)

It comes down to not being sure whether I can preempt an existing db's design to insert arrays that I know I can later search/reproduce -- or if I'll need to create a new table and slot these things in separately. Unfortunately, the create-a-new-table operation would be even more hassle, given I've got to deal with WP's functions to get in and out of it, and they're not always quite as intuitive as I'd like.

So. Anyone?

Comments

[onthehill]

AFAIK - you would get back every field that had any of the items. I can't work out if they come back in any specific order therefore I think not.

[kaigou.livejournal.com]

...I was figuring that would be the case. Bleah.

[davidcook.livejournal.com]

(disclaimer: I haven't worked with MySQL, mostly DB2 and Oracle - MySQL may do things differently to what I expect).

Do the arrays always occur in the same order, or could you have e.g. "carrots, tomatoes" in one record, and "tomatoes, carrots" in another ? I'm guessing they're being stored as literal strings like that, or has MySQL really added some sort of array type in SQL ?

Anyway, assuming that the order could be different, and there are no specific array operations in MySQL, your search query would have something like :
(table.array1 like '%carrots%') AND (table.array1 like '%tomatoes%') AND (table.array1 like '%squash%')
... which would return any records which include all three (but also any containing any other fruits).

Serious DB programmers would recommend that you split the array field out into a new table - your main table would just have a column named "arrayindex" or something, and then you insert records into a separate table (e.g. arraytab) accordingly. In theory, it should make the queries easier in general. On the other hand, I just tried to think up the query for the three-vegetable example above and failed, although I'm a bit out of practice now ...

[kaigou.livejournal.com]

MySQL is mostly like Oracle, at least logic-wise (even if its syntax differs somewhat). I'm trying to think of how I could force a specific order, like alphabetically, and I'm not sure I could do that without a whole 'nother heap of code between getting the input and putting it in the db -- and the more code, the more places for breakage.

Yeah...okay, I guess this means I figure out how to do a new table OR how to herd the input so the results will become a single string (like instead of pick A, pick B, pick C, listing option A, A+B, A+B+C, and it'd be a nuisance but at least consistent, then).

Of course, it goes without saying that if I can't get any of it to hook into Wordpress' existing functions, it'll all blow up in my face anyway. BLEAH. The things I do, I swear, why can't I ever go with the easy options?

[branchandroot]

The two things I can think of to use are 'like' and 'in_array', and I think both of those get you any array with those terms in any order. Unless you implode the array to a string and match on the whole string at once, which I would only recommend if you *know* the terms will always be in the same order.

If it's a matter of "get me items which contain any/all of these terms in any order, but not items that contain any *other* terms"... then I think you want an extra table. It's kind of a pain, but if you do it up as a plugin it's pretty simple and controllable. I did my original-series-credits that way.

[kaigou.livejournal.com]

I would only recommend if you *know* the terms will always be in the same order.

That's the kicker, I think: I can't come up with a reasonable simple way to force the string's elements to follow a pattern, not with human operators at the other end.

The goal here is to make searchable pairings, but knowing we're talking about pairings that run the entire gamut, it's not going to be as easy as just A+B and C+D and E+F ... so the potential combinations are really massive, massive, massive. Pre-coding that into a select box would make for a huge scrolling list to pick from, but doing it as "pick one from each column" would be a nightmare on the backend instead, it seems.

Oh, well. Guess this means I'm going to have to go with preset pairing-options, if I want to keep any consistency in things. Fffftt. WP is great in some ways, but in other ways it's just not set up for what I'm trying to do. It will do it, though, but it's really pushing the edges of my skill level. (Which isn't a bad thing, but still, I do have other stuff I need to do this week!)