CODEWITHSUNDEEP
phploopsmysqliforeachprepared-statement
anonprophet
anonprophet

Reputation: 97

PHP prepare statement insert too many in foreach

in my input text i just input

biografi,

it's should only insert 1 row,

but it's insert 5 times (maybe from this -> my category table contain 5 category)

if i input

bigrafi,dictionary,

its insert 10 times

$categories = explode(",", $kategori);
    $stmt2 = $mysqli->prepare("INSERT INTO book_category (idbook, idcategory)
                                    SELECT book.idbook, category.idcategory
                                    FROM book, category
                                    WHERE book.isbn = ?
                                    AND category.nmcategory = ?");
    foreach ($categories as $category) {
        if (empty($category) || $category == "") {
            continue;
        }

        $stmt2->bind_param("si", $isbn, $category);
        if($stmt2->execute()) {

        } else {
            showAlert("Error when insert category");
        }
    }
    $stmt2->close();

its should only insert biografi(3) id to book_category table

idbook | idcategory
    8 | 3

but its insert 5 times

idbook | idcategory
    8 | 1
    8 | 2
    8 | 3
    8 | 4
    8 | 5

enter image description here

sorry for bad explanation, i can't speak english well. i hope u understand what i mean

SELECT idcategory, nmcategory FROM category

| idcategory | nmcategory |
| 1          | kamus      |
| 2          | agama      |
| 3          | biografi   |
| 4          | novel      |
| 5          | komik      |

SELECT idbook, isbn FROM book

| idbook | isbn |
| 1      | 111  |

Upvotes: 1

Views: 980

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270647

You are returning two separate pieces of information from two tables -- the requested isbn and the requested category are not related (you are creating their relationship by inserting into book_category), and so doing a join query as you have done without a joining condition results in a cartesian product (all books multiplied by all categories). Since there are 5 total rows in the categories table, all 5 are returned .

You can instead structure the query with two subselects - one querying book and the other querying category. Each subselect will return exactly one value, and the two will be combined together into a single row.

// The query reduces to "(SELECT book), (SELECT category)" as separate
// actions, which can be done together as subselects
$stmt2 = $mysqli->prepare("INSERT INTO book_category (idbook, idcategory)
    SELECT
     (SELECT idbook FROM book WHERE isbn = ?) AS idbook,
     (SELECT idcategory FROM category WHERE nmcategory = ?) AS idcategory");

// Your bind/execute/fetch can remain the same
foreach ($categories as $category) {
    if (empty($category)) {
        continue;
    }

    $stmt2->bind_param("si", $isbn, $category);
    if($stmt2->execute()) {

    } else {
        showAlert("Error when insert category");
    }
}
$stmt2->close();

Upvotes: 3

Related Questions