CODEWITHSUNDEEP
phpmysqlsql
Divyang
Divyang

Reputation: 49

Filter data using where and Like clauses in Mysql

I am filtering data using LIKE keyword in mysql database. This is the following query:

Select c.c_id, c.c_name, slab, 
COUNT(c.c_id) as tot_visit,
SUM(t.bill) as tot_revenue, c.priority, c.ratings 
From tbl_customer c
inner join tbl_transaction t on c.c_id=t.c_id
group by c.c_id 
Where r_id="r1" AND c.slab LIKE "%teen%"

When I am removing this -> AND c.occupation LIKE "%teen%" <- clause, the query is working fine..otherwise it is giving empty set of rows.

I am trying but not overcoming it. Any help would be appreciated.

EDIT I am filtering data by age slabs of teen, young and senior. The same code is working fine in other php files but not in this.

if(isset($_GET['submit']))
{
    if (isset($_GET["teen"]))
    {
        $arguments[] = "c.slab LIKE '%teen%'";
    }
    if (isset($_GET["young"])) {
        $arguments[] = "c.slab LIKE '%young%'";
    }
    if (isset($_GET["senior"]))
    {
        $arguments[] = "c.slab LIKE '%senior%'";
    }

    if(!empty($arguments)) {
      $str = implode(' or ',$arguments);
      $qry = mysql_query('Select c.c_id, c.c_name, c.slab, 
COUNT(c.c_id) as tot_visit,
SUM(t.bill) as tot_revenue, c.priority, c.ratings 
From tbl_customer c
inner join tbl_transaction t on c.c_id=t.c_id
Where r_id="r1" AND "'.$str.'" 
group by c.c_id');

Not getting what is wrong in all this. Kindly help.

Upvotes: 0

Views: 150

Answers (2)

Divyang
Divyang

Reputation: 49

In the following query:

$qry = mysql_query('Select c.c_id, c.c_name, c.slab, 
COUNT(c.c_id) as tot_visit,
SUM(t.bill) as tot_revenue, c.priority, c.ratings 
From tbl_customer c
inner join tbl_transaction t on c.c_id=t.c_id
Where r_id="r1" AND "'.$str.'" 
group by c.c_id');

This line was ambigous:

Where r_id="r1" AND "'.$str.'"

As the variable $str would be like this:

Where r_id="r1" AND '.$str.'

Thanks Stackoverflow for all the help.

Upvotes: 0

GautamD31
GautamD31

Reputation: 28763

Try to write where condition before groupby

Select c.c_id, c.c_name, slab, 
COUNT(c.c_id) as tot_visit,
SUM(t.bill) as tot_revenue, c.priority, c.ratings 
From tbl_customer c
inner join tbl_transaction t on c.c_id=t.c_id
Where r_id="r1" AND c.occupation LIKE "%teen%"
group by c.c_id

Upvotes: 2

Related Questions