search not returning query-CodePudding

Sorry my english. I'm trying to search mysql database that contains hashtags, but it returns all.

eg Search #NBA returns: #NBA, #NBA2021, #NBAscoreBoard, etc

I've tried every preg_replace on here. eg #(^|\s)#(\w*[a-zA-Z_] \w*)# But how do I break after the specific search is met?

$_GET["tag"]='#nba'; // $_GET is from a  query string

$fulltag = preg_replace("/[^a-zA-Z0-9_]/", '', $_GET["tag"]); //trying to isolate this problem 

 $sql='SELECT * FROM status WHERE data LIKE "%#'.$fulltag.'%" LIMIT 12';
    // 
    //
    //
echo //the result containing the "# $fulltag";

CodePudding user response：

As I said in my comment--just doing two queries with one to test if data = fullTag, and then if that returns nothing, then doing the wildcard search--is probably going to be simpler.

However, if you really want this to be a single query, you could do something like this, wherein you test to see if it is an exact match within a sub-query, then order by whether it's an exact match so that if there is an exact match, it will be the first result.

SELECT * 
FROM (
SELECT 
  data,
  CASE 
    WHEN data = "#NBA"
      THEN 1
    ELSE 0
   END AS is_exact
FROM status 
WHERE data LIKE "%#NBA%"
LIMIT 12
) AS matches
ORDER BY is_exact DESC

A separate note: You code right now is very vulnerable to SQL Injection. You should try using parameterized prepared statements instead of manually building your queries. See PDO or MySQLi prepared statements.

Here is an example of the above query using PDO, and this method of using CONCAT to safely add wildcards to your query:

$_GET["tag"]='#nba'; // $_GET is from a  query string

$fulltag = preg_replace("/[^a-zA-Z0-9_]/", '', $_GET["tag"]); //trying to isolate this problem 

$pdo = new PDO(/* your connection details */);

 $sql = 
    'SELECT * 
    FROM (
        SELECT 
            data,
            CASE 
                WHEN data = CONCAT("#", :tag1)
                THEN 1
                ELSE 0
            END AS is_exact
        FROM status 
        WHERE data LIKE CONCAT("%#", :tag2, "%")
        LIMIT 12
    ) AS matches
    ORDER BY is_exact DESC
';

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':tag1' => $fulltag,
    ':tag2' => $fulltag,
]);

Here's your simpler query using the same, safer approach:

$_GET["tag"]='#nba'; // $_GET is from a  query string

$fulltag = preg_replace("/[^a-zA-Z0-9_]/", '', $_GET["tag"]); //trying to isolate this problem 

$pdo = new PDO(/* your connection details */);

$sql = 'SELECT * FROM status WHERE data LIKE CONCAT("%#", :fullTag, "%") LIMIT 12';

$stmt = $pdo->prepare($sql);
$stmt->execute([':fullTag' => $fulltag]);