I tried to googling it but not yet found exactly with mine.
So in DB there is a record "Moon Light". I want to check if user try to input "PX Moon Light" or "Moon Light Corp", it must be rejected because it is possible similar company with existing record.
What I've tried one by one (found from another thread) are:
$name = $_POST['name']; //in this case user typed 'PX Moon Light';
$sql = "SELECT name FROM tbl_company WHERE name REGEXP '$^$name' LIMIT 1";
$sql = "SELECT name FROM tbl_company WHERE name REGEXP '$^$name^$' LIMIT 1";
$sql = "SELECT name FROM tbl_company WHERE name REGEXP '^$name^' LIMIT 1";
$sql = "SELECT name FROM tbl_company WHERE name REGEXP '$name' LIMIT 1";
$sql = "SELECT name FROM tbl_company WHERE name LIKE '%PX Moon Light%' LIMIT 1";
then
$res = mysqli_query($conn,$sql);
if(mysqli_num_rows($res)>0) {
echo 'rejected, possible similar data';
}
But all those SQL are successfully inserted into DB. Should be rejected.
Is there any MySQL method that can be used for it? Please advise. Thank you.
Looks like you just want to query the database for similar company name in the database as the input before proceeding with the insert. If that's the case, you can use the below mysql query
SELECT * FROM tbl_company where '<input name>' LIKE CONCAT('%',name,'%');
in your code it would be like
$sql = "SELECT name FROM tbl_company WHERE '$name' LIKE CONCAT('%',name,'%')";
Alternatively, you can also perform insert with condition check that the query does not contain Moon Light string using INSERT INTO SELECT. That way, you don't need to perform initial select query to check if the record exists in the database before using INSERT query
INSERT INTO tbl_company(name)
SELECT name from (SELECT 'PX Moon Light' as name) iv where iv.name not like '%Moon Light%';
INSERT INTO tbl_company(name)
SELECT name from (SELECT 'Moon Light Corp' as name) iv where iv.name not like '%Moon Light%';