I'm implementing a book search function based on authors. I should return a query result that contains all the books written by the queried author. However, it is possible that a query for certain author names returns multiple results (e.g., a query of "Smith, W" might match "Smith, Wesson" and "Smith, Will").

So, my problem is how to "concatenate" all the books written by these different authors. If I don't consider the possibility of multiple authors matching a query, I'd go about something like this (in pseudocode, as my real code is quite messy right now):

• search author table for the author
• matching the query
• get author's authorid
• search book table for book records with the same authorid

However, with the possibility of multiple authors, I have something like this in mind:

// search author table for authors matching the query

foreach(author_match as am){
  // search book table for book records with authorid=am.authorid
  // Is there such thing as this? :\
  book_results += all the rows returned by previous query
}
return book_results;

I'm doing this in PHP (with CodeIgniter Framework) and MySQL. Is there any function/operator that will allow me to do this? Yes, I've tried +=, even if I wasn't expecting much from it, to an ugly output.

Again, my apologies for the pseudocode. I'll try to clean-up my code and edit ASAP but if an answer comes before that, it'd be just as awesome. Thanks.

• php
• mysql
• codeigniter

I agree with Michael, it seems you need either an INNER JOIN or a LEFT JOIN.

You say you are using Codeigniter so here is a Codeigniter specific example:

$this->db->select('author.*, 
                   book.*');
$this->db->from('authors');
$this->db->join('books', 'books.authorid = authors.id', 'left');
$this->db->where('authors.id', $the_author_id);

Also see:

• Codeigniter's Activerecord documentation
• Other Codeigniter JOIN questions on SO

You can use .= to concatenate. However, more efficiently, you can simply specify multiple WHERE clauses in the SQL, so you'd have the following pseudocode:

search author table for authors matching the query
where = 'with '
foreach(author_match as am){
where += 'authorid=' . am.authorid . ' OR' }
where = substr(where[0,strlen(where)-3)
}
search book table for book records [where clause here]
return book_results

Perhaps you can modify your query to use LEFT JOIN. This way you can get multiple query results in one query.

To do a concatenate in PHP, use

.=