Multi query and auto increment?

Question

I'm having some real trouble figuring out this problem. The problem is in my database.php where I do all my database queries. This new function I'm creating have a foreach loop where it adds to a multi query and when the loop is finished all the queries get executed. (The multi query works in my other functions)

The MySQL table has a primary key id column with auto_increment and when the query tries to INSERT INTO it returns with an error.

So I leave out the id in my query to simple let MySQL take care of it with the auto increment. This is very the trouble starts and the multi query wont execute and tells me there is an error. if I simply fill out the id with my own values its fine.

database.php looks something like this

    $event .= "INSERT INTO table_info VALUES ('$userid', '$round', '$event');";

    // Execute multi query
    if (!$this->connection->multi_query($event)) {
        echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
    }

    do {
        if ($res = $this->connection->store_result()) {
            var_dump($res->fetch_all(MYSQLI_ASSOC));
            $res->free();
        }
    } while ($this->connection->more_results() && $this->connection->next_result());
    return;

The odd thing is when I add the $round where the auto_increment id should be it works.

    $event .= "INSERT INTO table_info VALUES ('$round', '$userid', '$round', '$event');";

It seems like a waste to load the table and get the latest id and then +1 just to fill it into the query.

• Thank you for your time

Answer 1

$event .= "INSERT INTO table_info (`user_id_column_name`,`round_column_name`,`event_column_name`) VALUES ('$userid', '$round', '$event');";

You need to specify the columns if the no of values are less than total no of fields