CODEWITHSUNDEEP
phpmysqlsql-like
Shabarish Shetty
Shabarish Shetty

Reputation: 132

SQL: LIKE Clause to match the values

I have a mobile validation field where it gives a message stating that the values exists if we type the same number/values which is in the database else available if the value doesn't exists

Here is the query which am using at present :- 

SELECT mobile FROM candidate_tabletest WHERE mobile  LIKE '%' '$mobile' '%'

The above query is not working in some cases . For example if we have number 123456789 in database and when we type inside mobile field with code or any other format like +91-123456789 it still says that mobile number is available .

For reference here is my HTML code for mobile field :-

<input type="text" class="form-control" id="mobile" name="mobile"    placeholder="mobile" value="+91" onkeyup="checkmobile();" >

PHP code for checking:- 

  <?php

   $host = 'localhost';
   $user = 'root';
   $pass = '';

   mysql_connect($host, $user, $pass);

   mysql_select_db('appsrx1a_skyfler');

  if(isset($_POST['user_mobile']))
  {
   $mobile=$_POST['user_mobile'];

   $checkdata=" SELECT mobile FROM candidate_tabletest WHERE mobile  LIKE      '$mobile' '%' ";

    $query=mysql_query($checkdata);

   if(mysql_num_rows($query)>0)
  {
   echo '<span style="color:red;"><b>Mobile  Already Exists</b></span>';
  }
  else
    {
   echo '<span style="color:green;"><b> Available</b></span>';
     } 
 exit();
 }

If the number (123456789) is present in the database and even if the user types the mobile number as +91-123456789 or any other format . It should give a warning message stating that the number already exists .

Upvotes: 1

Views: 90

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You should use concat .. for build a proper string pattern

 SELECT mobile FROM candidate_tabletest WHERE mobile  LIKE concat( '%', '$mobile', '%' );

and as suggested by Gordon Linoff remmber to replace offending char

 SELECT mobile 
 FROM candidate_tabletest 
 WHERE mobile  LIKE concat( '%', REPLACE(REPLACE('$mobile', '-', '') , '+', ''), '%' );

If you need only for th right part of the mobile number then you could use 

 SELECT mobile 
 FROM candidate_tabletest 
 WHERE mobile  LIKE concat( '%', SUBSTRING_INDEX('$mobile', '-', -1), '%' );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method would be to remove offending characters:

SELECT mobile
FROM candidate_tabletest
WHERE mobile LIKE REPLACE(REPLACE('%'.'$mobile'.'%', '-', '') , '+', '')

You could do this in the application layer as well, so $mobile only contains digits (which I presume is the format of the mobile column).

Upvotes: 1

Related Questions