How to use WHERE and LIKE with a variable

[cainam29]

I have a login session where it checks the user name and displays it to the form (it displays FirstName LastName). That username is also being used as a variable to pull up data in db. Now I also have another page where user's can update the db, I dont have a problem if the user will update it with a complete FirstName LastName entry because it will just be the same as the one's being used by the login session, but sometimes they just update it with FirstName.

The problem starts when I have values in TEST db under Tester column and in USERS db under User column that is of different values. It would have two diff values when a user did not enter the full FirstName LastName.

For example,

FirstName1 LastName1 is the value in USERS db User column - this is permanent and is being used in a login session

User updated Tester column in TEST db with just FirstName1 - this is different from the User column above

Here is what I am trying to do,

Getting list of tickets from the TEST db where datefrom and dateto and using a variable for the values that is in User column under USER DB

If User column under USERS db = Tester column under Test db which is FirstName1 LastName1 - it will be good as I will be able to get tickets under FirstName1 LastName1.

But I will not be able to get ticket which is still assigned to that same person because the value in Tester column under Test db is just FirstName1.

If User column under USERS db (FirstName1 LastName1) is not equal to Tester column under Test db which is just FirstName1 - I will not get tickets assigned to FirstName1 as my variable is equal to FirstName1 LastName1.

I hope that I explained it clearly, here is my code,

Here is the variable that I am posting,

$uid = false;
if(isset($_POST['uid'])){
    $uid = $_POST['uid'];
 } 

And here is the query,

$sql = 'SELECT `id`, `date_implemented`, `tester`, `comments` 
        FROM `tracker` WHERE `tester` = :uid AND `scheduled_start_date` BETWEEN :d1 AND :d2';
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':uid', $uid, PDO::PARAM_STR);
        $stmt->bindParam(':d1', $date['from'], PDO::PARAM_STR);
        $stmt->bindParam(':d2', $date['to'], PDO::PARAM_STR);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

I would need help in passing that variable such that I can use WHERE tester LIKE uid. In that case whether the tester column just contains FIRSTNAME1 or a complete FIRSTNAME1 LASTNAME1, I would be able to get all tickets assigned to FIRSTNAME1.

[pty]

And the moral of the story is, don't try to join tables using manually-entered string/varchar fields. It just won't work. Some people will enter their name as 'Timothy' in some forms, 'Tim' in others and ocassionally 'Timmy'.

Your user table should have a automatically incrementing numeric primary key. Your tracker should have a field called user_id, and you should set up a foreign key between those two tables.

Then your query would be sensible:

select
    t.id,
    t.date_implemented,
    u.name,
    t.comments

from
    tracker t
inner join
    users u
        on t.user_id = u.id
where
    t.scheduled_date_start between :d1 and :d2
and
    u.username = :uname
    -- or if you prefer, u.id = :uid

[JamesCherrill]

Another observation is that firstname is so far from unique that it's completely useless on its own as a key. Just wait until you have 100k users and you search on "John".
I suggest you go back a step and avoid this ever happening, eg default the name sfields in an update to the logon values ???
But anyway, as pty says, you need a guaranteed unique primary key, and firstname+lastname isn't unique.

[AndrisP]

My comment will not answer to your question but:

Is the column uid with type VARCHAR? I think it should be integer. If its true then bind variable as PDO::PARAM_INT and I recommend use

$uid = filter_input(INPUT_POST, 'uid', FILTER_VALIDATE_INT);

instead of

    $uid = false;
    if(isset($_POST['uid'])){
        $uid = $_POST['uid'];
     }

function filter_input() set variable to NULL if not set or value not parsed as integer.