PHP - search using 2 concatenated fields

Question

I've concatenated 2 fields in a form's drop down list, with the 2 fields being First Name and Last Name; This allowed me to display a person's full name.

The issue is that in the database a person's full name is stored in 2 seperate fields; First Name and Last Name.

I need to match the person's full name (from the dropdown list) to the First Name and Last Name in the DB, however I am struggling to achieve this.

The php code for the concatenated form list is:

<td>
  <select name='IndivSurname'>";
        while($FullName_row = odbc_fetch_array($sql_run_FullName)){
            $IndivFirstName=$FullName_row['FirstName'];
            $IndivLastName=$FullName_row['LastName'];
            echo"<option value='$IndivIndivId' . '$IndivTenantNdx'> $IndivFirstName $IndivLastName</option>";
    }           
   </select>
</td>

While the SQL statement is:

SELECT EventId, EventTime, Individual, Tenant, TenantName, DeviceName, Comment, 
InetDb.dbo.Individuals.FirstName, InetDb.dbo.Individuals.LastName
FROM taclogdata.dbo.Event
   LEFT JOIN InetDb.dbo.Tenants 
      ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
   LEFT JOIN InetDb.dbo.Individuals
      ON taclogdata.dbo.Event.Individual = InetDb.dbo.Individuals.IndivId 
      AND taclogdata.dbo.Event.Tenant = InetDb.dbo.Individuals.TenantNdx
   WHERE (taclogdata.dbo.Event.EventTime BETWEEN '00:00:00 05/26/2015' 
      AND  '09:00:00 05/26/2015' 
      AND (taclogdata.dbo.Event.Comment ='Reader entry' 
      OR taclogdata.dbo.Event.Comment='Reader exit') 
      AND (InetDb.dbo.Individuals.FirstName = '$IndivFirstName' 
      AND InetDb.dbo.Individuals.LastName = '$IndivLastName')";

Many thanks in advance

exactly what @daan said: What happens if you have two or more duplicate names, e.g. "John Smith"? It'd be impossible to tell which one was selected. You need to use a proper primary key as the option's value. — Marc B
– Marc B, Commented Jul 8, 2015 at 14:26
@Daan Yes you're right. Forgive me but I'm still a beginner at this. How would I amend the SQL code then please? — joebegborg07
– joebegborg07, Commented Jul 8, 2015 at 14:26

ChristianF · Accepted Answer · 2015-07-08 14:31:16Z

The easiest way to do this, is by splitting the string on the space. However, that introduces a bug if the person has more than one first- or surname. This means that in the cases where we have 3 or more elements in the result of the split, we'll have to add the middle elements to both matches; Seeing as we don't know whether it's a surname or middle name.
That is why I recommend using a different character for the glue in the value attribute, one which is never used in a name. # is one such character.

This leaves you with the following code for the form-generation:

// CF: Added HTML-escaping to prevent XSS-attacks.
$IndivFirstName=htmlspecialchars ($FullName_row['FirstName']);
$IndivLastName=htmlspecialchars ($FullName_row['LastName']);

echo"<option value='{$IndivFirstName}#{$IndivLastName}'> $IndivFirstName $IndivLastName</option>";

In the SQL-statement you can do the following (using prepared statements):

$stmt = $db->prepare ("SELECT EventId, EventTime, Individual, Tenant, TenantName, DeviceName, Comment, InetDb.dbo.Individuals.FirstName, InetDb.dbo.Individuals.LastName
FROM taclogdata.dbo.Event
 LEFT JOIN InetDb.dbo.Tenants 
  ON taclogdata.dbo.Event.Tenant = InetDb.dbo.Tenants.TenantId
LEFT JOIN InetDb.dbo.Individuals
  ON taclogdata.dbo.Event.Individual = InetDb.dbo.Individuals.IndivId 
  AND taclogdata.dbo.Event.Tenant = InetDb.dbo.Individuals.TenantNdx
WHERE (taclogdata.dbo.Event.EventTime BETWEEN '00:00:00 05/26/2015' AND '09:00:00 05/26/2015' 
  AND (taclogdata.dbo.Event.Comment ='Reader entry' OR taclogdata.dbo.Event.Comment='Reader exit') 
  AND (InetDb.dbo.Individuals.FirstName = :firstname AND InetDb.dbo.Individuals.LastName = :lastname)");
$name = explode ("#", $_POST['IndivSurname']);
$stmt->exec (array (":firstname" => $name[0], ":lastname" => $name[1]));

James Anderson · Accepted Answer · 2015-07-08 15:45:03Z

1

Splitting on spaces is not going to be reliable given the number of De Aches and Mc Pains in this world.

The really sure way of doing this is to save the first name and last name in a hidden table within your form (in the same order as your pulldown).

A possible alternative is to concatenate using a not a space but one of the other white space characters like NO BREAK SPACE "\u00A0" which you can then reliably split later.

answered Jul 8, 2015 at 15:45

James Anderson

27.5k7 gold badges55 silver badges81 bronze badges

1 Comment

joebegborg07 Over a year ago

I did split on spaces however I didn't POST the First Name and Last Name through the form. Alternatively I've POSTED the User's PK. Thanks for your input though

joebegborg07 · Accepted Answer · 2015-07-09 07:59:53Z

0

Ok I solved my problem by POSTing the user's PK rather than his First Name and Last Name individually, and search using those characteristics.
However I introduced the explode function as per the below to separate the concatenated values as per the below:

SQL query selects the IndivId, TenntNdx, individual's first name and last name, however only the first name and last name of the user are displayed in the form's drop down list. The matching IndivId and TenantNdx are posted using the code below:

$sql_FullName = "SELECT IndivId, TenantNdx, FirstName, LastName FROM InetDb.dbo.Individuals
                    ORDER BY FirstName ASC";
    $sql_run_FullName = odbc_exec($conn_general, $sql_FullName);    
while($FullName_row = odbc_fetch_array($sql_run_FullName)){
                            $IndivIndivId = $FullName_row['IndivId'];
                            $IndivTenantNdx = $FullName_row['TenantNdx'];
                            $IndivFirstName=$FullName_row['FirstName'];
                            $IndivLastName=$FullName_row['LastName'];
                            echo"<option value='$IndivIndivId $IndivTenantNdx'> $IndivFirstName $IndivLastName</option>";

The code above would POST the IndivId and TenantNdx (which form a composite primary key for the individuals table) to the page containing the code below which would then separate the 2 fields and search by the PK rather than the individual's name:

$IndivSurname = $_POST['IndivSurname'];
                $explode_sql = explode(" ", $IndivSurname, 2);
                    $ListIndivId = $explode_sql['0'];
                    $ListTenantId = $explode_sql['1'];

answered Jul 9, 2015 at 7:59

joebegborg07

8594 gold badges15 silver badges29 bronze badges

2 Comments

ChristianF Over a year ago

You really should add htmlspecialchars() to the code that outputs the names in the HTML form, to prevent XSS attacks. Same with using prepared statements, if you aren't already. It's not a matter of "if" you are going to be attacked, but "when"; All online systems will be attacked, and if not properly secured your users will pay the price. * * Glad you got it sorted out though, and that we could be of assistance. :)

joebegborg07 Over a year ago

It's not exactly online as it's just a system used between myself and a few colleagues (internal). However I will try your suggestion. Thanks

Collectives™ on Stack Overflow

PHP - search using 2 concatenated fields

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related