SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2013
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Optimize SQL query (MySQL)

    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.

    Code:
    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.


    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by edge82 View Post
    What will be the best way to do this using SQL or PHP.
    php, definitely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2013
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by edge82 View Post
    May I get updated conditional code to be added in sql query so that I will get only required fields in query.
    don't try to use sql for this... use php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2013
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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 .

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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by edge82 View Post
    Do you have idea about conditional php code so that there will not be problem of extra OR or missing OR clause in sql.
    sorry, i do not do php

    Quote Originally Posted by edge82 View Post
    Any idea how to add like condition in sql query based on field is empty or not?
    IS NULL or IS NOT NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2013
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Hi ,

    Thanks for support.

    I solved using array.


    Code:
    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


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •