Display multiply records in one cell

Question

I'm trying to display data from database in bootstrap datatables. The thing is that I have book with more than one author, so I have one array for first names and second for last names. And now I have trouble displaying it in < td >. Is it good way to get fnames and lnames in two arrays and try to display it that way or is there any other option?

My table and arrays:

Here is my code in which I display data:

$sql2 = "SELECT fname, lname FROM `books_has_authors` JOIN authors on books_has_authors.authors_id_author = authors.id_author 
                            WHERE books_has_authors.books_book_id = '".$row["book_id"]."'";
                            $result2 = mysqli_query($conn, $sql2);
                            $authors_array = array();
                                $authors_array2 = array();

                            //Getting data names of all authors
                            while($row2 = mysqli_fetch_assoc($result2)){
                                $authors_array[] = $row2['fname'];
                                $authors_array2[] = $row2['lname'];
                            }


                            echo '<pre>';
                            print_r($authors_array);
                            print_r($authors_array2);
                            echo '</pre>';  
                               echo '  
                               <tr>  
                                    <td>'.$row["name"].'</td>  
                                    <td>'.$row["year"].'</td>  

                                    <td> 
                                    //Here I want display those names each in new line if possible
                                    </td>  
                               </tr>  
                               ';

You can use group concat for this or string combination operation https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — Nilesh Lathe, Dec 01 '17 at 18:00
You can use a simple `for loop` or concatenate the first- and lastname to fullname in your `while loop` — cSteusloff, Dec 01 '17 at 18:02

score 1 · Answer 1 · answered Dec 01 '17 at 18:08

Librarians generally list multiple authors for books in this form.

David M Glover; David Dugan; Jeff Goldblum; James D Watson; Francis Crick;

For example, take a look at this catalog entry. https://www.worldcat.org/title/dna-the-secret-of-life/oclc/605305974

To do this, you want to retrieve your authors with first and last names concatenated, perhaps with a query like this:

SELECT CONCAT(fname, ' ',lname, ';') authorname FROM `books_has_authors` ...

Then you can put these authorname items together into a single table cell.

Or, you can do this

SELECT GROUP_CONCAT(CONCAT(fname, ' ',lname, ';') SEPARATOR ' ') authornames
  FROM `books_has_authors` ...
 GROUP BY book

and get back a single authornames item you can use in your table cell.

(Be aware that the order of authors on listings of books is not up to a programmer or librarian. It is chosen by the authors. If you listed the book in this example with Dr. Crick anywhere except the last author, he would be unhappy.)

mtr.web · Accepted Answer · 2017-12-01T18:36:01.630

Assuming the first and last name are both populated, you could do it with a for-loop, based on the number of either array's values:

for($x=0; $x < count($authors_array); $x++) {
    echo '<p>'. $authors_array[$x] . ' ' . $authors_array2[$x] . '</p>';
}

...Or you could change the select statement to concat the names and only populate one array for the output:

$sql2 = "SELECT CONCAT(fname, ' ', lname) AS 'fullname' FROM `books_has_authors` JOIN authors on books_has_authors.authors_id_author = authors.id_author WHERE books_has_authors.books_book_id = '".$row["book_id"]."'";

...

//Getting data names of all authors
while($row2 = mysqli_fetch_assoc($result2)){
    $authors_array[] = $row2['fullname'];
}

...

//Here I want display those names each in new line if possible
for($x=0; $x < count($authors_array); $x++) {
    echo '<p>'.$authors_array[$x].'</p>';
}

If you are already echoing, then you can either close the echo and then do the loop:

echo '.....
<td>';

//Here I want display those names each in new line if possible
for($x=0; $x < count($authors_array); $x++) {
    echo '<p>'.$authors_array[$x].'</p>';
}

echo '</td>';

or you can escape the php and not use echo:

//php-code
...
?>
<tr>
    <td><?php echo $row["name"]; ?></td>  
    <td><?php echo $row["year"]; ?></td>  
    <td>
    <?php for($x=0; $x < count($authors_array); $x++) {
        echo '<p>'.$authors_array[$x].'</p>';
    } ?>
    </td>
</tr>

Looks good and simple, but the thing is im already in echo in my when I do echo of all table values. How can I use it in single < td > when this < td> is in echo already — Czesi Joum, Dec 01 '17 at 18:05
Thanks, It's working now. But I lost sort and search in datatable due to
tag inside. Any way to display data and have these features od datatable? — Czesi Joum, Dec 01 '17 at 18:35
You could enclose them in one `
` tag and add line-breaks, i.e. `
Fname Lname
Fname Lname
`, but in any case, it will be limited to the first author's name for sorting. — mtr.web, Dec 01 '17 at 18:38
Well I didn't check it well actually. The thing that caused losing sort and search was that I didnt fill all columns, I just removed 2 empty columns and I got sorting and searching even with
tag. ;) — Czesi Joum, Dec 01 '17 at 18:48

Display multiply records in one cell

2 Answers2