Grouping Two Columns Together

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

Grouping Two Columns Together

Post 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!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Grouping Two Columns Together

Post by Weirdan »

Code: Select all

select `title`, `artist`, concat(`title`, ' ', `artist`) as `my_new_row`
from ...
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

Re: Grouping Two Columns Together

Post 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]
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Grouping Two Columns Together

Post 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'
Post Reply