1

I am trying to filter data from database by adding a variable into the query. The query that I have made is like this:

$data = $this->db->query('SELECT channel, MIN(product_name) as product_name, SUM(revenue) AS revenue FROM my_test_table WHERE channel = "chanel1" AND province=$area GROUP BY SUBSTRING(product_name, 1, 3)')->result();

But it result in this message:

Error Number: 1054

Unknown column '$area' in 'where clause'

I use the "$area" variable in the query to make it dynamically filter the data based on the input from the user. So the $area is a variable that assign any value from the input.

i have no name
  • 43
  • 1
  • 7
  • If this variable contains user supplied data, this would be prone to an SQL injection. Please properly [escape the string](https://www.php.net/manual/en/mysqli.real-escape-string.php). – Xxmarijnw Oct 19 '19 at 15:36

2 Answers2

2

the codeigniter way for your example is:

$data = $this->db->select('channel, MIN(product_name) as product_name, SUM(revenue) AS revenue')
                 ->where('channel','chanel1')
                 ->where('province',$area)
                 ->group_by('SUBSTRING(product_name, 1, 3)')
                 ->get('my_test_table')
                 ->result();

Codeigniter Query Builder Class, it creates a query string, which escapes the columns properly

Vickel
  • 7,879
  • 6
  • 35
  • 56
0

$data = $this->db->query('SELECT channel, MIN(product_name) as product_name, SUM(revenue) AS revenue FROM my_test_table WHERE channel = "chanel1" AND province='.$area.' GROUP BY SUBSTRING(product_name, 1, 3)')->result();

Note this solution probably is not secure. The variable can be SQL injected. I write the solution under your consideration.

Curlas
  • 879
  • 5
  • 14