Like operator in Mysql

[olinroger]

Hello,

 

I want to put a simple text search function on my website for people to enter address (street, city, state or zip code) to search data in mysql. There are same fields/columns such as street, city, state and zip code in mysql table.

 

My question is: if people enter a city and state at the same time, how do I compare what people entered with two or three columns such as city and state together? I tired something like "(city+state) like '%$whateverPeopleEnter%'" but it does not work.

 

Any suggestion or sample code to share with? I am new to php and mysql.

 

Thanks.

[EdwinPaul]

Something like:

<?php
$query = "SELECT city, state FROM your_table";
if($entered_city != ''){
   $query .= " WHERE city LIKE '%".mysql_real_escape_string($entered_city)."%'";
   if($entered_state !=''){
      $query .= " AND state LIKE '%".mysql_real_escape_string($entered_state)."%'";   // you also may try OR instead of AND
   }
}else{
   if($entered_state !=''){
      $query .= " WHERE state LIKE '% ".mysql_real_escape_string($entered_state)."%'";
   }
}
$result = mysql_query($query,$link_id);
if(!$result){
   echo 'Nothing found';
}
?>

 

 

[EdwinPaul]

Sorry, I made a typo :-\

My 10th line should read:

      $query .= " WHERE state LIKE '%".mysql_real_escape_string($entered_state)."%'";

[WebStyles]

<?php
$query = "select * from `addresses` where ";
if( isset($_REQUEST['street']) && trim($_REQUEST['street'] != '') ){
   $query .= "`street` like '%".$_REQUEST['street']."%'";
   $addAnd = 1;
}
if( isset($_REQUEST['city']) && trim($_REQUEST['city'] != '') ){
if(isset($addAnd) && $addAnd == 1) $query .= ' and ';
   $query .= "`city` LIKE '%".$_REQUEST['city']."%'";
   $addAnd = 1;
}
if( isset($_REQUEST['state']) && trim($_REQUEST['state'] != '') ){
if(isset($addAnd) && $addAnd == 1) $query .= ' and ';
   $query .= "`state` LIKE '%".$_REQUEST['state']."%'";
   $addAnd = 1;
}
if( isset($_REQUEST['zip']) && trim($_REQUEST['zip'] != '') ){
if(isset($addAnd) && $addAnd == 1) $query .= ' and ';
   $query .= "`zip` LIKE '%".$_REQUEST['zip']."%'";
}
$query .= ' order by `street`,`city` limit 50';
echo $query;
?>

[EdwinPaul]

@WebStyles:

- If you use '*' for ALL the fields, you may cause too much I/O. Make it a habit to mention only the fields you need.

- Your 'WHERE' is unconditional. If nothing is entered, your query will be invalid.

- The use of backticks (`) is stongly disadvised. It may cause confusion or disasters when using reserved words as fieldnames. If you for instance use a fieldname of 'delete' (which is possible with backticks) the next query may destroy your table if you accidentally forget the backticks:

SELECT delete FROM tabel;

[WebStyles]

@EdwinPaul

 

My code is OBVIOUSLY not complete, it's an example of how olinroger can construct a query_string with the options he needs. (which was the initial question). The rest of the code is up to him, as I am here to try and help and point them in a direction where they can solve their problem, and not do everyone's homework for them.

 

(The use of back ticks or not is irrelevant here, and also a personal preference. You say it's dis-advised, I disagree.)

 

[EdwinPaul]

Ok, you're right. I was somewhat over-enthousiastic in making his code for him.

Nevertheless, the WHERE may cause an invalid query.. 

 

[Pikachu2000]

@EdwinPaul

 

My code is OBVIOUSLY not complete, it's an example of how olinroger can construct a query_string with the options he needs. (which was the initial question). The rest of the code is up to him, as I am here to try and help and point them in a direction where they can solve their problem, and not do everyone's homework for them.

 

(The use of back ticks or not is irrelevant here, and also a personal preference. You say it's dis-advised, I disagree.)

 

 

But since you didn't indicate that in your post, it's very likely that someone who is new to php and MySQL syntax, as the OP indicated he is, wouldn't know that the code is only an example.

[WebStyles]

ok, I'll clarify this for everyone to be happy:

 

@olinroger

The code I posted allows you to build a query_string based on the options selected in the form and it will echo the results, so that you can tweak it to your likings, check the results, and when you're happy with them you can implement it into your code and actually pull out the data from your database with mysql_fetch_assoc or mysql_fetch_array.

 

Please notice I used backticks for the table and field names, this seems to annoy other programmers, so I suggest you google it and figure out if you should use them or not. I also did not account for the fact that people might perform an empty search, since that was not the question and I have no idea how you're posting the fields. (i.e. if you're doing it with ajax, maybe you're already checking if it's a valid search or not before it even reaches this part)

 

Hope it was helpful.

[olinroger]

Guys,

 

Thanks for replying. maybe I did not explain it clearly. The search is a text search, which mean people enter a phase into a text box. the phase could be street address, city name, state name or zip code, or any combination of these. My code was like:

 

  if($searchText) $address .= "and ((l.street like '%$searchText2%') or (l.city like '%$searchText2%') or (l.state like '%$searchText2%') or ( (l.city + l.state) like '%$searchText2%') or ( CONCAT('l.city','l.state') like '%$searchText2%')or (l.zip like '%$searchText2%'))";

 

If the text entered is a street, it seems I could get all the addresses with the street, if the text entered is a city, what I got was all the cities and also the addresses that has a same word in their street name as the city; and, if the text entered is a city and state, the search result will show zero result (which is correct), and if I entered is street, city, and state, it will show zero result, even if there is one in the database.

 

So I was wondering where there is a way to let the text phase entered to check with the combination of all street, city, state and zip?

 

 

[Andy-H]

 

$query  = "SELECT field1, field2 FROM table WHERE ";
$search = isset($_POST['fieldname']) ? mysql_real_escape_string($_POST['fieldname']) : '';
$fields = array('field1','field2','field3');
$query .= implode($fields, " LIKE '$search%' OR ");


if ( !empty($search) )
{
   $result = mysql_query($query);
   //rest of code i.e. mysql_num_rows / mysql_fetch_* etc.
}