0

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.

4
  • 2
    Although you should include the error message reported by mysql_error and for starters do not use the mysql_* functions at all, I'm guessing you have an SQL error because of task_id_unique int(11) NOT NULL` not being inserted with your query Commented Nov 27, 2014 at 11:39
  • '$task_list_id' change to '".$task_list_id."' Commented Nov 27, 2014 at 11:40
  • To debug this properly you need to echo out the $sql variable from within the foreach loop. To see exactly what is being generated as the query statement. Commented Nov 27, 2014 at 11:41
  • ... @fejese you are right. See my update. Commented Nov 27, 2014 at 13:10

2 Answers 2

3

Your $task_list_id is an array and it should probably be an integer. Just change the HTML to look like this:

<input type="checkbox" name="tasks[]" ...>

and that will fix it.

Sign up to request clarification or add additional context in comments.

Comments

0

I think $task_list_id should be $task_list_id['task_list_id'] in VALUES section of query as it must not be an array.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.