0

I have a table with column "company_name". Now with some third party apps I receive short strings like this: Somebody has sent item to "stack-exchange".

I want to find row with stack-exchange company_name column from string. (In other words I want like operator to work the other way around). How to do this?

EDIT: How to check if any of the values of company_name in your table match part of the string I receive?

ewooycom
  • 2,651
  • 5
  • 30
  • 52

2 Answers2

0

Edit: Since you can't identify what part of the input string is the company name, you need to check your existing values for company_name in your table against the string. For example, in PHP:

$input = 'Somebody has sent item to "stack-exchange"';
$result = mysql_query('SELECT company_name FROM table');
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    if strpos($input, $row['company_name']) === False {
        print "Company name is not in this row!" /* Substitute whatever action you want here */
    } else {
        print "Company name is in this row!" /* Substitute whatever action you want here */
    }
}

Dealing with User Input

Make sure that the input is sanitized before including it as part of a SQL query. Otherwise you make your code vulnerable to hacking.

Unfortunately, since some of the strings you receive are inputted by users, you can't be sure that they'll match what you have in your database. For example, "stack-exchange" could reasonably be represented as "Stack Exchange", stack exchange, StackExchange, etc. So you'll need to standardize the input to match the way you store company names in your database. For example, you could make all characters lowercase and replace all spaces or punctuation with hyphens. This doesn't rule out edge cases like incorrect or variant spellings, but that's beyond the scope of this question.

If the third-party strings you receive reliably contain the company name in double quotation marks (and double quotation marks are not used to indicate anything else in those strings), you can retrieve the company name using PHP. Then you can use a SQL WHERE clause to get the relevant rows.

$input  = 'Somebody has sent item to "stack-exchange"';
$parts = explode('"', $input);
$company_name = $parts[1];
$sql_query = 'SELECT * FROM table WHERE company_name="' . $company_name . '"'

Community
  • 1
  • 1
ASGM
  • 11,051
  • 1
  • 32
  • 53
  • No unfortunately not. Before&after company name can be any character. – ewooycom Mar 02 '13 at 15:03
  • Are there any regular ways of identifying what part of the string contains the company name? If not, perhaps your question would be better asked in reverse: how to check if any of the values of company_name in your table match part of the string you receive. Is that what you're looking for? – ASGM Mar 02 '13 at 15:06
  • This is exactly what I am looking for. There is no way to get part of the string in which is company_name -> some of third party apps even send me some user generated strings. – ewooycom Mar 02 '13 at 15:07
0

I think you need to explain the question better, since I can interpret this in at least 2 ways: i) The string provided in the email is a partial match with some data in a column called company-name: Use the like expression either way around, adding % as required eg, '%'+companyname+'%' like emailstring [which will find 'stack-exchange company' from 'stack-exchange provided in the email]

ii) There are multiple columns, each named after a company... eg, id, company1, company2, stack-exchange-company, company4

If your db supports dynamic sql (eg, most flavours of SQLServer) you can compute the sql then use set @sqlexpr = 'select stack-exchange from mytable' exec(@sqlexp)

Otherwise, you'll need to dynamically create the sql prior to calling the db.

simon coleman
  • 349
  • 2
  • 5
  • Check my edit. Basically I have table companies, with list of companies. Then I get some text and have to identify about what company we are talking about. – ewooycom Mar 02 '13 at 15:12