Redbeanphp - Query securely with multiple conditions

Question

I have a list of users in my database, e.g. with firstname, lastname and company, which are all textfields. Now I have an autocomplete field where one can enter firstname, lastname and company to find a user that has a similar name or company name. I want to accomplish this with RedbeanPHP. Unfortunately the documentation doesn't explain how to implement multiple conditions into their "finder"-functions. Also the "exec"-function isnt explained very well, because there I don't know how to prepare values from a form into the manual statement.

Here is my solution that does find users when either firstname or lastname or company is entered:

        if (!empty($_POST['autocomplete'])) {
            $userListbyFirstname = R::find( 'user', ' firstname LIKE ?', [ '%' . $_POST['autocomplete'] . '%' ] );
            $userListbyLastname = R::find( 'user', ' lastname LIKE ?', [ '%' . $_POST['autocomplete'] . '%' ] );
            $userListbyCompany = R::find( 'user', ' company LIKE ?', [ '%' . $_POST['autocomplete'] . '%' ] );
            $userRbList = array_merge($userListbyFirstname, $userListbyLastname, $userListbyCompany);
            $userList = array();
            foreach ($userRbList as $userRb) {
                $userList[] = $userRb['firstname'] . ' ' . $userRb['lastname'] . ' ' . (!empty($userRb['company']) ? $userRb['company'] : '');
            }
            return json_encode($userList);
        }
        else
        {
            return json_encode(array());
        }

When someone enters "John Williams" it returns an empty array, even if there is an database entry for it. I would know how to do it with plain php, but not how to accomplish, that those multiple fields can be searched together (= multiple conditions) with RedBeanPHP.

I suspect you are over estimating what the redbeanPHP find function does. It takes the sql string and appends it to the generated SQL after the WHERE statement. Then it prepares the resulting SQL query string. So: To match on first and last name use the SQL string: ' firstname LIKE ? and lastname LIKE ?' as the SQL. The where clause can be a complex as you wish. The exec function is similar. — Ryan Vincent
$userRbList = R::find( 'user', ' (firstname LIKE ?) or (lastname LIKE ?) or (company LIKE ?)', [ '%' . $_POST['autocomplete'] . '%' ] ); doesn't work for example like expected — hardking
okay, thank you that works! if you want you can answer the question i'll give you a vote and mark it as top answer — hardking

Ryan Vincent Ryan Vincent · Accepted Answer · 2015-10-16T13:21:32

Requirements: Add multiple conditions to the WHERE clause when using the redbeanPHP find function.

The find function expects a valid SQL WHERE clause. It can be any valid SQL. Often, it is some multi column filter that is required.

Example: To match on firstname OR lastname OR company.

The SQL Query string will be something like:

' (firstname LIKE ?) or (lastname LIKE ?) or (company LIKE ?)'

Now, there are three placeholders so when the query is executed then it needs three runtime values in the parameters array.

Example:

array( '%'. $_POST['firstname'] .'%',
       '%'. $_POST['lastname']  .'%',
       '%'. $_POST['company']   .'%' 
    )

Now, redbeanPHP takes the SQL where clause you have supplied and appends it to the generated query. It then prepares the resulting SQL which looks like:

SELECT * 
FROM user
WHERE  (firstname LIKE ?) or (lastname LIKE ?) or (company LIKE ?);

It then executes the query using the array of runtime parameters you supply.

That is what:

R::find('user', 
        ' (firstname LIKE ?) or (lastname LIKE ?) or (company LIKE ?)',             
         array( '%'. $_POST['firstname'] .'%',
                '%'. $_POST['lastname']  .'%',
                '%'. $_POST['company']   .'%')
       );

Actually does.

Redbeanphp - Query securely with multiple conditions

1 Answers