Using a PHP array in a Zend Framework MySQL query

Question

I am trying to build a tag system and I would like to query my table of tags before uploading the new tags to weed out duplicate tags. So I package the tags in an exploded array, and when I go to query the table with the array I get numerous error messages from Zend about how it can't convert the array to a string.

This is what I have so far.

$tags = explode(', ', $_POST['itemTag']);

foreach($tags as $tag) {
    $tag_sql[] = '\''.$tag.'\'';
}
$tag_where = implode(',',$tag_sql);

$i = 0;
while ($i < count($tags)) {


    $sql = $dbRead->quoteInto("SELECT tagID FROM item_tag WHERE tag IN ($tag_where)");
$tag_result = $dbRead->fetchAll($sql);

    if ($tag_result) {
        $tag_ID = $tag_result;
    }
    else {
    $data = array ('tag' => $tags[$i]);
    $dbWrite->insert('item_tag', $data);
    $tag_ID = $dbWrite->lastInsertId();  }

$data = array('itemID' => $item_ID,
              'tagID'   => $tag_ID);

    $dbWrite->insert('item_tag_connection', $data);
++$i;
    }
}

I also need it to insert the ID number of the tag if one is found during the query into the connecting table and I am unsure if the code I have is functional as I can't get past the array query problem.

Thank you.

Answer 1

Let Zend quote the array for you like that, using quoteInto():

$dbRead->quoteInto('SELECT tagID FROM item_tag WHERE tag IN (?)', $tags)

Answer 2

try this :

$tags = explode(', ', $_POST['itemTag']);
foreach($tags as $tag)
{
    $sql = $dbRead->quoteInto("SELECT tagID FROM item_tag WHERE tag='$tag'");
    $tag_result = $dbRead->fetch($sql);

    if ($tag_result) { // I'm not sure if you should use this or count($tag_result) > 0
        $tag_ID = $tag_result['tagID'];
    } else {
        $data = array ('tag' => $tag);
        $dbWrite->insert('item_tag', $data);
        $tag_ID = $dbWrite->lastInsertId();  
    }

    $data = array('itemID' => $item_ID,
              'tagID'   => $tag_ID);

    $dbWrite->insert('item_tag_connection', $data);
}