使用JSON提取SQL查询

使用JSON提取SQL查询 - Codeigniter(Extract SQL Query with JSON - Codeigniter)

我有一个名为country_zones的表，还有一个名为country的列，当前包含该列

[“加拿大”，“美国”]

我首先提取他们注册的用户所在的国家/地区，然后需要进行查询以查看哪些国家/地区与country_zones行匹配。 虽然每当我这样做，我要么得到错误或NULL

我试过这个。

$country = json_decode($this->db->get_where('country_zones',array('country'=>$user_country)));

也。

$country = $this->db->query("SELECT * FROM country_zones WHERE country='$user_country'")->result();

I have a table called country_zones and theres a column called country which currently holds

["canada","United States"]

I first, extract the users country they signed up with and then need to do a query to see which users country matches the row of country_zones. Although whenever I do that I either get error or NULL

Ive tried this.

$country = json_decode($this->db->get_where('country_zones',array('country'=>$user_country)));

also.

$country = $this->db->query("SELECT * FROM country_zones WHERE country='$user_country'")->result();

最满意答案

假设user_country是加拿大，那么你的查询应该是：

SELECT * FROM country_zones WHERE country LIKE '%"Canada"%';

所以，试试这个：

$country = $this->db->query("SELECT * FROM country_zones WHERE country LIKE '%\"$user_country\"%'")->result();

编辑：

因为列包含["canada","United States"]并且您想要与Canada匹配，所以需要LIKE查询。 所以上面的LIKE查询是SQL的说法给我国家包含“加拿大”的行。

请参阅： https ： //dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

Let's say user_country is Canada, then your query should be:

SELECT * FROM country_zones WHERE country LIKE '%"Canada"%';

So, try this:

$country = $this->db->query("SELECT * FROM country_zones WHERE country LIKE '%\"$user_country\"%'")->result();

Edit:

LIKE query is needed because column contains ["canada","United States"] and you want to match it with Canada. So above LIKE query is SQL way of saying give me rows where country contains "Canada".

See: https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html