Where in Condition Not Accepting String Value

mysql IN Clause not accepting string variable

Your issue is that the values for your IN clause need to be quoted like WHERE whatever IN ('one','two','three') and so forth. You can fix this with $fuel = "'" . implode("','", $fuel) . "'";. One note though, if you're selecting by all possible values, why not just leave AND fuel IN out of your query altogether for that case?

How to execute query when there are no values in where in clause

If all you want is for the query to work, you should be able to use:

SELECT DISTINCT restaurant.*,branch.* 
 from restaurant,branch,restaurant_cuisines,restaurant_collections 
 WHERE restaurant.restaurant_id=branch.restaurant_id 
   AND restaurant_cuisines.cuisine_id IN (2,3) 
   AND restaurant_collections.collection_id IN (NULL);

But this will always return no results.

If you want something a little more flexible, or if you are expecting all results if collection_id does not have any values passed to it, I recommend changing from implicit to explicit joins.

---

Right now you're using an implicit join syntax:

SELECT A.*, B.*, C.* FROM A,B,C 
WHERE A.j=B.j AND A.j=C.j

The problem with this is that if C.j is empty, even if you fix your syntax, your query will return nothing.

Explicit joins allow for a more nuanced approach:

SELECT A.*, B.*, C.* FROM A
INNER JOIN B ON A.j=B.j AND B.v IN (1,2)
LEFT JOIN C ON A.j=C.j AND C.v IN (NULL);

---

When you code, instead of passing an empty string, pass a null value (or, if you're not using prepared statements, pass a string containing NULL). Otherwise, pass the values as a list.

If you need the rows to be ignored only when there are no values, you can do something like this instead:

SELECT A.*, B.*, C.* FROM A
INNER JOIN B ON A.j=B.j AND B.v IN (1,2)
LEFT JOIN C ON A.j=C.j
WHERE (COALESCE(<val>) IS NULL OR C.v IN (<val>));

When you add the LEFT JOIN and then refer to it in the WHERE clause, you can easily check if the C.v contains the list of values, if the value passed is not null by using the COALESCE function.

1. Scenario 1, there are values in , return only those values.
2. Scenario 2, there are no values in and =NULL. Return everything.

In summary, LEFT joins are very flexible and allow you to go back later and optionally enforce a strict INNER JOIN. They should not be used when an INNER JOIN does the job, but most definitely have their uses, such as here.

---

A note on LEFT vs INNER:

• If you want the query to execute even if the list is empty, use LEFT JOIN.
• If you want the query to return results only if there are values in the list, then use INNER JOIN.

Laravel get values that contains string in where clause

first explode $city by space and then use orWhere with like

if(!is_null($city)){
  $query-> where('rent', '>=', $min_price)
  ->where('rent','<=',$max_price)
  ->Where(function ($q) use($city) {
          $words = explode(' ',$city);
          foreach ($words as $word){
          $q->orwhere('city', 'like',  '%' . $word .'%');
      }      
   });
}

i think there are better solutions but this is what i found for now.

Select Query is not working with WHERE clause when there is a space in column value

The ANSI standard says that string comparisons are to be padded to the same length, and I have observed that behavior many times.

I believe the most likely cause of your problem is the last character being a tab or other non space character that simply looks like a space to you.

SQL Query Where Field DOES NOT Contain $x

What kind of field is this? The IN operator cannot be used with a single field, but is meant to be used in subqueries or with predefined lists:

-- subquery
SELECT a FROM x WHERE x.b NOT IN (SELECT b FROM y);
-- predefined list
SELECT a FROM x WHERE x.b NOT IN (1, 2, 3, 6);

If you are searching a string, go for the LIKE operator (but this will be slow):

-- Finds all rows where a does not contain "text"
SELECT * FROM x WHERE x.a NOT LIKE '%text%';

If you restrict it so that the string you are searching for has to start with the given string, it can use indices (if there is an index on that field) and be reasonably fast:

-- Finds all rows where a does not start with "text"
SELECT * FROM x WHERE x.a NOT LIKE 'text%';

---

Related Topics