I have this mysql query:
select
case
when country like '% usa %' then 'usa'
when country like '% italy %' then 'italy'
when country like '% china %' then 'china'
when country like '% india %' then 'india'
end as ccountry,
count(*) as population
from
(
select concat(' ', country, ' ') as country
from Table1
where city = 'cityName'
) T
group by ccountry
i've already try using this query:
$subQuery = Table1::selectRaw("concat(' ', country, ' ') as country")
->where('city', 'cityName');
and this is the next query:
$query = DB::table(DB::raw('('.$subQuery->toSql().') as T'))
->select(DB::raw("case
when country like '% usa %' then 'usa'
when country like '% italy %' then 'italy'
when country like '% china %' then 'china'
when country like '% india %' then 'india'
end as ccountry"), DB::raw('count(*) as population'))
->groupBy('ccountry')
->get();
And this is the error message: SQLSTATE[HY000]: General error: 2031 No data supplied for parameters in prepared statement
select
case
when country like '% usa %' then 'usa'
when country like '% italy %' then 'italy'
when country like '% china %' then 'china'
when country like '% india %' then 'india'
end as ccountry,
count(*) as population
from
(
select concat(' ', country, ' ') as country
from Table1
where city = ?
) T
group by ccountry
i think, the problem is because the value of where city becomes a question mark (?) please if anyone can help, I'm very grateful.
