$_POST form variable with a LIKE statement?

[bigkev1983]

Hello there

I'm pretty new to PHP and mianly use Dreamweaver to come up with most of the code for search interfaces to be honest so I'm hoping you guys can help me.

I have this select statement for my search results page:
$query_directory = sprintf("SELECT * FROM directory WHERE Name LIKE '%".$_POST['name']."%' ORDER BY id DESC", $colname_directory);

to get all records from the database whose name contains characters typed in by the user from the search form.

However, it doesn't work and SQL just comes back with an error so I think the syntax must be wrong. The standard statement (below) DOES return results but obviously it doesn't do what I need it to as it forces the user to type in the whole name exactly:

$query_directory = sprintf("SELECT * FROM directory WHERE Name = '".$_POST['name']."' ORDER BY id DESC", $colname_directory);

Can anyone help me make the first statement work?

Kev

[HuggieBear]

Doesn't % have special meaning in sprintf().

[size=8pt][b]Try this:[/b][/size]
[code=php:0]
$query_directory = sprintf("SELECT * FROM directory WHERE Name LIKE '\%".$_POST['name']."\%' ORDER BY id DESC", $colname_directory);
[/code]

Regards
Huggie

[bigkev1983]

Just tried that but still get the same error  ???

[trq]

What is $colname_directory? sprintf needs a place holder, so your code should look something like.

[code=php:0]
$colname_directory = $_POST['name'];
$query_directory = sprintf('SELECT * FROM directory WHERE Name LIKE "%'."%s".'%" ORDER BY id DESC', $colname_directory);
[/code]

[bigkev1983]

Note sure what that means but I get thie following error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '"%s ORDER BY id DESC LIMIT 0, 10' at line 1

???

Here's the code I just used...

mysql_select_db($database_directory, $directory);
$colname_directory = $_POST['name'];
$query_directory = sprintf('SELECT * FROM directory WHERE Name LIKE "%'."%s".'%" ORDER BY id DESC', $colname_directory);

$query_limit_directory = sprintf("%s LIMIT %d, %d", $query_directory, $startRow_directory, $maxRows_directory);
$directory = mysql_query($query_limit_directory, $directory) or die(mysql_error());
$row_directory = mysql_fetch_assoc($directory);

[HuggieBear]

Why are you using sprintf()?

Why not just have...

[code=php:0]$query_directory = "SELECT * FROM directory WHERE Name LIKE '%$_POST['name']%' ORDER BY id DESC";[/code]


Regards
Huggie

[bigkev1983]

Hi Huggie - I tried this code...

mysql_select_db($database_directory, $directory);
$query_directory = "SELECT * FROM directory WHERE Name LIKE '%$_POST['name']%' ORDER BY id DESC";

$query_limit_directory = sprintf("%s LIMIT %d, %d", $query_directory, $startRow_directory, $maxRows_directory);
$directory = mysql_query($query_limit_directory, $directory) or die(mysql_error());
$row_directory = mysql_fetch_assoc($directory)

But got the following error:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /homepages/16/d93745840/htdocs/m18directory/results.php on line 13

I also tried it without the sprintf (and brackets) in the $query_limit_directory line but same error.

Any ideas? I thought it wouldn't be too difficult to have a results page displaying all results from a table where the name field is like what the user has typed in the form but it's proving tricky!

[kenrbnsn]

Change the line suggested by HuggieBear to:
[code]<?php $query_directory = "SELECT * FROM directory WHERE Name LIKE '%" . $_POST['name'] . "%' ORDER BY id DESC"; ?>[/code]

Ken

[HuggieBear]

Ken,

In this instance would the following work?

[code=php:0]
$query_directory = "SELECT * FROM directory WHERE Name LIKE '%{$_POST['name']}%' ORDER BY id DESC";[/code]


Or does SQL not like the curley braces?

Regards
Huggie

[mewhocorrupts]

[quote author=bigkev1983 link=topic=109390.msg440974#msg440974 date=1159196465]
$query_directory = "SELECT * FROM directory WHERE Name LIKE '%$_POST['name']%' ORDER BY id DESC";
[/quote]

That's your problem.

You can't reference a associative array, such as $_POST, within the quotes of an expression.  It should look more like this:

<code>
<?php
...
$q = "SELECT * FROM `directory` WHERE `Name` LIKE '%" . $_POST['name'] . "%' ORDER BY id DESC;";
...
?>
</code>

I'm pretty sure that that is your problem.  Let me know if it helps.  Oh, and if you want to use "%" in a sprintf(), you need to backslash it, like sprintf("something something percent sign = \% and again \%");, otherwise it takes it as a sign that your going to drop a variable into it, and its looking for an type identifier.

[bigkev1983]

Thanks Huggie - you're a star. I tried your statament and it worked first time.

Kev

P.S. - If I want to get results from two different form inputs can I just do this?....

$query_directory = "SELECT * FROM directory WHERE Name LIKE '%{$_POST['name']}%' OR WHERE Keywords LIKE '%{$_POST['keywords']}%' ORDER BY name ASC";

[HuggieBear]

Yes you can, and there's no need to put the ASC bit on the end.  just [color=blue]ORDER BY name[/color] will be fine as the default method is ascending.

Regards
Huggie

[bigkev1983]

I don't belive it!
Just tried it but got the following message...

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Keywords LIKE '%%' ORDER BY name ASC LIMIT 0, 10' at line

Any ideas?

???

[HuggieBear]

OK, you have an extra WHERE in there instead, when using AND or OR, they replace WHERE.  Try this:

[code=php:0]$query_directory = "SELECT * FROM directory WHERE Name LIKE '%{$_POST['name']}%' OR Keywords LIKE '%{$_POST['keywords']}%' ORDER BY name";
[/code]

Regards
Huggie

[bigkev1983]

Huggie my man - you are a genius!

Just one last thing - if one of the fields i blank, it reeturns all results no matter what. Is there any way I can tell it to ignore a field if it's blank? Here's what I have now...

$query_directory = "SELECT * FROM directory WHERE Name LIKE '%{$_POST['name']}%' OR Keywords LIKE '%{$_POST['keywords']}%' ORDER BY name";

[HuggieBear]

[quote]
Just one last thing - if one of the fields is blank, it returns all results no matter what. Is there any way I can tell it to ignore a field if it's blank?
[/quote]

You mean if one of the submitted form fields is blank, or one of the database fields?

Can you also tell me what column is your primary key in your directory table?

Regards
Huggie