Extract SQL Query with JSON - Codeigniter

Question

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();

Show more of the code as what you show is not of much help to us — RiggsFolly
– RiggsFolly, Commented Feb 9, 2017 at 18:07
what is dataType for country column? and share $user_country value? — Naincy
– Naincy, Commented Feb 9, 2017 at 18:08
Datatype is LongText, and I just have a query to extract users country aka, It displays 'Canada' thats what '$user_country' holds. Now I need to find the row in the country_zones table which holds 'canada' and return it — D12L12
– D12L12, Commented Feb 9, 2017 at 18:11

Sangharsh · Accepted Answer · 2017-02-09 18:22:12Z

1

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

edited Feb 9, 2017 at 18:22

answered Feb 9, 2017 at 18:14

Sangharsh

3,0292 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

D12L12 Over a year ago

It works! Can you just explain to me why I need to use LIKE and what the '%\ is for??

D12L12 Over a year ago

Thank you. And now if I want to extract just the country row can I do $thecountry = $country->row()->country; ??

Sangharsh Over a year ago

What do you mean by "extract just the country row"? Also, PHP is not my forte, I can help with MySQL though.

Collectives™ on Stack Overflow

Extract SQL Query with JSON - Codeigniter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related