[dhirsch226]

Hi. I am trying to set up a PHP/MySQL backend for a webpage that will display scientific abstracts. The datasets will be small, so performance is not a big issue here - I'm trying to offload the content maintenance onto the content provider, so I don't have to keep cleaning up his ugly MS Word files to post the content.

I've got two tables: author, and abstract

author has columns that include name and id
abstract has columns that include authorList, which is a comma-delimited set of id numbers corresponding to author.id. Here is an example:

example author data:
+----------+----+
| name     | id |
+----------+----+
| jones    | 1  |
| smith    | 2  |
| allen    | 3  |
+----------+----+

example abstract data:
+-------------+------------+
| content     | authorList |
+-------------+------------+
| blahblah    | 2,3        |
| foobar      | 1          |
| fooblah     | 3,1,2      |
+-------------+------------+

I'm trying to construct a SELECT statement that will translate the authorList into a human-readable list of names, and being fairly new at MySQL this involved, I am stumped and hope you can help. (Well, I can think of brute-force PHP methods to do it, but it seems like this must be a common SQL task and have an easy solution)

I would like to get the following type of output:

+-------------+-------------------+
| content     | authorTextList    |
+-------------+-------------------+
| blahblah    | smith,allen       |
| foobar      | jones             |
| fooblah     | allen,jones,smith |
+-------------+-------------------+

Note: this is not homework for some class. This is for the following website that I manage: http://mbvo.wwu.edu. I believe the MySQL version is at least 4.1.
Thanks, in advance
Dave Hirsch

[MMJ]

I seriously doubt that this is possible with SQL only. Heres some PHP that should work though.

PHP Code:

<?php
echo "<table border='1'>
<tr>
<th>content</th>
<th>authorTextList</th>
</tr>";

$query = mysql_query("SELECT content, authorList FROM `abstract`");
while ($column = mysql_fetch_array($query))
{
    $authorList = explode(',', $column['authorList']);
    echo "<tr>";
    echo "<td>" . $column['content'] . "</td>\n<td>";
    for ($i = 0; $i <= (count($authorList)-1); $i++)
    {
        $idName = mysql_fetch_array(mysql_query("SELECT name, id FROM `author` WHERE id = {$authorList[$i]}"));
        echo implode(",", $idName);
    }
    echo "</td></tr>";
}

echo "</table>";
?>