Match items in MySQL imploded array to PHP array in query

Question

I'm a database noob, how do I search for rows by matching items in a PHP array with items in a MySQL imploded array? It's stored like that out of convenience, but if there's a better way to do what I want, I'm all ears. I'm looking for something like this (I use ** to indicate where I'm handwaving):

$sql = "SELECT * FROM codeunits";
$sql .= " WHERE CASE WHEN private=1 THEN creatorID=:userID ELSE true END";
if (isset($tags))
{
  $tag_ids = join(',',$tags);
  $sql .= " AND ****items in mysql array 'tags'  IN :tags";
  if($stmt = $this->_db->prepare($sql)) {
    $stmt->bindParam(":userID", $userID, PDO::PARAM_STR);
    $stmt->bindParam(":tags", $tag_ids, PDO::PARAM_STR);
    $stmt->execute();
    $stmt->closeCursor();
  }
}

Here is how I am storing off the items into the DB. $values is bound to the query for the tags column, thus why I'm calling it a MySQL array.

    $tags = array();
    foreach ($_POST['tags'] as $selectedOption)
    {
        $tags[] = $selectedOption;
    }
    $values  = implode(",",array_values($tags));
    $sql = "INSERT INTO "
      ."codeunits(tags)"
      ." VALUES(:tags)";
      if($stmt = $this->_db->prepare($sql)) {             
          $stmt->bindParam(":tags", $values, PDO::PARAM_STR);
          $stmt->execute();

          $stmt->closeCursor();

          return true;
        }

This results in an entry in my db that looks like 'tag1,tag2,tag3'. Very simple string.

true, I clarified what I mean. I'm using a PHP array which I implode into a single string, which I then store. It is this string I'm wanting to compare against an array. — jakev, Mar 20 '13 at 22:58
You can't implode a PHP array to a string and use that in the query as separate values. You need to add a placeholder to MySQL for each one and bind them all separately. — Mike, Mar 20 '13 at 23:04
Are you really telling me that if i have a query like "SELECT .. FROM ... WHERE a='1'" i have to do it like that: "SELECT .. FROM ... WHERE a = :one" and then bind the :one to 1? — ITroubs, Mar 20 '13 at 23:08
@jakev see this question: It doesn't work that way. See http://stackoverflow.com/questions/6071619/pdo-bind-unknown-number-of-parameters — Mike, Mar 20 '13 at 23:09
@Mike Thanks, but that doesn't really address what I want. And as an aside, I've successfully solved a similar problem to that of the OP in that link. — jakev, Mar 20 '13 at 23:12
@Mike to bind the 1 statically would be nonsense! of course i can do "SELECT .. FROM ... WHERE a='1'" binding parameters is only for stuff that users may give you. and to prevent them from injectiong sql you bind your dynamic parameters. but you don't have to bind them. of course it would be better but mysqli and pdo can't force you to do that — ITroubs, Mar 20 '13 at 23:12
@jakev if i understand it right you have a column named tags where you have a string of arbitrary length where your tags are separated by some kind of separator right? — ITroubs, Mar 20 '13 at 23:13
so basically you want to look if some tags you get, match a part of the 'tags' col like so `table.tags LIKE '%tag1%' OR table.tags LIKE '%tag2%' OR and so on` — ITroubs, Mar 20 '13 at 23:17
OK, I misunderstood you. I thought you were taking a PHP array and trying to implode that into a string and then bind that like it was multiple MySQL placeholders. However with that said, your database design is flawed. You shouldn't store multiple values in one column. Instead use a `tags` table and a `codeunits_tags` relational table. — Mike, Mar 20 '13 at 23:19
@Mike you didn't misunderstand, that is what I was trying to do. It does seem flawed, when I think about it. I'd rather do it properly, and actually a relational table is probably what I want. Now to go find out what that is... — jakev, Mar 20 '13 at 23:55

score 0 · Accepted Answer · answered Mar 20 '13 at 23:20

0

this is not a good database design for this kind of query, you will have to use LIKE to find a tag.

instead you should have a table "tags" holding one tag per line, then you can use a join with the "IN" statement :

SELECT * FROM codeunits JOIN tags ON tags.codeunit_id=codeunits.ID WHERE tags.name IN (:tags) AND CASE WHEN private=1 THEN creatorID=:userID ELSE true END

answered Mar 20 '13 at 23:20

redmoon7777

4,498
1
24
26

Your "Should" won't solve the problem if he has a gigantic project that has to be maintained! – ITroubs Mar 20 '13 at 23:21
1

There's not time like the present to fix it ;) – Mike Mar 20 '13 at 23:22
@Mike that's for sure, plus in his question he said "it's stored like that out of convenience, but if there's a better way to do what I want, I'm all ears" – redmoon7777 Mar 20 '13 at 23:24
In fact, I do have a 'tags' table set up as you describe. I'm just unfamiliar with doing such operations. What you have looks promising, but I have no codeunit_id column in tags. Is it necessary to join ON something? I intended a one-to-many relationship from codeunits to tags. – jakev Mar 20 '13 at 23:38
no you should either have a one:many relationship from tags to codeunits or many:many relationship in which case you need a third table codeunits_tags but it depends on your needs. – redmoon7777 Mar 20 '13 at 23:41
@redmoon7777 I see. Reverse the relationship, and it works. Thanks! – jakev Mar 20 '13 at 23:46

Match items in MySQL imploded array to PHP array in query

1 Answers1