Help-Mysql Select query "like'$variable'"

PHP
1

Hello All: This is a first post from a Newbie so bear with me. I am wanting to display selected info from a Mysql database with a “SELECT columns WHERE column LIKE’$variable’” query only I am unable to get ‘$variable’ to work with the LIKE statement. Specificaly I want to pull the name of all towns from the database based on a passed $variable from a web form where the town name is LIKE ‘ash%’ as an example for Ashby, Ashton etc.

Any and all help appreciated. Thanks to Kevin Yank for information and chapters related to his new book on PHP and Mysql. They got me off to a good start!

2

Posting some source code would help us figure out whats wrong.

Here is what I would do:


$name = 'ash';
$sql = "SELECT columns WHERE column LIKE '".$name."%'";
$result = mysql_query($sql);
1 Like
3

Code is very straight forward:

<?php

$place = $_POST[‘place’]; Where ‘place’ is passed in from web form (a Town name )

$result = SELECT Name_place, Name, Location FROM Table Where Name_place LIKE ‘$place’;

// Code for printing output from SELECT query

?>

The problem arises trying to use a variable with the LIKE portion of the SELECT Query. If I hard code a test example such as " LIKE’ash%’ " I receive the expected results from my database ie. Town names like Ashby, Ashcroft,etc. So I know the code works and also that the parameter is being passed as I test printed ‘$place’ at the top of the page.

How do I get a SELECT Query to output the needed town names dynamically based on the passed $variable from the web form on previous page?:confused:

Thanks-All help appreciated.

4

Well, first off, you need to have quotes around that MySQL statment.

Also, you will need to have a % following the variable.

Try this:


$place = $_POST['place'];

$result = "SELECT Name_place, Name, Location FROM Table Where Name_place LIKE '".$place."%'";
5

Cyngon-forgive my sloppy coding example as I typed from memory and did not cut and paste.The lack of quotes and % sign are not the problem. My code works when I hard code as in ‘ash%’. I need to replace between the single quotes for LIKE with a dynamic variable passed from the previous page for multiple instances of the requested text string.

Is there a way to make a SELECT Query that will accept the passed variable so I can retrieve multiple table entries for a given text string? Perhaps my using the LIKE portion of the query is not the proper way to attack the problem?

Any suggestions appreciated.

6

The code I posted above will work for what you described. It will return any rows in which the Name_place column starts with the text in $_POST[‘place’].

If you want to make an SQL query that returns any rows in which one column is equal to a variable you don’t need to use LIKE.

You could simply do:


$place = $_POST['place'];

$result = "SELECT Name_place, Name, Location FROM Table WHERE Name_place='".$place."'";

LIKE is only necissary for matching rows which are not exactly the same as the variable, but contain the value of the variable somewhere in the string.

7

cyngon:-Thanks for the suggestions, i’ll give them a try.

I was having trouble at this end reviewing your posts. The replies seemed to be cut off just at your suggestions until I looked at answering via “Quote” which gave me the complete text of your message.

Thanks again!

Originally posted by cyngon
[B]The code I posted above will work for what you described. It will return any rows in which the Name_place column starts with the text in $_POST[‘place’].

If you want to make an SQL query that returns any rows in which one column is equal to a variable you don’t need to use LIKE.

You could simply do:


$place = $_POST['place'];

$result = "SELECT Name_place, Name, Location FROM Table WHERE Name_place='".$place."'";

LIKE is only necissary for matching rows which are not exactly the same as the variable, but contain the value of the variable somewhere in the string. [/B]
:smiley:

8

The percent sign (%) is not necessary. It is only needed to denote wildcard searches. If you want exact searches using LIKE then you wouldn’t use it. And while you don’t need to use LIKE in these cases on MySQL, there are instances where it is very handy to use it in dynamically built query statements especially if you are building a search engine.

9

I thought LIKE was generally only used for non-exact matches.

For exact matches, woulden’t one just do “WHERE column=‘value’”?

10

Sure if you want to write each and every query out and have them hardcoded in your code. However if you want to build queries programmatically and reuse said query for different uses then no, you would want maximum flexibility.

11

You could still generate an exact match query (without using LIKE) programatically:

$sql = "SELECT * FROM table WHERE column='$variable'";

Is there a reason using LIKE would be better in the above query?

Or are we talking about totally different things? :wink:

12

javascript:smilie(‘:D’)cyngon- That worked!

One more question- In the code snippet ‘“.$place.”%’"; the leading and trailing periods around ‘$place’-are they to
concatinate?

Originally posted by coho4
[B]cyngon:-Thanks for the suggestions, i’ll give them a try.

I was having trouble at this end reviewing your posts. The replies seemed to be cut off just at your suggestions until I looked at answering via “Quote” which gave me the complete text of your message.

Thanks again!

:smiley: [/B]

13

Yes. In PHP periods do string contantenation (sp?).

Glad I could help. Be sure to post if you need any more assistance.

14

Take this query here:


        $sqlwords=$DB_site->query("SELECT wordid,title FROM word WHERE title LIKE '".addslashes($val)."'");

This is from the search engine used here in these very forums.

Now… If I wanted to search for say, fread, it would use that query and perform an exact match on the criteria. However what if I didn’t know the criteria for exactly so I put in read as my criteria. The code would then expand that to %read% and it would work in the same query. However if I had used a simple compare using equals (=), I would need two queries.

Depending on how your code was written and how your search engine performs, using equals could double the number of queries you used. One for every exact match and one for every wildcard match. And this doesn’t even get into boolean searches or multiple words.

15

Ah, I understand what you mean now.

Thanks for the explanation, Wayne.

16

Friends, I’m having a hard time with a similar search. It should be very simple but I can’t figure out what’s wrong. Here’s the scenario:

I have a form field (call it ‘keyword’) where people can search by author’s last and first names, of book title. If they enter only one piece of information (first name or last name, or title, etc) it works fine. However, if anyone enters 2 pieces of info, my search doesn’t find anything.

ex: “nichollas Allan” returns no matches. Here’s my code

(autor.autornombre LIKE '$clave%' OR autor.autorapellido LIKE '$clave%' OR
ilustrador.ilustradorNombre LIKE '$clave%' OR ilustrador.IlustradorApellido LIKE '$clave%' OR
principal.Title LIKE '%$clave%')

I realize that code searches only for one match or another, so if the 2 terms they ener don’t match first_name (autornombre) it won’t find anything. So I added this:

((autor.autornombre LIKE '%$clave%' OR autor.autorapellido LIKE '%$clave%') OR

at the beginning of the code so that the first thing it does is look for a match in either field.

This doesn’t work either. I know I’ve got a problem in the code since it looks for a match for first name of “Nicholas Allan” (in the example above) and won’t find it since the first name is only “Nicholas”, but don’t know how to go about getting the results I want. Any help would be REALLY appreciated.

Thanks.

17