how to handle data from join query with php? [closed]

51
June 26, 2021, at 6:50 PM
Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 12 hours ago.

Improve this question

so i have three tables book, categories, book_category - I join them with this query:

$stmt = $con->prepare("SELECT novel.*,categories.* 
            FROM novel 
            INNER JOIN book_category ON novel.NovelID = book_category.novelid 
            INNER JOIN categories on categories.ID = book_category.categoryID");
$stmt->execute();
$rows = $stmt->fetchAll();

here is image of db statement table

[![this is image of db table in mysql server][1]][1]

so output with php this way

<div class="container">
    <div class="row">
    <?php
        $image = '';
        $count = '';
        $des = '';
        $es = '';
        $esa = '';
        
        foreach ($rows as $row) {
            echo '<div class="col-md-6">';
            echo '<div class="desgin">';
            echo '<div class="image pull-right">';
            echo '<img src="images/cover4.jpg" alt="">';
            echo '</div>';
            echo '<div class="title">';
            if ($count != $row['NovelName']) {
                $count = $row['NovelName'];
                echo  '<h2>' . $count . '</h2>';
            }
            echo '</div>';
            echo '<div class="describe">';
            if ($des != $row['description']) {
                $des = $row['description'];
                echo  '<p>' . $des . '</p>';
            }
            echo '</div>';
            echo '<div class="categories">';
            if ($es != $row['Name']) {
                $es = $row['Name'];
                echo  '<span>' . $es . '</span>';
            }
            echo '</div>';
            echo '</div>';
            echo '</div>';
        }
    ?>
    </div>
</div>

here is the result in image

[![this is image of the output in the page][2]][2]

there is no problem with novelname and description output but the categories get duplicate with loop as in the image above so how can i resolve this

update my question

I change the select statement with group concat and group by boo name like this

$stmt = $con->prepare("SELECT DISTINCT novel.NovelID AS ne, novel.NovelName,novel.description,novel.NovelImage,
           GROUP_CONCAT(categories.Name) AS em FROM novel INNER JOIN book_category ON
          novel.NovelID = book_category.novelid INNER JOIN categories on categories.ID = book_category.categoryID GROUP BY novel.NovelName");
         $stmt->execute();
         $rows = $stmt->fetchall();

then I put the categories column into explode like this

 $arr = array();
  foreach ($rows as $row) {
    $arr[] = explode(',', $row['em']);

I work fine at first but when I but i add new record of book it will add the new record categories to the last array and create new array by the new record I print out the explode array with the new record and I get this

Array
(
    [0] => Array
        (
            [0] => أكشن
            [1] => فانتازيا
            [2] => قوي خارقة
            [3] => مهارات قتال
        )
)
Array
(
    [0] => Array
        (
            [0] => أكشن
            [1] => فانتازيا
            [2] => قوي خارقة
            [3] => مهارات قتال
        )
    [1] => Array
        (
            [0] => أكشن
            [1] => فانتازيا
            [2] => قوي خارقة
            [3] => مهارات قتال
        )
)
Array
(
    [0] => Array
        (
            [0] => أكشن
            [1] => فانتازيا
            [2] => قوي خارقة
            [3] => مهارات قتال
        )
    [1] => Array
        (
            [0] => أكشن
            [1] => فانتازيا
            [2] => قوي خارقة
            [3] => مهارات قتال
        )
    [2] => Array
        (
            [0] => أكشن
            [1] => فانتازيا
            [2] => قوي خارقة
            [3] => مهارات قتال
        )
)

how can I fex this [1]: https://i.stack.imgur.com/YtxwU.png [2]: https://i.stack.imgur.com/KhEUW.png

READ ALSO
ON DUPLICATE KEY UPDATE non-index columns

ON DUPLICATE KEY UPDATE non-index columns

I have a code that is updating a few mySQL tables with the data that is coming from a Sybase databaseThe table structures are exactly the same

67
How can I override MTablePagination component in Material- Table reactJS

How can I override MTablePagination component in Material- Table reactJS

I am stuck in my assignment, I have more than one million rows and want to display them in material-table reactjsHow do I get data from back-end only for rows on one page and then get next set of rows on next page click?

56
Javascript: Sends two arrays of base64 encoded images to php via xhr

Javascript: Sends two arrays of base64 encoded images to php via xhr

Hello everyone I'm here againI'm trying to write a code that's able to resize all images before they're uploaded to the server

39