There is a table:

articles. id | author | title | content | type 1 | author1, author2 | thetitle1 | text1 | typeA 2 | author1 | thetitle2 | text2 | typeB 3 | author2 | thetitle3 | text3 | typeA

Arrays come from the client, they are like data filters:

 $conditions = array(); $where = ''; if(isset($_POST['authors'])){ //empty, is_array and etc. $authors = $_POST['authors']; // [ author1, author2 ] $conditions[] = "author IN ('".implode("','",$authors)."')"; } if(isset($_POST['types'])){ $types = $_POST['types']; // [ typeA, typeB ] $conditions[] = "type IN ('".implode("','",$types)."')"; } if(!empty($conditions)){ $where = ' WHERE '.implode(' AND ', $conditions); } $sql = "SELECT * FROM articles".$where;

Everything would be fine, but here only the author field can contain authors separated by commas, as can be seen from the example, which does not fall under such a filter: author IN ('author1') - naturally, such a filter will select only the 2nd entry from the table, but all records where this author participated (and this is the 1st and 2nd entries).

  • I will say right away that I did not invent the table (s) structure, and I don’t want to add more tables. That's how it is, so it is. - Smash

2 answers 2

Such an option immediately comes to mind:

 if(isset($_POST['authors'])){ //empty, is_array and etc. $authors = $_POST['authors']; // [ author1, author2 ] if (is_string($authors)) $authors = array($authors); if (is_array($authors)) { foreach ($authors as $value) { $conditions[] = "FIND_IN_SET('".$value."', `author`)"; } } }
  • Doesn't this mean that the field itself must be of type SET ? - Smash
  • Somehow this AND doesn’t work out well, something like this query came out: SELECT * FROM articles WHERE FIND_IN_SET('author1', author ) AND FIND_IN_SET('author2', author ) AND FIND_IN_SET('author3', author ) . - Smash
  • No, the field should be plain text - we simply substitute it into the FIND_IN_SET function as a list of value options, the data type SET has nothing to do with it. Well, a concise query with such a structure will not work. - Indifferently

With this structure, you probably have to use LIKE (or SUBSTRING ) with each value from the array: author LIKE '%author1%' OR author LIKE '%author2%' ...

Or convert the values ​​in the column to an array and compare arrays.

  • LIKE with such a structure never rolls. "a, b, c", "k, ab, ac" - LIKE %a% returns both, and by condition only the first is needed. - Indifferently
  • If you mean that the name of an ator is a substring in the name of another author, then it’s cool. But it already depends on the data, how likely this situation is. For example, if the data is of the Surname I.O. And if these are logins / nicks, then the probability is high. - msi