problems with search

[jerrylouise]

hi all i got a search script but it dosnt work when i use a variable
this is it when it works (tn is random but returns expected results)

$post = $_POST['search_box'];
$sql = "SELECT `id`, `to`, `from`, `subject`, `body`, `date`, `new` FROM `privatemail` WHERE `from` LIKE CONVERT(_utf8 \'%tn%\' USING latin1) COLLATE latin1_swedish_ci";
$result = mysql_query($sql) or die("Query failed");

here using $post witch prints out the with echo fine but dosnt work inside the search results for sql.

$post = $_POST['search_box'];
$sql = "SELECT `id`, `to`, `from`, `subject`, `body`, `date`, `new` FROM `privatemail` WHERE `from` LIKE CONVERT(_utf8 \'%$post%\' USING latin1) COLLATE latin1_swedish_ci";
$result = mysql_query($sql) or die("Query failed");

Another thing i would like to know is if i also wanted the search to do more than just search one thing how would i do it?

[bpat1434]

In the die() statement, just call [man]mysql_error/man and that will give you the exact problem MySQL has with your query 😉 Could be that the $post variable has a double-quote which isn't escaped, or some other weird stuff. Could also be something completely different.

As for searching multiple columns, use the "OR" keyword:

SELECT * FROM `table`
WHERE `column` LIKE '%tn%'
  OR `column` LIKE '%md%'
  OR `column` LIKE '%ak%'
  OR `column2` LIKE '%tn%`

You can have as many OR and AND statements as you need/want. You can also use parenthesis to group statements together.