How do I select a cell with multiple values in one column?

Question

I have an MYSQL table with a large product list. In this product list table there are categories, a name, description, text, etc. I want to add a modifier for the industries that it can be used in (e.g. hospitals, schools, events, sport events, etc.)

I'm running this query through PHP/MYSQL right now:

public function GetIndustrySeries($identifier, $SeriesIDArray = null)
{            
    $query = "select ser.Identifier,
                        ser.ModelNumber,
                            ser.Title,
                            ser.Caption, 
                            ser.Description, 
                            ser.Picture, 
                            ser.Industry, 
                            cat.TitleText,
                            ser.AutoID, 
                            ser.BasePrice 
                            from ProductSeries ser 
                            inner join 
                            ProductIndustry cat 
                            on 
                            cat.Industry 
                            = 
                            ser.Industry 
                            where
                            ser.Industry
                            like 
                            ?";
    if($SeriesIDArray != null && count($SeriesIDArray) > 0)
        $query .= " and ";
    $i = count($SeriesIDArray);
    $parameters = array();
    $parameters[0] = "s";
    $parameters[1] = $identifier;
    if($SeriesIDArray != null){
        foreach($SeriesIDArray as $id)
        {
            $parameters[0] .= "i";
            array_push($parameters, $id);
            $query .= " ser.AutoID = ?";
            if($i > 1)
                $query .= " or ";
            $i--;
        }
    }

    $stmt = $this->_mysqli->prepare($query);
    //$stmt->bind_param('ss', $identifier);                
        call_user_func_array(array($stmt,'bind_param'), $parameters);
    $stmt->execute();
    $stmt->bind_result($ident2, $model, $title, $caption, $description, $picture, $ident, $catText, $sid, $price);
        $stmt->store_result();        
    if($stmt->num_rows < 1)
    {
        $stmt->close();
        return null;
    }
    $array = array();
    while($stmt->fetch())
    {
        array_push($array, array('seriesLink' => "/products/$ident2/$model", 'seriesTitle' => $title, 'seriesImage' => $picture, 'seriesCaption' => $caption, 'seriesDescription' => $description, "seriesCategoryName" => $catText, "seriesID" => $sid, "basePrice" => $price));
    }
    $stmt->close();        
    return $array;        
}     

I've tried using the % modifier in that code on both sides of the ? but I'm getting an error down the line:

Warning: call_user_func_array() [function.call-user-func-array]: First argument is expected to be a valid callback, 'Array' was given in C:\wamp\www\database.php on line 70.

In the table I have a column for "Industry" and what I want to do is put the industries that the product qualifies for in there hoping it can accept multiple values: "school,hotel,hospital"

Answer 1

where's the call_user_func_array() and how are you using it?

Answer 2

You are using the wrong method to get the data. call_user_func calls a method (see http://au.php.net/call_user_func). You want to query the data using mysql functions (http://au.php.net/manual/en/book.mysql.php), PDO (http://au.php.net/manual/en/book.pdo.php) or a data abstraction layer such as zend_db, doctrine, etc.

Answer 3

Would seem to me to be a lot easier just to loop through the array of industries wanted and rather than doing a 'LIKE' doing

$i=0

$query......

....WHERE ( ser.Industry = '".$industry(i)."'";

i++;
while $industry(i) {
  $query.=" OR ser.Industry = '".$industry(i)."'";
  i++;
}
 $query.=")";

Re-reading your question however it seems you need an intermediate table with a two part key, column 1 would be the industry and column two the product, you would then have the key for the product and the key for the industry and all you would need to do is select the rows from your category table where there was a record in the new table with the indusrty you were seeking, or all the rows from the new table which had the category from the first table to see all industries the category fitted, or all rows from the new table for an industry to see all categories that matched.

It seems you could do with more tables since category looks like one table, industry another and product yet another another. Remember the first rule, the data should be dependent on the key, the whole key and nothing but the key.

Hope this helps. SteveK

Answer 4

It was a combination of my tables not having the same values under the Industry as well as the $identifier part in $paramters[1]; Also some I had to modify some parts of the query string.

Thanks for your help