Datamapper comparing strings SQL and PHP

Question

I'm trying to compare two strings using CodeIgniter and Data Mapper with PHP and MySQL, I found the function similar_text for PHP and using it in this function:

$v = new Vendedor();
$v -> get();
foreach ($v as $vv) {
    $n = $vv -> name . " " . $vv -> lasta_name1 . " " . $vv -> last_name2;
    $vv -> sorteo -> get();
    similar_text($n, $name, $sim);
    if ($sim > 20) {
        $vv -> boleto -> get();
        $ven[$i] = array(
            'id' => $vv -> id, 
            'nombre' => $n, 
            'telefono' => $vv -> phone, 
            'mail' => $vv -> email,  
        );
        $i++;
    }
}

return $ven;

Do somebody the equivalent of similar_text for Data Mapper? Thank you so much!

How do you mean "for Data Mapper"? You mean something that would return similar results from the database directly (so not really for datamapper but for sql then)? What database system are you using? — complex857, Apr 17 '13 at 10:04
for default code igniter works with active record, but my boss doen't like it, we are using datamapper and some things are very different. I'm fighting with the pagination. — Laura, Apr 17 '13 at 18:54
Both CodeIgniter's active record lib and DataMapper are ultimately just build sql queries at the end. Do you have some non DM using code to be ported? The code sample in your question would look similar if built with plain CI too. The `similar_text()` is just a built in php method so its not "for" either DM or AR. Maybe its my English but i just don't really understand what's your question (-: Maybe you could write down the code that you wish to exists in DM and explain what it should do (sort of a wishful-thinking-driven-development). — complex857, Apr 17 '13 at 19:06
Sorry, it's my english, lack of practice. I'm using object to get de info from the database, usin similar_text to compare names, and saving the coincidences in an array. But my pagination is not working with arrays, how can I compare the names and save them in DM? — Laura, Apr 18 '13 at 19:14

score 0 · Answer 1 · edited May 23 '17 at 12:04

The algorithm implemented in PHP's similar_text() function is not available in MySQL. There is soundex in mysql that could be used with an expression like:

... where name sounds like "some input"

in sql, so in your PHP code this would look like:

$v = new Vendedor();
$v->where('CONCAT(name, " ", lasta_name1, " ", lasta_name2) sounds like ', $name);
// ...

but soundex is doesn't really work with non english text. You can try various implementation of levenshtein too, see this or this.

Both of these will still have pretty bad performance since the database still have to compare every row so you don't really win much on that front but you change the algorithm of similarity and that might not be ok.

If you decide to keep the similar_text() function you can implement the pagination on the array with an array_slice() preferably only creating the order once and saving the information in some cache (memcached, plain files, apc cache...) so the subsequent calls with this input can be served without recalculating the order thus being faster. I imagine something like this (the cache parts are optiona):

function get_similar_vevendors($name, $offset, $limit) {
    // Imaginary cache library, 
    // it would return the previously cached search results under a key 
    // created from the name
    if (Cache::has('similar_vevendors_'.$name)) {
        $ven = Cache::get('similar_vevendors_'.$name)
    } else {
        // your orignal code here, filling $ven array filled with similar Vendedor's
        // ...
        // saving the created similar vevendors array in cache
        Cache::store('similar_vevendors_'.$name, $ven);
    }

    // return both the count of the results for pagination 
    // and a acting as the current "page" 
    return array(count($ven), array_slice($ven, $offset, $limit));
}

This way you can use the same parameters for paging trough the array that you would use in SQL and you can init CI's pagination library with something like this:

// $offset is coming from the links generated by the pager
list($total_rows, $paged_vens) = get_similar_vevendors($name, $offset, $items_per_page);
$this->pagination->initialize(array(
    'per_page'   => $items_per_page,
    'total_rows' => $total_rows,
));

Thanks a lot!! I can't use sounds(), but trying the second option. — Laura, Apr 19 '13 at 23:49

Datamapper comparing strings SQL and PHP

1 Answers1