I want to generate a query of this form:
select b.* from brands b
inner join products p on b.id = p.brand_id
where p.category_id = 'category' and p.subcategory_id = 'subcategory'
group by p.brand_id;
If I use
public static function brands_by_subcategory ($category_id, $subcategory_id) {
return self::query ('b.*')
->from ('brands b inner join products p on b.id = p.brand_id')
->where ('p.category_id', $category_id)
->where ('p.subcategory_id', $subcategory_id)
->group ('p.brand_id')
->fetch ();
}
The generated query looks like
select b.* from brands b
inner join products p on b.id = p.brand_id
where `p.category_id` = 'category' and `p.subcategory_id` = 'subcategory'
group by `p.brand_id`
...which, apparently because of the backticks, generates unknown column errors in MySQL (p.category_id, p.subcategory_id, p.brand_id).
Since there's only one brand_id
in the two tables, I can make it work using
public static function brands_by_subcategory ($category_id, $subcategory_id) {
return self::query ('b.*')
->from ('brands b inner join products p on b.id = p.brand_id')
->where ("p.category_id = '$category_id'")
->where ("p.subcategory_id = '$subcategory_id'")
->group ('brand_id')
->fetch ();
}
Is there a better approach?
Comments
Looks like the backticks shouldn't be including the
.
character. Here's a quick fix forModel::backticks()
, let me know if it fixes it.Also a quick tip: you can rewrite your where clauses to avoid SQL injection like this:
This allows for more complex expressions while still being secure :)
Works great. And thanks for the tip -- I knew there must be a better way!
Awesome, just pushed the fix to Github!
Is there any way to do a LIKE query and avoid SQL injections? Something like
$query->where ('name LIKE %?%', $_GET['q'])
, but that renders asname LIKE %'search string'%
instead ofname LIKE '%search string%'
.Maybe there's a method to call? Something like
$query->where ("name LIKE '%".DB::method($_GET['q'])."%'")
How about this?
Wouldn't that be vulnerable to SQL injection?
Never mind, I see what you did. I'll try it.
It works. :)