concat two columns

Question

how do I concat two columns? I need it to have FIRSTNAME AND LASTNAME to equal $Employee_Name I tried different combinations and still doesn't work. Also I know this has a huge SQL injection issue. I do not know yet how to correct the issue I am looking it up.

<?php

    header('Content-Type: application/json');$db_conx = mysqli_connect("localhost", "root", "systems399","employees_db");
    $Employee_Name=  $_POST["Employee_Name"];
     $sql="SELECT * FROM  names WHERE FIRSTNAME='$Employee_Name'   ";
    $query= mysqli_query($db_conx, $sql);
    $row = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $rc= $row["EMPLOYEE_NUMBER"];
    echo json_encode ($rc);
     ?>

I tried it this way and it doesn't work.

<?php
    header('Content-Type: application/json');
    $db_conx = mysqli_connect("localhost", "root", "systems399", "employees_db");
    $Employee_Name=  $_POST["Employee_Name"];
     $sql="SELECT * FROM  names WHERE FIRSTNAME='$Employee_Name' AND LASTNAME='$Employee_Name'";
    $query= mysqli_query($db_conx, $sql);
    $row = mysqli_fetch_array($query, MYSQLI_ASSOC);
    $rc= $row["EMPLOYEE_NUMBER"];
    echo json_encode ($rc);
 ?>

Here is my Javascript code

$(document).ready(function() {
    $("#Employee_Name").change(function() {
        var Employee_Name = $(this).val();
        if (Employee_Name != '') {
            $.ajax({
                type: "post",
                url: "insert.php",
                data: "Employee_Name=" + Employee_Name,
                datatype: "json",
                success: function(data) {
                    $("#Employee_Number").val(data);
                    $('#Employee_Number').css("background-color", "#B3CBD6")
                    $('#Employee_Number').animate({
                        backgroundColor: "#ffffff"
                    });
                },
                error: function(response) {
                    alert("error scripting")
                }

            });
        } else {
            $("#Employee_Number").val("");
        }

    });
});

I will try that I did that without CONCAT before so maybe with CONCAT in front will work. — Donny, Oct 24 '14 at 15:35
$sql="SELECT * FROM names WHERE CONCAT(FIRSTNAME, " ", LASTNAME) = '$Employee_name'"; I get an error — Donny, Oct 24 '14 at 15:37
@Donny What error? It's hard for us to help without error information. — Jim, Oct 24 '14 at 15:38
@Jim how would I rewrite that so it does not get SQL injection I am new to PHP and MySQL or can you direct me to a book or website to learn proper method — Donny, Oct 24 '14 at 15:39
@Donny Here is a good SO question: http://stackoverflow.com/q/60174/505722 — Jim, Oct 24 '14 at 15:40
The double quotes inside the CONCAT() are likely to clash with the ones enclosing the whole statement in a PHP script. Try replacing them with single quotes. — some-non-descript-user, Oct 24 '14 at 15:43
That worked I get no errors but my script is not populating the employee number into the textbox. I am able to populate it when I use this. $sql="SELECT * FROM names WHERE FIRSTNAME='$Employee_Name'"; But this only allows user to type firstname only into the text field. I need it to be able to type first and last name into the field. — Donny, Oct 24 '14 at 16:21
@Donny: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Marc B, Oct 24 '14 at 16:21

score 2 · Accepted Answer · answered Oct 24 '14 at 16:20

2

There are 2 posibilities:

1.

$sql = "SELECT CONCAT(`FIRSTNAME`, ' ', `LASTNAME`) AS `EmployeeName`, * FROM `names` HAVING `EmployeeName` = '".$Employee_name."'";

2.

$sql = "SELECT * FROM `names` WHERE CONCAT(`FIRSTNAME`, ' ', `LASTNAME`) = '".$Employee_name."'";

answered Oct 24 '14 at 16:20

AlexL

1,699
12
20

2nd one should work I just have to look at my JavaScript see why it is not pulling the employee number. – Donny Oct 24 '14 at 16:32
both should work I just have to look at my JavaScript see why it is not pulling the employee number. It is weird because when I had it with first name it works now trying to type both first and last doesn't – Donny Oct 24 '14 at 16:38
Yes, both are the same just using different filtering clause. – AlexL Oct 24 '14 at 16:39
I am not sure why when I type the first and last name into my text box it does not want to pull the employee number to auto fill the employee text box. I am able to do it with this example $sql="SELECT * FROM names WHERE FIRSTNAME='$Employee_Name'"; with first name – Donny Oct 24 '14 at 16:45

score 1 · Answer 2 · answered Oct 24 '14 at 16:09

1

Here is an example -

$sql = "SELECT CONCAT(`FIRSTNAME`, ' ', `LASTNAME`) AS `EmployeeName`, * FROM `names` WHERE `FIRSTNAME` = '" . $First_Name . "' AND `LASTNAME` = '" . $Last_Name' . "' ";

answered Oct 24 '14 at 16:09

Jay Blanchard

34,243
16
77
119

What do you mean by "errors out my script"? What error is it giving you? – Jay Blanchard Oct 24 '14 at 16:18
@Jay your solution is wrong. He needs to search by the entire full name not parts of the name like in your script. See my answer – AlexL Oct 24 '14 at 16:22
It's hard to know what is right @AlexLinte because the feedback from the OP is sparse. – Jay Blanchard Oct 24 '14 at 16:24
I have a script that grabs the name in the textbox and autofills employee number into the other text box. It works with first name. But I want it so employees can type first and last name. This works with my script but only if user types firstname. I am trying to figure how to insert my javascript into this post. $sql="SELECT * FROM names WHERE FIRSTNAME='$Employee_Name'"; – Donny Oct 24 '14 at 16:24

concat two columns

2 Answers2