Page 1 of 1

Grouping Two Columns Together

Posted: Sun Jul 10, 2011 7:28 am
by lenton
I have a database which stores the information of MP3 files.

This is my current database with an example row:
____________________
| `` title ` | ` artist ` |
----------------------------
| Gold Dust | DJ Fresh |
----------------------------

Is there a way to temporarily group title and artist together like this:
______________________________________
| `` title ` | ` artist ` | ` my_new_row ` |
-----------------------------------------------------
| Gold Dust | DJ Fresh | Gold Dust DJ Fresh |
-----------------------------------------------------

I need to group them like this so I can match keywords in both title and artist. Thanks for your help!

Re: Grouping Two Columns Together

Posted: Sun Jul 10, 2011 11:34 am
by Weirdan

Code: Select all

select `title`, `artist`, concat(`title`, ' ', `artist`) as `my_new_row`
from ...

Re: Grouping Two Columns Together

Posted: Sun Jul 10, 2011 2:52 pm
by lenton

Code: Select all

SELECT `title`, `artist`, `album`, concat(`title`, ' ', `artist`, ' ', `album`) AS `all` FROM `mp3s` WHERE `all` = '$query'
gives me this error:

[text]Unknown column 'all' in 'where clause'[/text]

Re: Grouping Two Columns Together

Posted: Sun Jul 10, 2011 7:04 pm
by Weirdan
MySQL manual wrote: Standard SQL disallows references to column aliases in a WHERE clause.
So you'd have to repeat that concat() expression in the where, like this:

Code: Select all

where concat(...) = '$query'