CODEWITHSUNDEEP
phpmysqlsqlexecutestatements
Oliver Tappin
Oliver Tappin

Reputation: 2541

MySQL query to search keywords ordered by relative match

I am creating an online system that matches bank statements (description, value, type, etc) to purchasers (names, addresses) and would like some feedback on the way I am currently doing this:

$array = array("keywords", "taken", "from", 
               "bank", "statements", "using", "explode", 
               "function", "using", "a", "space");

$i = 0;
$r = array(); //r = relevant

while($i<count($array)) {

  $keyword = $array[$i];

  $get = mysql_query("SELECT `id` FROM `users`, `addresses` 
                      LEFT JOIN `users`.`id` = `addresses`.`user` 
                      WHERE (`users`.`frame` LIKE '%$keyword%' 
                      OR `users`.`lname` LIKE '%$keyword%') 
                      OR ((`addresses`.`address` LIKE '%$keyword%' 
                      OR `addresses`.`town` LIKE '%$keyword%') 
                      OR (`addresses`.`county` LIKE '%$keyword%' 
                      OR `postcode`.`town` LIKE '%$keyword%'));");

  if(mysql_num_rows($get)) {
    while($fetch = mysql_fetch_array($get)) {
      list($var) = $fetch;
      push_array($r, $var);
    }
  }

  $i++;

}

//count the IDs that match within the relative array to see
//which is most relative to the search

Is there a better way of doing this as well as keeping the execute time to an absolute minimum? The database will be in the 10s of thousands when it's finished.

Upvotes: 0

Views: 1052

Answers (1)

Ja͢ck
Ja͢ck

Reputation: 173532

It would be better to build a keyword table that ties each keyword to a user, e.g.:

keywords (keyword, user) + UNIQUE(keyword, user)

keyword1   12
keyword1   14
keyword2   3

After you populate the keywords table from the data you wish to search on, the query becomes much more optimal:

SELECT users.* 
FROM keywords 
INNER JOIN users ON users.id = keywords.user
WHERE keyword IN ('keyword1', 'keyword2')

Of course, you need to maintain this table when you make changes to the user or address table (insert, update, delete).

Upvotes: 1

Related Questions