Ошибка 1366 phpmyadmin

Summary

When I use the phpMyAdmin GUI to insert a new entry into my table (which has a BEFORE INSERT TRIGGER applied to it), it seems to insert the entry just fine… but it always displays this in response:

( ! ) 1 row inserted.
Warning: #1366 Incorrect integer value: » for column ‘line_id’ at row 1

What am I doing wrong? Is there a better way to set up the trigger so I don’t get the error?

Background

Note: You can probably skip the code blocks as you read.

Using phpMyAdmin, I created my second table with this SQL statement. (This worked fine.)

CREATE TABLE IF NOT EXISTS `myDb`.`line_item` (
  `order_id` INT NOT NULL,
  `line_id` INT NOT NULL,
  `line_text` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`order_id`, `line_id`),
  CONSTRAINT `FK_order_line_item`
    FOREIGN KEY (`order_id`)
    REFERENCES `myDb`.`order` (`order_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

You’ll note there’s no AUTO_INCREMENT imposed on line_id. That is because we want it to reset it’s numbering with each new order_id. To accomplish this resetting number, we presumed a TRIGGER was most appropriate for the task.

When I tried to add a TRIGGER with this code, phpMyAdmin said it couldn’t make it. (Something to do about «permissions» or such, but I quickly resorted to a built-in workaround after this little adventure.)

DELIMITER $$
USE `myDb`$$
CREATE DEFINER = CURRENT_USER TRIGGER `myDb`.`line_id_incrementer` 
BEFORE INSERT ON `line_item` 
FOR EACH ROW
BEGIN
    DECLARE i INT;
    SELECT  COALESCE(MAX(line_id), 0) + 1
    INTO    i
    FROM    line_item
    WHERE   order_id = NEW.order_id;
    SET NEW.line_id = i;
END$$

DELIMITER ;

When the above SQL statement didn’t work, I simply used the GUI of phpMyAdmin to add the trigger to the table.

Server:localhost > Database:myDb > Table:line_item > «Triggers» Tab > «New» > «Add Trigger»

Trigger Name: line_id_incrementer
Table: line_item
Time: BEFORE
Event: INSERT
Definition:

BEGIN
    DECLARE i INT;
    SELECT  COALESCE(MAX(line_id), 0) + 1
    INTO    i
    FROM    line_item
    WHERE   order_id = NEW.order_id;
    SET NEW.line_id = i;
END

So far so fair.

Performing a practice run, I inserted a test entry into the ‘order’ table via the phpMyAdmin GUI (the ‘Insert’ Tab while viewing the ‘order’ table.): No problems there.

When I inserted a test entry for ‘line_id’ via the phpMyAdmin GUI, I left the NOT NULL ‘line_id’ field empty, to see if the trigger would fill it in correctly for me. And that’s when I got this:

( ! ) 1 row inserted.
Warning: #1366 Incorrect integer value: » for column ‘line_id’ at row 1

With the generated code shown as:

INSERT INTO `line_item` 
    (`order_id`, `line_id`, `line_text`) 
    VALUES ('1', '', 'This is a test line for the incrementer trigger');

What is interesting is: It inserted the entry as expected (with a 1 as the line_id). When I inserted a second entry, the warning still showed, but the next entry was also entered as expected (with a 2 as the line_id).

So, the rows seem to be inserted just fine, but I keep getting that nagging Warning which makes me suspect I didn’t do something up to «Best Practice Standards».

Hey I keep getting the following error when completing a form on the site I am building and cannot figure out why.

Database Error
Error: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: ' ' for column 'product_id' at row 1

SQL Query: INSERT INTO `ahbtest`.`user_products` (`product_id`, `user_id`) VALUES (' ', 8)

Notice: If you want to customize this error message, create app\View\Errors\pdo_error.ctp

Here is the code for my form’s view:

<form method="post" action="/admin/users/add_favorites<?= isset($userId) ? "/" . $userId  : ""; ?>" enctype="multipart/form-data">
    <div class='row form-group'>
        <div class="col-md-12">
            <h3>Products</h3>
                <?php foreach($products as $product):
                    ?>
            <div class="col-md-3">
                <input id="checkBox"  name='data[Product][]' value=" <?php $product['Product']['id']; ?>" type="checkbox">
            <label><?php echo $product['Product']['name']; ?></label>
            </div>
                <?php
                endforeach; ?>
        </div>
    </div>



<?php // echo $this->Form->hidden('Product.id'); ?>
<?php // echo $this->Form->hidden('Product.parent_id', array('value' => $parentId)); ?>
<button type="submit" class="btn btn-primary"><i class="fa fa-check"></i> Save Changes</button>

</form>

And here is the code for my form’s controller:

public function admin_add_favorites($userId = null){

    ini_set('memory_limit', '2G');

        if ($this->request->is('post')) {

            $error = false;
            $this->loadModel('UserProduct');
            if(!isset($userId))
                $userId = $this->Auth->user('id');
            foreach($this->request->data['Product'] as $product)
            {
                $this->UserProduct->create();

                if(!$this->UserProduct->save(array('product_id' => $product, 'user_id' => $userId)))
                        $error = true;
            }
             if (!$error) {
                $this->Session->setFlash("Favorites have been successfully added.", 'flash_success');
                return $this->redirect('/admin/users/favorites');
            }
            else {
                   $this->Session->setFlash("One or more errors occurred. Please try again.", 'flash_error');
            }
        }
        if(isset($userId))
            $this->set('userId', $userId);
        $this->loadModel('Product');
        //$this->set('products', $this->Product->find('list', array('fields' => array('id', 'name'))));
        $product = $this->Product->find('all', array('fields' => array('id', 'name')));
        $this->set('products', $product);
          //pr($product); 
    }

Any idea why this would be happening or how to stop it? I can attach the structure of my database tables if needed.