CODEWITHSUNDEEP
phpmysqlsql
Javacadabra
Javacadabra

Reputation: 5758

Query a column containing Comma Separated String

I am having an issue with an sql query. Basically I have this method:

public function loadByDirectoryContact($directoryContact,$type='') 
    {
        global $db;
        $t = 'directoryprogramme';
        $query  = 'SELECT c.* FROM `'.$t.'` c ';
        $query .= 'WHERE `c`.`presenters` = '.$directoryContact->getId().' ';
        $query .= 'OR `c`.`staff` = '.$directoryContact->getId().' ';
        if($type!=""){
            $query .=" AND c.type='".$type."' ";
        }


        $query .= QueryBuilder::orderBy('name','asc');

        echo $query; die();

        $db->query($query,$t);

        while($db->next($t))
        {
            $node = new Programme();
            $node->setId($db->get('id',$t));
            $node->setName($db->get('name',$t));
            $node->setBroadcastTime($db->get('broadcast_time',$t));
            $node->setDescription($db->get('description',$t));
            $node->setDays($db->get('days',$t));
            $node->setSubjects($db->get('subjects', $t));
            $node->setStaff($db->get('staff',$t));
            $node->setPresenters($db->get('presenters',$t));
            $directoryCompany = new DirectoryCompany($db->get('directorycompany_id',$t));
            $node->setDirectoryCompany($directoryCompany);
            $node->setContributors($directoryContact);
            $node->setType($db->get('type',$t));
            $this->nodes->add($node);
        }
    }

It creates a query like this:

SELECT c.* 
FROM `directoryprogramme` c 
WHERE `c`.`presenters` = 1234
OR `c`.`staff` = 1234
ORDER BY `name` ASC

The problem is that the presenters column stores comma seperated values so in reality it looks like this

presenters
1234,7738,5097,5100

and so for any query where the c.presenters equals any value that is not first in this column it returns no results. I am wondering if there is a way I can query so that it checks the entire String?

Upvotes: 0

Views: 150

Answers (2)

Abubakar Siddiq Ango
Abubakar Siddiq Ango

Reputation: 138

You can also try:

SELECT c.* FROM `directoryprogramme` c WHERE `c`.`presenters` LIKE '%1234%' OR `c`.`staff` = 1234 ORDER BY `name` ASC

I believe the presenters column is in varchar.

Upvotes: 0

VMai
VMai

Reputation: 10336

You can use the function FIND_IN_SET to find your value in the list:

SELECT c.* 
FROM `directoryprogramme` c 
WHERE FIND_IN_SET('1234',`c`.`presenters`) > 0
OR `c`.`staff` = 1234
ORDER BY `name` ASC

Remark

Consider normalizing your table design, if you can. This means a separate table for the presenters ids with one row per id.

Upvotes: 2

Related Questions