Optimize SQL query (MySQL)

Databases
1

Hi,

I am using following search query for searching records from table.

I have 5 to 6 columns and I want to add columns only if value is not empty.



SELECT * FROM table WHERE
(
	(description like '$Keyword' OR name like '$Keyword' OR username like '$Keyword') OR
	(qualification like '$Qualification') OR
	(nationality like '$Nationality') OR
	(country like '$Country') OR
	(city like '$City')
)

AND active='1'

ORDER BY tid

I want to add qualification ,nationality and other columns in query only if they are not empty.

Therefore I created following conditional php code but it is adding extra OR or missing OR sometimes.



if($Status!="") { $active = "AND active='$Status' "; }
if($Keyword!="") { $where .= " (description like '$Keyword' OR name like '$Keyword' OR username like '$Keyword') OR  "; }
if($Qualification!="") { $where .= " (qualification like '$Qualification') OR  "; }
if($Nationality!="") { $where .= " (nationality like '$Nationality') OR   "; }
if($Country!="") { $where .= " (country like '$Country') OR   "; }
if($City!="") { $where .= " (city like '$City')    "; }

What will be the best way to do this using SQL or PHP.

Any Idea?

-Thanks

2

php, definitely

3

One thing to point out, do you really need all the fields from the table in the result set? If you don’t you should specify just the ones you need in the SELECT clause

4

Hi guys thanks for support.

May I get updated conditional code to be added in sql query so that I will get only required fields in query.

I want to include all fields.

  • Thanks
5

don’t try to use sql for this… use php

6

Hi r937,

Do you have idea about conditional php code so that there will not be problem of extra OR or missing OR clause in sql.

Any idea how to add like condition in sql query based on field is empty or not?

I have following but it does not work because of problem .

SELECT * FROM table WHERE
					(
						$Keyword
						$Qualification
						$Nationality
						$Country
						(city like '$City')
					)
					
					$active
					
					ORDER BY tid
  • Thanks
7

sorry, i do not do php

IS NULL or IS NOT NULL

8

Hi ,

Thanks for support.

I solved using array.

if($Keyword!="") { $whereArray[] = " (description like '$Keyword' OR name like '$Keyword' OR username like '$Keyword')   "; }
		if($Qualification!="") { $whereArray[] = " (qualification like '$Qualification')   "; }
		if($Nationality!="") { $whereArray[] = " (nationality like '$Nationality')    "; }
		if($Country!="") { $whereArray[] = " (country like '$Country')    "; }
		if($City!="") { $whereArray[] = " (city like '$City')    "; }
		
		$where_separated = implode(" OR ", $whereArray);

-Thanks