Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jul 2012
    Location
    Boston
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    POST and sql "LIKE" problems.

    Hello everyone!
    I'm here because I'm sick of being treated like an idiot at Stack Overflow. Hope people aren't as elitist here.

    I'm trying to get this query to work after spending a WEEK now on autocomplete from scratch on YT.
    Code:
    $sql = "SELECT * FROM table WHERE title LIKE '%$input%' OR column LIKE '%".$input."%.'";
    I'm sending post data in and recieving it as usual. $_POST['$input']. The query works and dumps the whole table when I test with -> SELECT * FROM table. So what am I doing wrong? Even the mysql website confirmed doing it correctly with the method above.

    I'm going to create a tutorial after this one!

    Any help would be appreciated.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Can you show the code that creates the $input variable, as well as the query and the fetching too?

    Edit:
    May be easier than I think. Just noticed this here: '%".$input."%.'". Whilst the '%" . and ."% are correct, is that supposed to be .".'? That indicates that you must end the input with a period if you are matching column (which cannot be named column btw since it's a reserved word - if you actually have it named column, you must use backticks (`) to surround the field name for more information refer here for reserved words: http://dev.mysql.com/doc/refman/5.5/...ved-words.html).
    Assuming that column is not the name though, you should still have matches even if that period is irrelevant and assuming that the input matches something within the title field.
    Last edited by Fou-Lu; 07-07-2012 at 08:44 PM.

  • Users who have thanked Fou-Lu for this post:

    Webenvelopment (07-08-2012)

  • #3
    New Coder
    Join Date
    Jul 2012
    Location
    Boston
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thanks for your legit reply!

    Already seeing a much more efficient community here thanks!

    This is coming from a tutorial on YT regarding autocomplete and creating a word search with your database. The tutorial's PHP just seems to work like magic for Hari but not for production I guess? I started this project with connecting and querying with PDO and prepared statements which obviously didn't work for the same reasons...

    I've backtracked to this code below to figure out how to query it properly...

    After coming up with issues I started testing the PHP with this:

    Code:
    <form action="searchHelper.php" method="post">
    <input name="input" type="text" id="input" size="" maxlength="">
    <input name="" type="submit" id="submit" size="" maxlength="">
    </form>
    The PHP in full is:

    Code:
    mysql_connect("localhost", "###", "N7MBBzTl#") or die() ; 
    
    mysql_select_db('z88s_test') or die('couldnt connect to db') ;
    
    $input = $_POST['input'];
    
    $input = mysql_real_escape_string(trim('input') );
    
    // this query returns the entire table meaning code works!
    //$sql = "SELECT * FROM beaches
    
    //This query returns nothing but should
    $sql = "SELECT * FROM beaches WHERE title LIKE '%".$input."%' OR address LIKE '%".$input."%'";
    
    $data = mysql_query($sql);
    
    	while($result = mysql_fetch_array($data)){
    		echo "<li>" . $result['title'] . "<br>";
    
    	}
    Last edited by Webenvelopment; 07-08-2012 at 02:48 AM.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    It'll seem daft, but are you sure you're input provides a match against either the title or the address? Even if input is nothing, using %% as your LIKE comparison results in all records anyway.

    So the query appears to check out, and the usage looks alright. Modify this:
    PHP Code:
    $data mysql_query($sql); 
    to this:
    PHP Code:
    $data mysql_query($sql) or die(mysql_error()); 
    Does that show any errors?

    Edit:
    Ahaha, founds it lol.
    PHP Code:
    $input mysql_real_escape_string(trim('input') ); 
    If I had to bet, you don't have a match for the string %input% within either the title or the address? That should probably be:
    PHP Code:
    $input mysql_real_escape_string(trim($input)); 
    Oh, and while we're at it, may as well fix the notice on this one:
    PHP Code:
    $input = isset($_POST['input']) ? $_POST['input'] : ''

  • #5
    New Coder
    Join Date
    Jul 2012
    Location
    Boston
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hey thanks again,
    Yeah the DB is FULL of data to match and whatever is typed doesn't matter. Everything is still the same and I get blank results w no errors with

    Code:
    $data = mysql_query($sql) or die(mysql_error());
    It's def
    Code:
     WHERE title LIKE '%".$input."%' OR address LIKE '%".$input."%'
    I get the full table without that. Is '%".$input."%' really how you get POST data into your query? I don't get why this works without a hitch for the guy in the tut? This is going on day 8 for me on this.
    Thanks for the moral support at this point haha
    -Ben

    MAJOR DEVELOPMENT:
    Firebug tells me:

    PHP Code:
    A form was submitted in the windows-1252 encoding which cannot encode all Unicode charactersso user input may get corruptedTo avoid this problemthe page should be changed so that the form is submitted in the UTF-8 encoding either by changing the encoding of the page itself to UTF-or by specifying accept-charset=utf-8 on the form element
    Last edited by Webenvelopment; 07-09-2012 at 01:25 AM.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Did you verify the edit I made with this:
    PHP Code:
    $input mysql_real_escape_string(trim('input') ); 
    ?

    $input is literally the string "input" with the above, so the matches you get are WHERE title LIKE '%input%' OR address LIKE '%input%'. Swap that $input assignment to take $input within the trim instead of 'input'.

    You can specify UTF8 if you desire for a characterset. It will only have value if the DB is in a unicode charset itself (default its latin), and you are storing data within it that qualifies as a unicode charset.

  • #7
    New Coder
    Join Date
    Jul 2012
    Location
    Boston
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Unicode

    Whoa. Thanks for all this help!
    Yeah I've learned a valuable lesson with this. And that is a great idea with getting the value from 'trim'. It would then be in its charset?

    Also, is this correct in how to check TWO columns for matches? Can't get it to check the "address" column for results.

    PHP Code:
    SELECT FROM beaches WHERE (titleaddressLIKE '%".$input."%' 

    Thanks!
    Last edited by Webenvelopment; 07-09-2012 at 02:54 PM.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •