mysql和codeigniter中逗号分隔的varchar的条件 [英] where condition from a comma seperated varchar in mysql and codeigniter

问题描述

我有两个表，分别称为musical_types和musical_albums，其中musical_types由type_name和一个自动增量ID组成，其中如musical_albums表中包含auto增量ID，name，types一样，这是一个varchar，用于存储music_types的id（以逗号分隔）值，例如1,2,3,4等。
所以有人可以帮助我从音乐专辑表中获取所有类型为x的专辑。

我尝试过像mysql一样，但它是不正确的。
，所以请帮助我编写此查询。

iam使用codeigniter，是否可以在codeigniters活动记录类中编写上述查询。 p>

另一点是，这是一个joomla组件表设计，并且iam为android网络服务编写了一个脚本，该脚本返回音乐事件，歌曲等。

谢谢。

解决方案

尝试一下，但是已经提到过的@juergen不会将值存储为逗号分隔会导致痛苦，您现在可以按照以下步骤进行操作

  $ this-> db-> select（' *'）; 
 $ this-> db-&from;（musical_albums'）; 
 // $ this-> db-> join（第二个表名，第一个表的关系ID =第二个表的关系ID）； 
 //自定义字符串：其中
 $ where = FIND_IN_SET（’x’，`types`）; 
 $ this-> db-> where（$ where）; 
 $ query = $ this-> db-> get（）; 
  

活动记录

FIND_IN_SET（）

i have two tables called musical_types and musical_albums in which musical_types consists of type_name and an auto increment id, where as in the musical_albums table contains auto increment id , name , types which is a varchar which stores the id of musical_types as comma seperated values like 1,2,3,4 etc. So could anyone please help me in getting all the albums of type x from musical_albums table.

I tried mysql like but it is not correct. so please help me in writing this query..

iam using codeigniter, is it possible to write the above mentioned query in codeigniters active record class.

another point is this is a joomla component table design, and iam writing a script for an android webservice that returns the musical events, songs etc..

Thanks in advance.

解决方案

Try this one but @juergen already mentioned do not store values as comma separated it results in a pain for now you can do as below

$this->db->select('*');
$this->db->from('musical_albums');
 //$this->db->join('second table name', 'relation id of 1st table = relation id of 2nd table');
 //Custom string: in where 
$where = " FIND_IN_SET('x',`types`)";
$this->db->where($where);
$query = $this->db->get();

Active Record

FIND_IN_SET()

这篇关于mysql和codeigniter中逗号分隔的varchar的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！