php+mysql - Like does not display all values

Question

Im making this person search function build up of jquery(json) and php, the from the search bar are sent to the php document but it doesn't behave like i want it to.

I don't know if there's problem with the mysql server or the php code, but when i execute my search script it does not return values on (example):"e","s" or "r" but if i type in the whole name of a person, or just an "a" it returns all names with an "a" in it.

here is a little snip of my script:

    $string = trim(mysql_real_escape_string($_POST['string']));

    $sql = "SELECT * FROM users WHERE firstName LIKE '%$string%' OR lastName LIKE '%$string%' ORDER BY firstName ASC";
    foreach($db->query($sql) as $row) {
    //usage of $row like: $name = $row['firstName']." ".$row['lastName'];
    }

Update:
heres the whole script:

    $sql = "SELECT * FROM users WHERE firstName like '%$string%' OR lastName like '%$string%' ORDER BY firstName ASC";
    foreach($db->query($sql) as $row) {

    $name = $row['firstName']. " ".$row['lastName'];//combing firstname with lastname
    //replacing æ,ø,å,Æ,Ø,Å with html friendly charcodes
    $remove = array("æ", "ø", "å", "Æ", "Ø", "Å"); 
    $with = array("&aelig;", "&oslash;", "&aring;", "&AElig;", "&Oslash;", "&Aring;");
    $fname = str_replace($remove, $with, $name);

    //composing a answer to the user.
    $stringUser[] = "<li class='searchResultHit'><div class='searchResultLeft'><img src='".$row['picture']."'></div><div class='searchResultCenter'><span class='searchResultContent'><a href='user.php?id=".$row['id']."'>".$fname."</a></span></div></li>";
    }

string is reserve keyword. change variable name and then try it. — Abid Hussain, Sep 27 '12 at 18:02
okay so (1) it didn't help changeing the name of $string. (2)why should i echo($sql) that would just output the whole query as text. (4)and what do you mean? it's supposed to work in upper and lower case when its done. tell me more if theres something i don't know here ? :) — Anders Fejerskov, Sep 27 '12 at 18:12
(2) to see what's the query you're executing, maybe then the problem will become obvious. (4) maybe you want `ilike` or lowercase things. Where's (3)? ;-) — Michael Krelin - hacker, Sep 27 '12 at 18:14
(2)so i tried this and yeah.. it came out as expected: SELECT * FROM users WHERE firstName LIKE '%e%' OR lastName LIKE '%e%' ORDER BY firstName ASC (3) ilike? so you mean the sql query should be written in lowercase ? :) and sorry typo with the (4) — Anders Fejerskov, Sep 27 '12 at 18:19
tested it, looks fine in this example http://sqlfiddle.com/#!2/fc7e6/1 — Teena Thomas, Sep 27 '12 at 18:23
i have to admit it works in my mysql page when i do a manually sql execution, but not in the php code. it only works with some charecters and whole names — Anders Fejerskov, Sep 27 '12 at 18:26
No, what I meant is either do something like `lower(firstName) like lower('%e%')` or `firstName ilike '%e%'` (`like` vs. `ilike`). — Michael Krelin - hacker, Sep 27 '12 at 18:28
Perhaps you should show your code in your for loop. Maybe certain strings are killing the code in there? — dispake, Sep 27 '12 at 18:49
okay i updated the orginal post so you can see the actual code. im going to try that Michael — Anders Fejerskov, Sep 27 '12 at 19:01

php+mysql - Like does not display all values

0 Answers0