Jump to content


Photo

$_POST form variable with a LIKE statement?


  • Please log in to reply
15 replies to this topic

#1 bigkev1983

bigkev1983
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 25 September 2006 - 11:06 AM

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

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 September 2006 - 11:25 AM

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

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

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 bigkev1983

bigkev1983
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 25 September 2006 - 11:43 AM

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

#4 trq

trq
  • Staff Alumni
  • Advanced Member
  • 31,041 posts

Posted 25 September 2006 - 11:50 AM

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

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



#5 bigkev1983

bigkev1983
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 25 September 2006 - 02:25 PM

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

#6 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 September 2006 - 02:28 PM

Why are you using sprintf()?

Why not just have...

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


Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#7 bigkev1983

bigkev1983
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 25 September 2006 - 03:01 PM

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!

#8 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 25 September 2006 - 03:24 PM

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

Ken

#9 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 September 2006 - 03:27 PM

Ken,

In this instance would the following work?

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


Or does SQL not like the curley braces?

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#10 mewhocorrupts

mewhocorrupts
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts
  • LocationParker, CO

Posted 25 September 2006 - 03:32 PM

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


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

#11 bigkev1983

bigkev1983
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 25 September 2006 - 07:33 PM

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

#12 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 September 2006 - 07:35 PM

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

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#13 bigkev1983

bigkev1983
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 25 September 2006 - 08:25 PM

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?

???

#14 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 25 September 2006 - 10:54 PM

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

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

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#15 bigkev1983

bigkev1983
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 26 September 2006 - 07:33 AM

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

#16 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 26 September 2006 - 08:45 AM

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?


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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users