How to explode data using MYSQL and using NOT IN?

[IQBAL_13]

I have a programming code problem, I need help to solve the problem.

How to explode data array using MYSQL and using NOT IN.

The program code is below

<form style="overflow: hidden;" method="post">
    <div class="table-responsive">

    <?php
        $sql = "SELECT * FROM kursus WHERE KodKursus NOT IN 
        (SELECT kursusList FROM pensyarahkursus WHERE IDUser= '$value');";
        $stmt = $db->query($sql);

        $i = 1;
        if ($stmt->rowCount() > 0) { ?>

        <table class="table table-hover table-bordered table-striped" id="dataTable" width="100%" cellspacing="0">
            <thead>
                <tr>
                    <td style="width: 10px;"></td>
                    <th style="width: 20px;">#</th>
                    <td>Semester</td>
                    <td>Kod Kursus</td>
                    <td>Nama Kursus</td>
                </tr>
            </thead>
            <tbody>
            <?php
                while ($row = $stmt->fetch()) {
                    echo "<tr>"; ?>
                <td>
                    <input type="checkbox" aria-label="Checkbox for following text input" name="kursus[]" value="<?php echo $row['IDKursus']; ?>">
                    </td>
                        <th><?php echo $i ?></th>
                        <td><?php echo $row['Semester']; ?></td>
                        <td><?php echo $row['KodKursus']; ?></td>
                        <td><?php echo $row['NamaKursus']; ?></td>
                    <?php 
                        echo "</tr>";
                        $i++; } ?>

                </tbody>
            </table>
            <?php
                } else {
                    echo "<br>";
                    echo "<center><h4> Tiada Rekod</h4></center>";
                } ?>
        </div>
        <div class="d-flex justify-content-between mt-3">
            <button type="submit" class="btn btn-success" name="daftar_kursus">Daftar Kursus</button>
            <button type="reset" class="btn btn-primary">Reset</button>
        </div>
    </div>
</form>

Picture below sample data pensyarahKursus

Picture bewlow sample data from kursus

[Reverend Jim]

How to explode data array using MYSQL and using NOT IN

What does that mean?

[rproffitt]

PHP does have an explode function/method so I would be guessing why you would restrict yourself to using MYSQL and not PHP.

In short, the question is unclear. Tell more about what you need to do.

[Dani]

The question here has nothing to do with PHP's explode() function. I can see exactly what is going wrong here for the OP.

You can see on lines 5 and 6 of the OP's code, they have the MySQL query:

SELECT * FROM kursus WHERE KodKursus NOT IN 
(SELECT kursusList FROM pensyarahkursus WHERE IDUser= '$value');

There's a subquery here. Basically what this subquery says to do is to select all of the items in the kursusList column of the pensyarahKursus table that match a specific IDUser. Then, once we have the list of all kursusLists, select all rows from the kursus table whose KodKursus column is not in the list.

Unfortunately, the reason the code doesn't work is because the kursusList column contains comma-delimited lists. As mentioned, the OP wants to "explode" these lists the way that one does in PHP with PHP's explode() function. Unfortunately, explode() does not have a MySQL equivalent. It is not ideal to store comma-delimited lists within a single MySQL column because they are not easy to work with or to search.

There is a MySQL function called FIND_IN_SET() that you may be able to use. For example, you can do something such as:

SELECT * FROM pensyarahKursus WHERE FIND_IN_SET('KK-2', kursusList)

and what that will do is find all rows that have KK-2 as an item in the kursusList column. Unfortunately, it will work very slowly because it will have to manually search all rows in the table (you can't use a MySQL index in this way). Also, I can't think of a way of getting it to work with your subquery, because you need to use FIND_IN_SET() as opposed to IN().

There is a Stack Overflow question posted here that should answer what you're trying to do in terms of "exploding" a MySQL column. Unfortunately, it's not a simple process, as mentioned and also as you can see from that thread.

It is never, ever, ever a good practice to design your MySQL tables such that you need to query comma-delimited lists. I highly recommend that you change your table design to be more conducive to MySQL's capabilities, in order to accomplish what you're trying to do.

I am sorry that I couldn't be the bearer of better news for you.