I Googled and tested but can not get my below php script to insert into my MySQL database. When I run the query directly in my mysql management tool the INSERT works. I want to INSERT the query into multiple rows in the table task_in.
I tested my database/table connection and it is ok, so can someone please explain why my php script do not insert any data in my database table task_in?
PHP SCRIPT:
<?php
if(isset($_POST['tasks'])) {
$tasks = $_POST['tasks'];
foreach ($tasks as $task_list_id) {
$sql = mysql_query("INSERT INTO task_in (task_list_id, customer_id, user_id, created_at)
VALUES ('$task_list_id', '1', '1', NOW())");
print_r($task_list_id);
}
}
?>
Part of my form:
<td><input type="checkbox" name="tasks[][task_list_id]" value=<?php echo($key[0]);?>></td>
<td><input type="checkbox" name="tasks[][task_list_id]" value=<?php echo($key[1]);?>></td> </tr>
<td>Nav.rep For</td>
The print_r in the foreach loop returns this:
Array
(
[task_list_id] => 1
)
Array
(
[task_list_id] => 2
)
Array
(
[task_list_id] => 8
)
My table task_in looks like this:
CREATE TABLE `task_in` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`task_id_unique` int(11) NOT NULL,
`task_list_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `taskListId` (`task_list_id`),
CONSTRAINT `taskListId` FOREIGN KEY (`task_list_id`) REFERENCES `task_list` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;
Ps. I am fully aware of sql injections in my above code + PDO and Mysqli and it will be changed asap. So please do not comment on this.
UPDATE SOLVED.
@axiac suggestions works and also as commented by @fejese, my table which have a foreign key taks_list_id was expecting INT and got a string, and therefore it returned the following sql error:
Invalid query: Cannot add or update a child row: a foreign key constraint fails (workcard.task_in, CONSTRAINT taskListId FOREIGN KEY (task_list_id) REFERENCES task_list (id))
I added the implode function to my php script and this php script solved the insert issue:
<?php
if(isset($_POST['tasks'])) {
$tasks = $_POST['tasks'];
foreach ($tasks as $task_list_id) {
$values = implode('""', $task_list_id);
$sql = mysql_query("INSERT INTO task_in (task_list_id, customer_id, user_id, created_at)
VALUES ('$values', '1', '1', NOW())");
}
if (!$sql) {
die('Invalid query: ' . mysql_error());
}
}
?>
Thank you all for your suggestion and comments.
mysql_errorand for starters do not use themysql_*functions at all, I'm guessing you have an SQL error because oftask_id_uniqueint(11) NOT NULL` not being inserted with your query$sqlvariable from within theforeachloop. To see exactly what is being generated as the query statement.