PHP parameterized on mysql query with LIKE and CONCAT on partial string

Question

I'm struggling with this query. I'm trying to match one of two fields against a partial string for an ajax call. Start typing a name, it should match against first or last name.

My parameterized query is returning 0 rows on LIKE statement.

I've tried answers from here and here and here.

I'm afraid I'm missing something simple, but I can only think that the parameters aren't passing in right for the partial string.

<? 
$access = 3;
$dbConnect = true;
require "../scripts/php/scriptSecurity.php";

// Partial name given by user.
$name = $_GET["name"];


if (!empty($name)){
    if (strpos($name, " ")){
        $nameParts = explode(" ", $name);
        if (strpos($nameParts[0], ",")) {
            $last = str_replace(",", "",$nameParts[0]);
            $first = $nameParts[1];
        }
        else {
            $first = $nameParts[0];
            $last = $nameParts[1];
        }
    } 
    else {
        $last = str_replace(",", "", $name); 
        $first = str_replace(",", "", $name);
    }

    // Freak out that maybe some hidden character is in the name. 
    $last = preg_replace( "/[^a-zA-Z0-9']/", "", $last );
    $first = preg_replace( "/[^a-zA-Z0-9']/", "", $first );

    if ($last != $first){
        $query = "SELECT * FROM students WHERE LastName LIKE CONCAT('%', ? , '%') AND FirstName LIKE CONCAT('%', ? , '%') ORDER BY LastName, FirstName LIMIT 30" ;
    }
    else {
        $query = "SELECT * FROM students WHERE LastName LIKE CONCAT('%', ? , '%') OR FirstName LIKE CONCAT('%', ? , '%') ORDER BY LastName, FirstName LIMIT 30";
    }

    if ($nameStmt = $connect->prepare($query)){
        $nameStmt->bind_param('ss', $last, $first);

        if (!$nameStmt->execute()) {
            echo $nameStmt->error;
        }


        $result = $nameStmt->get_result();

        $count = 0;

        if (empty($result)){
            while ($row = $result->fetch_assoc()){
                    $count++ ;
                    if ($count % 2 != 0) 
                        $class="odd";
                    else 
                        $class="even"; 
            ?>
                    <div class="studentRow <?php echo  $class ?>"><?php echo $row["LastName"] . ", " . $row["FirstName"] . " " . $row["MiddleName"] ?>  <div><a class="stuPass" id="stuPass_<?php echo $row["id"] ?>" href="scripts/getPass.php?id=<?php echo $row["id"] ?>">Pass</a></div><div><a class="stuDetails" id="stuDetails_ <?php $row["id"] ?>" href="scripts/students/getDetails.php?id=<?php echo $row["id"] ?>">Details</a></div></div>
                    <div class="stuDetails hidden" id="stuDetailsHolder_<?php echo $row["id"]?>"></div>
            <?php 
            }
        }
        else {
            echo "Results are empty.";
        }
    }
    else {
        echo "<br />". $connect->error;
    }
}

Here is the code from uploading the data in the db. I was worried that I might have hidden characters lurking, but I think I have them all stripped out.

$fStu = new SplFileObject('../resources/students.txt');
$fStu->seek($count);
list($year,$building,$id,$last,$middle,$first,$gender,$grade,$gradYear) = explode(",",$fStu->current());    
    if (!empty($year)){
        $stuQuery = "INSERT INTO students (LastName, MiddleName, FirstName, StudentId, Gender, Grade, GradYear) VALUES (?,?,?,?,?,?,?)";
        $stuStmt = $connect->prepare($stuQuery);

        $last = preg_replace( "/[^a-zA-Z0-9']/", "", $last );
        $first = preg_replace( "/[^a-zA-Z0-9']/", "", $first );
        $middle = preg_replace( "/[^a-zA-Z0-9']/", "", $middle );
        $gender = preg_replace( "/\r|\n|\s+/", "", $gender );
        $id = intval(preg_replace('/\D/', '', $id));
        $gradYear = intval(preg_replace('/\D/','', $gradYear));
        $grade = intval(preg_replace('/\D/','', $grade));
        $stuStmt->bind_param("sssisss", $last, $middle, $first,$id,$gender,$grade,$gradYear);
        $stuStmt->execute();
        echo $count . "||" . $last . ", " . $first . " (" . $id . ")";
    }
    else {
        $count = -1;
        echo $count . "||Complete.";
    }

Here's the main page passing the partial name with the querystring.

$(document).ready(function(){
            $("#name").on("change paste keyup", function(e){
                e.preventDefault();
                $.ajax({url: "process/students.php?name=" + $("#name").val(), 
                    success: function(result){
                        $("#results").removeClass("hidden");
                        $("#results").html(result);
                    }
                });
            });

Here's a set of data getting uploaded.

> > Current School Year,Current Building,Student Id,Student Last Name,Student Middle Name,Student First Name,Student Gender,Student
> > Grade,Grad Year  2018,111,11111111111,Doe,Jane,,F,09,2021
> > 2018,111,22222222222,Doe,John,,M,09,2021

Why use concat() at all? Why not just attach the symbols to the variable before sending it through the query? — GrumpyCrouton
– GrumpyCrouton, Commented Dec 11, 2017 at 17:14
I tried that too (stackoverflow.com/questions/9138807/…). I think the outcome is the same, it's just where to append the wildcard right? Still the same issue. — edumacator
– edumacator, Commented Dec 11, 2017 at 17:16
Can you show us an example of the actual search term, with data contained in the database? — GrumpyCrouton
– GrumpyCrouton, Commented Dec 11, 2017 at 17:18
Always check the core documentation to avoid badly reimplementing things like trim. Your stripping function only removes one of a carriage return, a newline or some spaces in one spot. — tadman
– tadman, Commented Dec 11, 2017 at 17:24
Ouch...tadman. I'll go back and review. Thanks for pointing that out. I'm just a teacher trying to write a site for my students, so this can be trying at times. Thanks. — edumacator
– edumacator, Commented Dec 11, 2017 at 17:26

miknik · Accepted Answer · 2017-12-11 20:44:58Z

0

This is tagged as PDO. If you really are using PDO and not mysqli then you cannot bind multiple parameters in a single statement as you are trying to. They should be declared individually and if you are using ? as placeholders they need to be numbered.

If you are using LIKE you must add the % signs to either side of the variable which you are going to bind, you cannot include them in your query as you are trying to.

As far as your query method, instead of trying to split your user input into first and last names why not concat the name fields from the database and search against the result of that instead?

SELECT * FROM students WHERE CONCAT_WS(' ', FirstName, LastName) LIKE '%whatever%';

answered Dec 11, 2017 at 20:44

miknik

5,9671 gold badge14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP parameterized on mysql query with LIKE and CONCAT on partial string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related