PHP实现MySql语句

I have 2 tables

test_pattern with column:

  • id,url,creator

test_map with column

  • id, url
  • example data
    • id:1, url:"www.abc/"
    • id:2, url:"www.abc/?a=b"
    • id:3, url:"www.abc/testing"

I have a website that retrieve a list of urls from test_map, and user are able to choose a url to insert into test_pattern

  • Now, when a user select a url to add it should check with test_map and add the relevant url with querystings
  • Example, when select "www.abc/" it should add "www.abc/" and "www.abc/?a=b".
  • Lastly, if url already exist in test_pattern, it shouldn't be added

I'd came our with a working sql statement:

SELECT * FROM (SELECT NULL as id, url as url, "Ronny" as creator 
from test_map where url LIKE '%www.abc/?%') as tmp WHERE NOT EXISTS (SELECT pattern FROM test_pattern WHERE pattern LIKE '%www.abc/?%');

However i don't know how to implement it in php, this is what i tried:

$sql = array(
sprintf('INSERT INTO %s SELECT * FROM (SELECT NULL as id, url as url, %s as creator FROM test_map WHERE url LIKE '%%s%') 
AS tmp', "'".$creator."'", "'".$url."?'".
' WHERE NOT EXISTS (SELECT * FROM test_pattern WHERE url LIKE '%'.sprintf('%s',"'".$url."?'").'%') LIMIT 1'
);

Please assist on php implementation of the sql statement. Thanks

Set URL field in test_pattern as unique and then just do

 WHERE url LIKE '$url%'

No need to test for the value if exists in test_pattern

I have found the issue, we can't use %s directly in LIKE like this %%s%.

To past value in between %%, we need to do this %%%s%%