SQL query doesnt find exact name from database

Question

SELECT * FROM hge_funcionarios 
 JOIN hospitais
 ON hge_funcionarios.hospital_id = hospitais.id_hospitais 
 JOIN funcoes
 ON hge_funcionarios.funcao_id = funcoes.id_funcoes
WHERE nome LIKE '%$search%'
ORDER BY hospital_id DESC

When I try the exact name from the database doesnt show up any results. If i search "Larissa" or "LARISSA", I get no results even in my database having "LARISSA CAMPOS".

If I try "lar" or anything like this I can find it, but when it gets too close to the name on database like "LARISS" I can't find it any more. I tried collate and charset but no success.

EDIT: Its not a Query error with ambiguous column name in SQL because column names are distinct.

@UlissesSilva, you really should never put a variable sourced from user input directly into a query. Use prepared statements. — Devon Bessemer, Jul 05 '18 at 23:06
Does `= "LARISSA CAMPOS"` work? Sounds like it could be a special character in the name. — Devon Bessemer, Jul 05 '18 at 23:08
Possible duplicate of [Query error with ambiguous column name in SQL](https://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql) — mickmackusa, Jul 06 '18 at 00:57
You should switch on your error/warning/notice warnings whenever things don't go as planned. Always check your error logs before posting a question SO -- this helps us to quickly and accurately answer your question. ...but more importantly, you can usually avoid asking a question if you take the time to investigate and research fully before asking for support. — mickmackusa, Jul 06 '18 at 00:59
I dont think its duplicated post i check all and Its not "Query error with ambiguous column name in SQL" — Ulisses Silva, Jul 06 '18 at 01:56
Here you go: https://www.w3schools.com/php/php_mysql_prepared_statements.asp — The Impaler, Jul 06 '18 at 02:00
@TheImpaler I really dont understand how this can prevent this error, if I need to write the code and I can make some mistakes . Can u explain? — Ulisses Silva, Jul 06 '18 at 02:06
Try to search for other names and rule out the possibility of the error coming from search character length — Bobby Axe, Jul 06 '18 at 02:17
@BobbyAxe If it is character length what I can do to solve?? — Ulisses Silva, Jul 07 '18 at 20:22
please notice that adding back ticks in stackoverflow highlights the word — Bobby Axe, Jul 08 '18 at 03:08
in that case (1) check the database character type length for the `nome` column (2) check if you have overlapping columns on the both tables, that is columns with the same name (3) add back ticks to WHERE `nome` LIKE '%$search%' (4) concatenate your search input like so $search= '%".$search."%' and replace the current one like so WHERE `nome` LIKE $search (5) run your query as it is now but remove the last line remove ORDER BY hospital_id DESC — Bobby Axe, Jul 08 '18 at 03:10

score 1 · Answer 1 · answered Jul 06 '18 at 02:17

1

I'm writing this answer since it's not possible to show it in the comments. Feel free to disregard it.

The problem you are facing seems to be related to the injection of parameter values into your SQL query. The easy (dangerous) way is to simply concatenate strings, as in:

$stmt = $conn->prepare(
  "select * from my_table where name = '" . $param1 . "'");

Even though it works for simple cases, your case is more complicated, and confusing. Most of the time you'll use Prepared Statements as in:

$stmt = $conn->prepare("select * from my_table where name = ?");
$stmt->bind_param("sss", $param1);

This way, the parameter will be injected the right way. In your case you'll need to prepend and append % to your parameter, since it'll be used for a LIKE operator.

answered Jul 06 '18 at 02:17

The Impaler

45,731
9
39
76

about sql injection I used it $conn->real_escape_string($_GET['search']); – Ulisses Silva Jul 06 '18 at 02:34
but is not something big is a private application. I dont see reason for the user sql inject his only information. – Ulisses Silva Jul 06 '18 at 02:35
is better I waste my time learing Prepared Statements on MYSQLi or PDO? – Ulisses Silva Jul 06 '18 at 02:38
PDO is the newest standard. – Bleach Jul 06 '18 at 03:49

score -1 · Answer 2 · answered Jul 06 '18 at 06:08

-1

WHERE nome LIKE '%$search%'

May be $ is the Reason.Try Like : WHERE nome LIKE '%search%'

answered Jul 06 '18 at 06:08

Anji

35
6

That is clearly just a variable that's being added. – Magisch Jul 06 '18 at 07:19

SQL query doesnt find exact name from database

2 Answers2