1

Context

I'm creating a comment system where users can use the "@" symbol to mention other users' usernames. I want to be able to go into the database and search through the comments table so that comments that include "@_____" (insert username) are given. However, it should only contain that username in the word; if there's a username randomusername and I search for randomusername in the query, I don't want names like randomusernameextended to show up because part of its string is what I queried. Additionally, there should be able to be more than just the username in the result, which is why using WHERE comment = "@____" doesn't work; it only returns rows that ONLY include that username. I want to return rows that simply contain the username in the message (example: "@____ random comment here" is returned when I query the username).

Question

How do I search a mySQL table for a specific row, one that includes the specific string I searched for but can also contain more text?

Things I Have Tried

I have already tried using the %LIKE% operator to return results that include the username string. The problem is that I only want the username to be in the one word I return and nothing more, but given the nature of the operator, I can't search for the username randomusername without getting randomusernameextended, as I mentioned.

Let me know if there is any confusion. Thanks.

Carson D
  • 81
  • 13
  • Have you ever use `LIKE 'a%' that finds any values that start with "a" this method is like a facebook when mentioning – Cyrille Con Morales Aug 18 '20 at 07:48
  • @CyrilleConMorales what if "a" is located in the middle of string – mangusta Aug 18 '20 at 07:54
  • Then it should be combined with two SQL queries that uses distinct to filter two values that found in two queries – Cyrille Con Morales Aug 18 '20 at 07:57
  • Note that there is the `REGEXP` keyword also in MySQL if you're familiar with regexes (I don't know which is slower with `LIKE`), it can be useful, as you username may be separated with something else than a space (dot, comma...) so that `LIKE '%@randomuser %'` won't find them – Kaddath Aug 18 '20 at 08:20
  • in other words, the query should handle three cases. 1. `pattern%` 2. `%pattern` 3. `%pattern%`. where special char includes spaces/tabs as well – mangusta Aug 18 '20 at 09:11
  • @Kaddath So you're saying that using `REGEXP` would allow me to consider the username along with any combination of punctuation? – Carson D Aug 18 '20 at 21:37
  • @CarsonD yes, exactly, I added an answer that can fit your case – Kaddath Aug 19 '20 at 09:13

2 Answers2

1

You can try with a regex pattern here, something like

WHERE comment REGEXP ".*@randomusername([[:>:]].*|$)"

regex explanation:

  • . means "any character" and * means "any number of times" (0 to unlimited times)
  • [[:>:]] a word ending, anything that is not a word character (A word character is an alphanumeric character in the alnum class or an underscore _) -> so this solution can lead to false positives if you allow underscores in your user names.
  • | means "or" and $ means "end of string" so that ([[:>:]].*|$) means "a word ending followed by any character any number of times OR end of the string"

You might need to adapt the regex following your needs, you have some explanations here

Kaddath
  • 5,933
  • 1
  • 9
  • 23
0

I think you are just on right way with patterns search

  SELECT * FROM pet WHERE name LIKE '%w%';

But need to fetch rows like this to select more details

 <?php
$result = mysql_query("SELECT id,email FROM people. 
WHERE id = '42'");
if (!$result) {
     echo 'Could not run query: ' . mysql_error();
    exit;
}
$row = mysql_fetch_row($result);

echo $row[0]; // 42
echo $row[1]; // the email value
?>

Try this special pattern format in your query to avoid problems with withe space etc.

(?<=\s|^)@([\w@]+)

Micha
  • 906
  • 6
  • 9
  • Thanks for the response. Are you saying the important part of this is the query? In that case, the query you provided is too specific; if I use `WHERE id='#'`, it doesn't include messages that simply mention that specific username. Do you know what I mean? – Carson D Aug 18 '20 at 21:32
  • It seemsca special problem with username query in php. See additional informations above. – Micha Aug 19 '20 at 06:18
  • https://stackoverflow.com/questions/13312202/query-for-php-mysql-and-or-for-an-advanced-search – Cyrille Con Morales Aug 19 '20 at 06:25