Select multiple values as an array in MySQL

Question

Suppose I have the following schema, and for your information, I'm using MySQL:

Paper(paperId, title) Author(authorId, authorName) Paper_Author(paperId, authorId)

I design the tables like this because a Paper may have more than one Author and a Author may write more than one Paper.

I'd now like to get the title and all the authors of a Paper.

I've been wondering if I could do this in one single statement?

If it is a YES, then how? So far I have only come with an idea of trying to get an array of Authors right from MySQL.

If it is a NO, how can I do it to maintain the atomicity of the transaction? (Let's consider getting the title and the author as ONE transaction)

Edit - in response to Justin E's comment:

I'd like to get something like this:

array(
    "title" => "ABC"
    "authors" => array("Ken", "Kitty", "Keith")
)

This would be difficult to achieve when there are multiple books in your result set. The answer I provided allows for you to get the name of the book in a simple foreach loop, with an array of authors. — Justin E
– Justin E, Commented Dec 7, 2015 at 16:18

Blag · Accepted Answer · 2015-12-07 16:34:01Z

3

MySQL GROUP_CONCAT() is what you need (and maybe PHP explode())

SQL Fiddle

MySQL 5.5 Schema Setup:

CREATE TABLE Paper (`paperId` int, `title` varchar(7));
INSERT INTO Paper (`paperId`, `title`)
VALUES (1, 'hello'),(2, 'hola'),(3, 'bonjour');

CREATE TABLE Author (`authorId` int, `authorName` varchar(3));
INSERT INTO Author (`authorId`, `authorName`)
VALUES (1, 'me'),(2, 'moi');

CREATE TABLE Paper_Author (`paperId` int, `authorId` int);
INSERT INTO Paper_Author (`paperId`, `authorId`)
VALUES (1, 1),(1, 2),(2, 2);

Query 1:

SELECT Paper.`title`, GROUP_CONCAT(Author.`authorName`) as `authors`
FROM Paper_Author
INNER JOIN Paper
  ON Paper.`paperId` = Paper_Author.`paperId`
INNER JOIN Author
  ON Author.`authorId` = Paper_Author.`authorId`
GROUP BY Paper.`title`

Results:

| title | authors |
|-------|---------|
| hello |  me,moi |
|  hola |     moi |

edited Dec 7, 2015 at 16:34

answered Dec 7, 2015 at 16:06

Blag

5,8942 gold badges26 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chung Lun Yuen Over a year ago

What will I get? Is it sth like this? title: "ABC", author: {"Ken", "Kitty"}

Blag Over a year ago

@ChungLunYuen yes, you get a list separated by , : Ken,Kittyor what you pass as 2param

Chung Lun Yuen Over a year ago

Oh! That's exactly what I need! Thank you! ^^

Justin E · Accepted Answer · 2015-12-07 16:10:19Z

2

SELECT paper.*, author.* FROM paper_author 
  LEFT JOIN paper USING(paperid)
  LEFT JOIN author USING(authorid)

Your array will look like:

Array(
  0 => Array(
    'title' => 'This is a title',
    'authorname' => 'This is an Authors Name',
  )
);

$authors = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $authors[$row['title']][] = $row['authorname'];
}

print_r($authors);

Array(
    'Book 1' => array(
         'John Smith',
         'Jane Doe'
    )
);

edited Dec 7, 2015 at 16:10

answered Dec 7, 2015 at 16:06

Justin E

1,25016 silver badges30 bronze badges

2 Comments

Justin E Over a year ago

Example updated with PDO code that would make an array keyed by book name with multiple child elements for each title representing an author.

Chung Lun Yuen Over a year ago

I have also edited my question to specify what I need.

Collectives™ on Stack Overflow

Select multiple values as an array in MySQL

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related