0

I have a variable in PHP. I want to check my PHP variable in MySQL query and if it is not null use it in a where clause.

select * from T1
where post = "news" and  $city = cityname and $city is not null

$city is PHP variable.

I have a problem when $city is null, that should show all news posts but it returns nothing.

this is my table: enter image description here

Nick
  • 138,499
  • 22
  • 57
  • 95
Amin.A
  • 45
  • 7
  • `$city` is a column name ? – executable Nov 28 '18 at 10:52
  • 5
    You should not even need the `$city IS NOT NULL` check, AFAIK. By the way, you should read about how to use prepared statements in PHP. – Tim Biegeleisen Nov 28 '18 at 10:53
  • Possible duplicate of [Use a $variable inside a SQL string?](https://stackoverflow.com/questions/8759372/use-a-variable-inside-a-sql-string) – Smollet777 Nov 28 '18 at 10:55
  • @executable no it's my variable in PHP. – Amin.A Nov 28 '18 at 10:55
  • Yes, but the `value` refer to a column name ? – executable Nov 28 '18 at 10:56
  • You should post your PHP code here – Nuwan Attanayake Nov 28 '18 at 11:02
  • @executable no i got a number by post method and i wanna use that in my where – Amin.A Nov 28 '18 at 11:03
  • @Smollet777 i have a problem when $city is null , I mean when $city = 7 . I don't have any problem but when it is null , that should show all of news – Amin.A Nov 28 '18 at 11:05
  • $_POST['city']; $city= $_POST['city']; – Amin.A Nov 28 '18 at 11:07
  • 2
    So you have column with name "7" ? – Eakethet Nov 28 '18 at 11:08
  • I think you need to reverse like `cityname = $city` – executable Nov 28 '18 at 11:08
  • @Eakethet no is a field in id column and I wanna get other fields of that row. – Amin.A Nov 28 '18 at 11:11
  • Can you show the table `T1` ? – executable Nov 28 '18 at 11:11
  • you can't use IS NOT NULL in _SQL_ to check the status of a _PHP_ variable. It can only be used to check the value in your column. Instead in PHP you should check this value before deciding whether to add the condition to the WHERE clause of your query or not. Currently if $city is null then your query will end up like `select * from T1 where post = "news" and NULL = cityname and NULL is not null` which just makes no sense at all. – ADyson Nov 28 '18 at 11:20
  • Equally if $city is populated, then it will be like `select * from T1 where post = "news" and 7 = cityname and 7 is not null`. Which will work but the last bit is totally redundant. You probably ought to make it into `cityname = $city` for readability as well, that's the conventional way to write it. – ADyson Nov 28 '18 at 11:22
  • Basically you need some PHP which will check if $city is null or not, and then decide whether to add the `and cityname = $city` part to the SQL or not. And you should use parameterised queries as well, to save yourself from SQL injection attacks. – ADyson Nov 28 '18 at 11:24
  • 1
    If you get this value from a form (`$_POST['city']`), then most likely it is never NULL to begin with, but rather an empty string. – misorude Nov 28 '18 at 11:43

2 Answers2

3

Since $city is a PHP variable, if it is NULL then when you echo it in your query you will simply get nothing. That will make an invalid query; it will look like this:

select * from T1
where post = "news" and   = cityname and  is not null

To make this work, you need to enclose $city in your query in quotes, and then rather than comparing it to NULL, compare it to the empty string i.e.

select * from T1
where post = "news" and  ('$city' = cityname or '$city' = '')

Note that the correct logical operator is or for this use case.

As was pointed out in the comments, you should look into prepared statements. This question has some really useful information: How can I prevent SQL injection in PHP?

Nick
  • 138,499
  • 22
  • 57
  • 95
-1

Try this

select * from T1
where post = "news" and  ($city = cityname or $city is not null )
Nick
  • 138,499
  • 22
  • 57
  • 95
user10716845
  • 9