SQL Like Operator can be used with OR Operator.

SELECT product_id 
FROM `product` 
WHERE
     product_id <> ''
     AND (lower(manufacturer) LIKE '%ACME%') OR (lower(manufacturer) LIKE '%DemoCO%');
     AND (lower(product_name) LIKE '%Anvil%') OR (lower(product_name) LIKE '%Dynamite%');