Failing to search for full names while querying mysql with php

Question

First time asking on SO. I currently am trying to search a database where the first and last names are seperate. Example:

player_id | first_name   | last_name  
   191         John          Smith
   192         Larry         Citizen
   193         Benjamin      Example

I am trying to allow users to search this list using a full name only. I currently have the following code once the user hits submit, it calls usersearch.php.

session_start();

include '../con.php';

$player = $_POST['name'];

$sql = "SELECT * FROM characters WHERE (concat(first_name,' ',last_name)) = ($player)";

$result = mysqli_query($conn, $sql);

if (!$row = mysqli_fetch_assoc($result)) {

    echo "Found no-one with the name $player. <a href='../search.php'>Try Again?</a>";

} else {

    $_SESSION['selplate'] = $row['plate'];
    $_SESSION['selname'] = $row['first_name, last_name'];

    header("Location: ../profile.php?player=$player");

}

No matter the query it will not find users and always returns "Found no-one with the name $player. Try again?"

This was supposed to be the easy part of this project and I am pulling my hair out.

I have spent over an hour searching SO and Google to no avail so it must be my code? afaik it should work.

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jul 05 '17 at 14:51
What's player name, what name are you searching for and what record is it not finding, ( don't pretend ) I mean exactly... — ArtisticPhoenix, Jul 05 '17 at 14:52
Should do this with binds, but anyway, change the () for quotes - `= ($player)"` to `= '$player'"` — Nigel Ren, Jul 05 '17 at 14:53
I wouldn't search with concat in the WHERE anyway it will prevent the query from using any indexes may have, and destroy performance..... What about first name misplaced with last name? things happen. Personally I would convert the first last name to a compound Text index and do `Match( first, last ) Against ( .... )` but be careful of names like `AL` lol ... all I'm gonna say. — ArtisticPhoenix, Jul 05 '17 at 14:57

Kunal Awasthi · Answer 1 · 2017-07-05T15:16:16.723

0

delete the white-space from concat function in query and other thing is use $player = str_replace(' ','',trim($_POST['name'])) instead of $player = $_POST['name'].

$SQL = "SELECT * FROM TABLE_NAME WHERE concat(first_name,last_name) = '".$player."'"

i suggest PDO with prepared statements...

assume you are searching 'benjamin example' and query will check for 'benjamin example' so str_replace will output as benjaminexample.and the concated first_name and last_name will match it.

hope it help. ignore if it sound silly.

edited Jul 05 '17 at 15:16

answered Jul 05 '17 at 15:00

Kunal Awasthi

310
2
14

what about `example benjamin` will it find that? – ArtisticPhoenix Jul 05 '17 at 15:05

score 0 · Accepted Answer · answered Jul 05 '17 at 15:01

you need qoutes ' ' around player because its a text and also concatinated. if you search Johnsmith it will return nothing if you search John Smith it will give you result, because in your concat you are adding a space between words

"SELECT * FROM characters WHERE concat(first_name,' ',last_name) =('".$player."')"

score 0 · Answer 3 · answered Jul 05 '17 at 15:08

0

I would change it to a fulltext index on both first and last name, change the word min on full text indexes. then I would search using ( some guy as the name )

  MATCH( first, last )AGAINST('+"some" +"guy"' IN BOOLEAN MODE )

answered Jul 05 '17 at 15:08

ArtisticPhoenix

21,464
2
24
38

Failing to search for full names while querying mysql with php

3 Answers3