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.
$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
What you need to do is as you are not inserting into every column on the table - as you are correctly avoiding race conditions by letting MySQL handle this with the auto_increment column - you need to define on your insert which columns you are inserting into:
INSERT INTO table_info (column names here separated by commas)
VALUES ( '$userid', '$round', '$event');
If you don't want to specify columns names and allow mysql to take care of AUTO_INCREMENT your id field you should send it with NULL value. See code below:
$event .= "INSERT INTO table_info VALUES (NULL, '$userid', '$round', '$event');";