Good afternoon!

According to my law, all empty cells in the MySQL database should be NULL, but I have a SQL query that involves searching users by 4 criteria, first name, last name, city, gender. But a problem arises, if a user has at least one of these NULL cells, then it does not fall under LIKE and, accordingly, under the result, how can I solve this?

$users = Users::findByRawSql('u.f_name LIKE ? AND u.s_name LIKE ? AND u.sex LIKE ? AND u.city LIKE ?', array( $this->_addLike($this->request->getPost('f_name')), $this->_addLike($this->request->getPost('s_name')), $this->_addLike($this->request->getPost('sex')), $this->_addLike($this->request->getPost('city')) )); $sql = "SELECT u.id, u.avatar, u.city, u.f_name, u.s_name, u.notes, u.username, SUM(b.summ) AS summ FROM users AS u LEFT JOIN balance AS b ON b.user_id = u.id WHERE $conditions GROUP BY u.id ORDER BY summ DESC";
  • one
    Request to the studio! - Donil
  • I think, it is not necessary to generate requests of type .... field LIKE '%%' - zb '10
  • @eicto, don't worry, my framework has included precaution methods. @Donil, Sori forgot)) Added to the header. - chuikoff
  • In the name of the law framework! sorry - etki

1 answer 1

Instead of a column name, use COALESCE , for example:

COALESCE (u.f_name, '') LIKE

  • Strangely, in the query editor PhpStorm works when executed, but for some reason it does not in the system code. - chuikoff
  • @chuikoff, most likely escaping the request somewhere along the way turns the function into a string. - etki
  • @Etki, apparently, yes, can you somehow get around this? - chuikoff
  • @chuikoff, without knowing at least the name of the framework and the type of the updated query, nothing can be said. - etki
  • Probably, you need to escape single quotes, such as COALESCE (u.f_name, \ '\') LIKE ... Or arrange the entire request in double. - msi