Select using Like

[tecnicaz]

First of all congratulation on medoo! I got stuck in the following query SELECT * FROM table WHERE ( name LIKE 'foo%' AND address LIKE '%bar' ) how to do it with Medoo Excuse me if this is already answered or addressed some pace else, I just couldn't find it thank you in advance Best Regards

[catfan]

http://medoo.in/api/where #Full Text Searching

[tecnicaz]

Thank you for your quick reply, but I had already read that section in Documentation, but I still can't accomplish the following ( and perhaps I stated this poorly in the beggining ). which is do a string search that begins with e.g. FOO% or ends with e.g. %BAR. When I take a look at the queryString there is always a % sign on either side of string.

[JulioSimon]

A fast and easy solution for your request is to add a new IF statement inside "where_clause" function with a new label for the operation you want to implement, for example: We want to remove the first '%' when using the LIKE operation ('FOO%') so lets start to make a new LIKE operation named as "xLIKE".

if (isset($where['xLIKE']))
    {
        $like_query = $where['xLIKE'];
        if (is_array($like_query))
        {
            $is_OR = isset($like_query['OR']);

            if ($is_OR || isset($like_query['AND']))
            {
                $connector = $is_OR ? 'OR' : 'AND';
                $like_query = $is_OR ? $like_query['OR'] : $like_query['AND'];
            }
            else
            {
                $connector = 'AND';
            }

            $clause_wrap = array();
            foreach ($like_query as $column => $keyword)
            {
                if (is_array($keyword))
                {
                    foreach ($keyword as $key)
                    {
                        $clause_wrap[] = $this->column_quote($column) . ' LIKE ' . $this->quote('' . $key . '%');
                    }
                }
                else
                {
                    $clause_wrap[] = $this->column_quote($column) . ' LIKE ' . $this->quote('' . $keyword . '%');
                }
            }
            $where_clause .= ($where_clause != '' ? ' AND ' : ' WHERE ') . '(' . implode($clause_wrap, ' ' . $connector . ' ') . ')';
        }
    }

Also, remember to add your new xLIKE option inside the explode list (function where_clause(...)):

$single_condition = array_diff_key($where, array_flip(
    explode(' ', 'AND OR GROUP ORDER HAVING LIMIT LIKE MATCH xLIKE')
));

And the usage of this new option is the same as the LIKE one but naming it as xLIKE:

$data = $conn->select("item_data", [

    "[>]item_general"   =>  ["id" => "id"]

],[

    "item_general.country",
    "item_general.lastupdate",
    "item_data.title",
    "item_data.category",
    "item_data.price",
    "item_data.currency",
    "item_data.url_details"

],[

    "xLIKE" => [

        "item_data.title" => "Title"    

    ],

    "ORDER" => "item_data.price"

]);

Regards.

[catfan]

That will be better I think:

$database->select("account", [
    "user_id",
    "user_name",
],[

    "LIKE" => [
        // Match %title%
        "content" => "something"

        // Match %title
        "%content" => "something"

        // Match title%
        "content%" => "something"
    ]

]);

[JulioSimon]

Definitely that is a better solution!

Also I suggest to put that example at the Medoo documentation.

http://medoo.in/api/where #Full Text Searching

[catfan]

@JulioSimon Yes, until the new version with this feature released.

[catfan]

Added, like the sample above.

$database->select("account", [
    "user_id",
    "user_name",
],[

    "LIKE" => [
        // Match %title%
        "content" => "something"

        // Match %title
        "%content" => "something"

        // Match title%
        "content%" => "something"
    ]

]);

[tecnicaz]

Thank you, it worked like a charm

[pwwiur]

if you mean that "text%" does not working you should fix the ~ operator section in data_implode method

fix these lines:

    if ($operator == '~' || $operator == '!~'){
        if ($type != 'array'){
            $value = array($value);
        }
        $like_clauses = array();
        foreach ($value as $item){
            $item = strval($item);
            if (preg_match('/^(?!(%|\[|_])).+(?<!(%|\]|_))$/', $item)){
                $item = '%' . $item . '%';
            }
                        elseif(substr($item, -1) == "_"){
                            $item = $item . '%';
                          }

            $like_clauses[] = $column . ($operator === '!~' ? ' NOT' : '') . ' LIKE ' . $this->fn_quote($key, $item);
        }
        $wheres[] = implode(' OR ', $like_clauses);
    }