#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    40
    Rep Power
    3

    Searching for full name in database when first and last names are in dif columns


    So I'm sure this is fairly a simple question, I'm just not up to speed on my mysql database functions.

    I have two columns in my 'jumperprofiles' table named 'first_name' and 'last_name'. I have a form that has a dropdown with options: name, personal record, college.

    At the moment, I can only search for the first name of a person in my database. I just made it that way cause thats all I knew. When name is selected, the value is 'first_name' and that is the $category. The input is the $criteria. This is the mysql statement that I've been working with.
    PHP Code:
    $exSql "SELECT * FROM `jumperprofiles` WHERE $category LIKE '%".$criteria."%' ORDER BY PR DESC LIMIT $first$max"
    I want to be able to type the full name into the input, either backwards or forwards, (ie. "smith, John" or "John smith") and this is the mysql statement that I've started with but I'm a little lost.
    PHP Code:
    $exSql "SELECT CONCAT_WS(' ', 'first_name', 'last_name') AS 'name' FROM 'jumperprofiles' WHERE name LIKE '%".$criteria."%' ORDER BY PR DESC LIMIT $first$max"
    Can anyone steer me in the right direction? Thanks for the help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by jel5363
    Can anyone steer me in the right direction?
    use the programming power of your application language (php or whatever) to analyze the contents of the form fields, split them up into first and last name values, and then query the table using two field and two values
    Code:
    WHERE first_name LIKE '%$firstname%'
      AND last_name LIKE '%$lastname%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    40
    Rep Power
    3
    Originally Posted by r937
    use the programming power of your application language (php or whatever) to analyze the contents of the form fields, split them up into first and last name values, and then query the table using two field and two values
    Code:
    WHERE first_name LIKE '%$firstname%'
      AND last_name LIKE '%$lastname%'
    Alright thanks for that. Setting aside my need to learn how to do that, I already have a question. How will "it" know which is which when it splits it up? I see what you're saying. You mean to take the $_POST and split that up into $first and $last, but what if the user put the last name first and the first name last? Get what I'm saying?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    OR (last_name LIKE '%$firstname%'
    AND first_name LIKE '%$lastname%')

    ?
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by jel5363
    How will "it" know which is which when it splits it up?
    php is ~way~ more flexible and powerful at breaking apart the contents of a single form field into two values than sql can ever hope to be

    right tool for the right job, sir
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    40
    Rep Power
    3
    so I tried what you both said, and this is what I came up with, which isn't working, but I'm sure its something simple.

    PHP Code:
    $names explode (" "$criteria);
        
    $firstname $names[0];
        
    $lastname $names[1];
        
    $exSql "SELECT * 
    FROM `jumperprofiles` 
    WHERE first_name LIKE '%
    $firstname%' 
    AND last_name LIKE '%
    $lastname%' 
    OR first_name LIKE '%
    $lastname%' 
    AND last_name LIKE '%
    $firstname%' 
    ORDER BY PR DESC 
    LIMIT 
    $first$max
    When I echo out $firstname and $lastname I get the two seperate names, so I'm not sure where I went wrong.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by jel5363
    When I echo out $firstname and $lastname I get the two seperate names, so I'm not sure where I went wrong.
    please echo out the entire sql statement

    i don't do php, but it seems you're not actually executing that sql statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    40
    Rep Power
    3
    Originally Posted by r937
    please echo out the entire sql statement

    i don't do php, but it seems you're not actually executing that sql statement
    Yea sorry, I have more to it.
    PHP Code:
        if ($exQuery mysqli_query($dbcon$exSql)) {
             if (
    $num_rows2 != 0) {
            echo 
    "<b>" .$frop."</b> through <b>" .$lrop."</b> of "
            }
            echo 
    "<b>" .$num_rows2."</b> Jumpers found";
            
            if (
    $totPages != 0) {
                if (
    $totPages != && $totPages != 0) {
                    echo 
    paginate_three($reload$page$tpages$adjacents);
                    }
    // end of if total pages doesn't equal 1 or 0
                
    echo "<hr /></div></div>";
                echo 
    "<div>";
                echo 
    "<table id='search' style=\"table-layout: fixed; width: 671px;\">";
                echo 
    "<tr> 
                <th id='jumpername'>Jumper</th> 
                <th width='6%'>Year</th> 
                <th width='10%'>Metric</th> 
                <th width='25%'>Team</th> 
                <th width='10%x'>Gender</th> 
                <th width='14%'>Level</th> 
                <th width='10%'>Nat.</th>"
    ;
                while (
    $row mysqli_fetch_assoc($exQuery))........ you get the idea 
    Do you think its the notation I'm putting the sql in? like a ' instead of a " or nothing at all instead of a ` .... Idk, because I know I have all the right parts, but I think a piece is turned around or something.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    please echo out the entire sql statement

    also, please describe what "not working" means... error message? 0 rows returned? wrong rows returned? computer crashes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo