POST and sql "LIKE" problems.

[Webenvelopment]

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.

$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.

[Fou-Lu]

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.

[Webenvelopment]

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:

<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:

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>";

	}

[Fou-Lu]

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'] : ''; 


[Webenvelopment]

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

$data = mysql_query($sql) or die(mysql_error());

It's def

 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 characters, so user input may get corrupted. To avoid this problem, the 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-8 or by specifying accept-charset=utf-8 on the form element. 


[Fou-Lu]

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.

[Webenvelopment]

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 (title, address) LIKE '%".$input."%' 


Thanks!