0

PDO, I want to use a search query. I tried to use like query, using % on both front and end of search variable. but it search in between the words.

like if in database there is name Jhon Anderson and if i use search variable using LIKE %erson%, The query will get me the row which contains the above name. But what i want is query should only search with starting alphabets so i tried the LIKE query with Jhon%, i got the desired result, but using this % in the end only works if search variable matches the starting of first word, but what about the word after the space?

I mean if i try Anderson% or Ander%, The query will not get me the result of this row as Anderson is the second word in the field Jhon Anderson after space.

So my question is how to search in the words after spaces, that if there starting alphabets match the search query.

I don't know if such kind of query exist or not, but i think it might be possible with some looping after using like query with wildcard '%'.$variable.'%' and after getting all the results unset the arrays or rows where search variable do not match with the first alphabet of the result.

Any ideas how to implement such kind of search.

Query right now i am using,

$stmt = $conn->prepare("SELECT ROOM, GUEST_NAME, GUEST_FIRST_NAME, CONFIRMATION_NO, DEPARTURE, PWD FROM RESERVATION_GENERAL_2 WHERE LOWER(GUEST_FIRST_NAME) LIKE ? OR LOWER(GUEST_NAME) LIKE ?");
$stmt->execute(array('%'.strtolower($searchFilter).'%','%'.strtolower($searchFilter).'%' ));
Sizzling Code
  • 5,932
  • 18
  • 81
  • 138

1 Answers1

1

A bit dirty, but simple solution is to include the space in the search:

WHERE ' ' || Name LIKE '% Anderson%'

You see I added a space in front of Name, so it will also find the word if it is at the beginning of the string.

Alternatively, you can use REGEXP_LIKE, but then it is still a bit awkward, since Oracle doesn't have a word boundary expression in its regex engine. For a solution, see this answer

Community
  • 1
  • 1
GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • thankyou for reply sir. But if there are three spaces, because the full name of a person is not certain. e-g `Rameez Hassan Mir`, then it will again not work? – Sizzling Code Sep 13 '14 at 09:32
  • I seem to have forgotten one of the `%` characters. I fixed that now. I think this should work fine for Mr Rameez as well: `WHERE ' ' || Name LIKE '% Hassan Mir%'` or `WHERE ' ' || Name LIKE '% Mir%'` should both find him. – GolezTrol Sep 13 '14 at 09:35
  • wow, it worked you are awesome.. it worked in mysql, i will try this in PDO also.. i hope it works there.. Many Many thanks.. – Sizzling Code Sep 13 '14 at 09:56
  • Nice. :) But.. MySQL? Your question's tag says 'oracle'. – GolezTrol Sep 13 '14 at 10:13
  • Yes, i wanted for oracle but i had only mysql installed on laptop that time thats why i said i will try on mysql.. Tried using Oracle DB. it worked. – Sizzling Code Sep 13 '14 at 14:16