0

As described in the title, I would like to concatenate more than 2 columns in my SQL query like this: $reqArticles = $db->prepare('SELECT id FROM articles WHERE CONCAT(title, content, author, location) LIKE "%'.$element.'%" ORDER BY id DESC'); This returns an empty array unlike when I enter only 2 parameters in "CONCAT". PS: there is a similar question but its answer does not correspond to my need.

EDIT : the data looks like this :

`id = 1
fields = contents_fields
title = 'the football'
content =' Football is the most popular sport in the world '
author = 'Patrick'
location = 'French'`

And the output I'm waiting for must contain all the ids of the items in the "element" to be found

Re-Edit :

Important things I forgot: the location field is often null. I think that's where the error comes from because I did a small test by removing this field and leaving the other 3 the request works correctly. But to be confirmed.

so the data looks more like this:

`id = 1
fields = contents_fields
title = 'the football'
content =' Football is the most popular sport in the world '
author = 'Patrick'
location = 'French' or NULL`
the star
  • 259
  • 1
  • 2
  • 10
  • 1
    Please could you post some sample data and expected output? – DhruvJoshi Apr 22 '22 at 18:15
  • Why does the question linked to not correspond to your needs? – Nigel Ren Apr 22 '22 at 18:22
  • `CONCAT` can take any number of columns. What does `$element` contain and what does the database have that should match? Separate note possibly unrelated `$element` should not be in the `prepare` function, that should be a placeholder and be being bound. – user3783243 Apr 22 '22 at 18:28
  • @ DhruvJoshi I have made the necessary changes, @ Nigel Ren Because the answers suggest to put a comma between each parameter and that's what I already do and @ utilisateur3783243 can you be more precise please? I don't really understand – the star Apr 22 '22 at 18:35
  • `LIKE concat("%", ?, "%") ORDER BY id DESC` then bind in `$element`. Not clear if using PDO or mysqli here so cant advise on which bind function is correct. `var_dump($element)` gives what? – user3783243 Apr 22 '22 at 18:42
  • var_dump gives an empty array and I use PDO. And also I made a last modification that must be important – the star Apr 22 '22 at 18:50
  • If `var_dump` is empty then that is your issue. You are not setting `$element` correctly. How is that being assigned? You can pass that to an array in the `execute` function since you are using PDO. That will allow it to bind correctly. Please use `@` so notifications are used. – user3783243 Apr 22 '22 at 18:53
  • excuse me var_dump gives this: 'object(PDOStatement)#4 (1) { ["queryString"]=> string(111) "SELECT id FROM events WHERE CONCAT(title, author, location, content) LIKE "%Football%" ORDER BY id DESC" }'. I had done 'var_dump(element->fetchAll)' – the star Apr 22 '22 at 19:05
  • `$element` should not be a PDO object. It should be a string. You ran what was provided?? You also need to use `@`s or notifications will not be sent. – user3783243 Apr 22 '22 at 19:11
  • @ utilisateur3783243 var_dump($element) == string(8) "Football"; – the star Apr 22 '22 at 19:39

1 Answers1

-1

try printing the concat part of the query to confirm that the output is correct. As you indicated in the post, you may have to write CONCAT(title,CONCAT(content,CONCAT(author, location)))

Swati Vishwanathan
  • 33
  • 6