Insert multiple records one query

[tanklessgunit]

I'm trying to solve a problem which I've now for 2days. But with any kind of success

I want to create something where people can track their progress, so if somebody 'creates a progress' certain information need to be selected from the DB and inserted into other table.

for now I got this:

    ID | ITEM       | MOBNAME | GAME | LOG_ID | LOG_NAME

    1  | test test3 | spider  | TEST |    1   |  just a test

As you see, I need COLUMN item among each other not in 1 single row. like this:

    ID | ITEM   | MOBNAME | GAME | LOG_ID | LOG_NAME

    1  | test   | spider  | TEST |    1   |  just a test
    1  | test3  | spider  | TEST |    1   |  just a test
    1  | test4  | spider  | TEST |    1   |  just a test

This is my current code for selecting and inserting:

    $sql = "INSERT INTO log_create(`name`, name2, game, monster, info)VALUES('$name', '$name2', '$game', '$mobname', '$info')";
    $log = $db->query("SELECT * FROM log_mitem WHERE mobname = '" .$mobname. "' AND game = '" .$game. "'") or die($db->error);

    if($log1 = $log->fetch_object())
        {
    while($loco = $log->fetch_object())
        {
    $item .= "$loco->itemname";
        }
    $logss = "INSERT INTO log_drops(`item`, mobname, game, log_id, log_name)VALUES('$item', '$mobname', '$game', '$id', '$name')";
    if($result1 = $db->query($logss));
    }

[pzuurveen]

It looks to me that your database is not structured
properly. Take a look at database normalization

[tanklessgunit]

@pzuurveen still cannot solve this problem after reading it. I'm new to php especailly PDO

[veedeoo]

The ID on your second table must be set to auto-increment. Another possible problem ahead is that you have too many identical columns between 1st and 2nd tables.

2nd table can be something like this . Assuming that the game serve like a category.

+----+--------+--------+--------+
+ ID +  ITEM  +  GAME  + LOG_ID +
+----+--------+--------+--------+
+ 1  +  test  + spider +   1    +
+----+--------+--------+--------+
+ 2  + test3  + spider +   1    +
+----+--------+--------+--------+
+ 3  + test4  + spider +   1    +
+----+--------+--------+--------+

You can sort and update the second table based on the validated user credential from the first table.

[tanklessgunit]

The ID is set as A I
I got this every time but I need your example.

    +----+-----------------+--------+----------+
    + ID + ITEM            + GAME   + LOG_ID   +
    +----+-----------------+--------+----------+
    + 1 + test test2 test3 + spider +      1   +
    +----+-----------------+--------+----------+

[tanklessgunit]

Ok! I got them now among each other! But there is still one problem! If certain monster has 3 items, he only adds 2 among each other the first one he doesnot insert into database!

$sql = "INSERT INTO log_create(`name`, name2, game, monster, info)VALUES('$name', '$name2', '$game', '$mobname', '$info')";

    if($result=$db->query($sql))
    {
        $log = $db->query("SELECT itemname FROM `log_mitem` WHERE mobname = '" .$mobname. "' AND game = '" .$game. "'") or die($db->error);
        if($log1 = $log->fetch_object());
        {
            while($loco = $log->fetch_object())
        {
            $item = "$loco->itemname";
            $logss = "INSERT INTO log_drops(`item`, mobname, game, log_id, log_name)VALUES('$item', '$mobname', '$game', '$id', '$name')";
            if($result1 = $db->query($logss)); 
        }
        }
    echo '<p>';
    echo 'Your droplog has been created! Check your droplog category to start hunting!';
    echo '</p>';
    } else { echo 'Something went wrong!';
    }

[veedeoo]

certain monster has 3 items

check the varchar(xx) value on your database structure. Is it enough for monster name characters?