SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not every field has to be completed

    Hi guys this is my search query

    At the moment every field must be completed to get a result, how can i edit this query so that not every field has to be completed


    PHP Code:
    $query ="select * from jobs where 
    category='
    $category'  AND 
    location='
    $location' AND
    salarytype='
    $salarytype' AND 
    salary='
    $salary' AND 
    jobstatus='
    $jobstatus'

     ORDER BY '
    $order'"


    Many thanks in advance

  2. #2
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Directly? You can't, not easily.

    You could change it to OR instead of AND, but I doubt that will give you the results you're looking for.

    Instead, the easiest way would be to write some PHP to inspect the values of those variables, and if they're set, add in the SQL code to filter on that field.

    BTW, I hope those fields aren't coming directly from a form.... baaaad mojo.

  3. #3
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    where else to search variables come from apart from a form

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "SELECT * FROM jobs WHERE 1=1 ";
    if (!empty(
    $category)) {
      
    $query .= "AND category='$category' ";
    }
    if (!empty(
    $location)) {
      
    $query .= "AND location='$location' ";

    if (!empty(
    $salarytype)) {
      
    $query .= "AND salarytype='$salartytype' ";
    }
    if (!empty(
    $salary)) {
      
    $query .= "AND salary='$salary' ";
    }
    if (!empty(
    $jobstatus)) {
      
    $query .= "AND jobstatus='$jobstatus' ";

    Quote Originally Posted by dnbidder2005 View Post
    where else to search variables come from apart from a form
    If these variables are assigned *directly* from $_GET or $_POST, then your query is vulnerable to SQL injection. Someone could write SQL into the input to alter the query you execute. Escape user input with mysql_real_escape_string() before using it in a query.

  5. #5
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks will look into it

    One more thing
    salary='$salary'
    What would be the code to return all rows with greater than the value?

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dnbidder2005 View Post
    Thanks will look into it

    One more thing


    What would be the code to return all rows with greater than the value?
    Code:
    salary > $salary

  7. #7
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    if (!empty($salary)) {
      
    $query .= "AND salary > $salary ";

    does that look right

    I tried it but it returned no results

  8. #8
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Looks ok but quote your variable
    PHP Code:
    if (!empty($salary)) { 
      
    $query .= "AND salary > '$salary' "

    echo the final $query variable to see if it looks right.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Numeric columns should NOT be quoted.

  10. #10
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys

    Do you have any tutorials regarding mysql_real_escape_string()

  11. #11
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Numeric columns should NOT be quoted.
    I know but can it be guaranteed that the salary will be numeric....?
    eg 12,500 or 12.500.

    Just being cautious
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dnbidder2005 View Post
    Thank you guys

    Do you have any tutorials regarding mysql_real_escape_string()
    The PHP manual is extensive, easy to read, and filled with useful comments from other programmers. You'll find it at http://php.net. There's a search box in the upper right; type mysql_real_escape_string in there and you'll find what it's for, how to use it, and lots of other info.

    Quote Originally Posted by spikeZ View Post
    I know but can it be guaranteed that the salary will be numeric....?
    eg 12,500 or 12.500.

    Just being cautious
    A job for the application, not the database. Input should be parsed before it's placed in a query

  13. #13
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    A job for the application, not the database. Input should be parsed before it's placed in a query
    agreed
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


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
  •