The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MySQL Help
|
Searching for full name in database when first and last names are in dif columns
Discuss Searching for full name in database when first and last names are in dif columns in the MySQL Help forum on Dev Shed.
Searching for full name in database when first and last names are in dif columns MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
September 10th, 2012, 09:40 AM
|
|
Contributing User
|
|
Join Date: Jul 2012
Posts: 40
Time spent in forums: 19 h 25 m 11 sec
Reputation Power: 1
|
|
|
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 
|
September 10th, 2012, 10:41 AM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | 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%'
|
September 10th, 2012, 10:48 AM
|
|
Contributing User
|
|
Join Date: Jul 2012
Posts: 40
Time spent in forums: 19 h 25 m 11 sec
Reputation Power: 1
|
|
Quote: | 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?
|
September 10th, 2012, 11:25 AM
|
|
|
|
OR (last_name LIKE '%$firstname%'
AND first_name LIKE '%$lastname%')
?
|
September 10th, 2012, 11:40 AM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | 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
|
September 10th, 2012, 01:36 PM
|
|
Contributing User
|
|
Join Date: Jul 2012
Posts: 40
Time spent in forums: 19 h 25 m 11 sec
Reputation Power: 1
|
|
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.
|
September 10th, 2012, 02:05 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | 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
|
September 10th, 2012, 02:13 PM
|
|
Contributing User
|
|
Join Date: Jul 2012
Posts: 40
Time spent in forums: 19 h 25 m 11 sec
Reputation Power: 1
|
|
Quote: | 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 != 1 && $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.
|
September 10th, 2012, 03:04 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
please echo out the entire sql statement
also, please describe what "not working" means... error message? 0 rows returned? wrong rows returned? computer crashes?
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
