SQL insert and update doesn't update or insert all the records, only a few record

Question

My php code:

    for($i=0;$i<num_rows($sql);$i++)
        if(isset($_POST['knight'.$i]))
        {
            $data=
            [
                $pm_id=$_POST['pm_id'.$i],
                $knight_id=$_POST['knight'.$i],
            ];
            mysql_query("update `project_waiting` set `chosen`=(b'1') where `pm_id`='$pm_id' and `knight_id`='$knight_id';");
            mysql_query("insert into `project_working`(`pm_id`,`knight_id`) values('$pm_id','$knight_id');");
        }

when I use echo instead of mysql_query it returns:

update `project_waiting` set `chosen`=(b'1') where `pm_id`='P1_1' and `knight_id`='K1';
insert into `project_working`(`pm_id`,`knight_id`) values('P1_1','K1');
update `project_waiting` set `chosen`=(b'1') where `pm_id`='P1_1' and `knight_id`='K2';
insert into `project_working`(`pm_id`,`knight_id`) values('P1_1','K2');
update `project_waiting` set `chosen`=(b'1') where `pm_id`='P1_1' and `knight_id`='K3';
insert into `project_working`(`pm_id`,`knight_id`) values('P1_1','K3');
update `project_waiting` set `chosen`=(b'1') where `pm_id`='P1_1' and `knight_id`='K4';
insert into `project_working`(`pm_id`,`knight_id`) values('P1_1','K4');
update `project_waiting` set `chosen`=(b'1') where `pm_id`='P1_2' and `knight_id`='K1';
insert into `project_working`(`pm_id`,`knight_id`) values('P1_2','K1');
update `project_waiting` set `chosen`=(b'1') where `pm_id`='P1_2' and `knight_id`='K4';
insert into `project_working`(`pm_id`,`knight_id`) values('P1_2','K4');
update `project_waiting` set `chosen`=(b'1') where `pm_id`='P1_3' and `knight_id`='K2';
insert into `project_working`(`pm_id`,`knight_id`) values('P1_3','K2');

It runs perfectly on my phpmyadmin, but when I change echo to mysql_query it only affect 5 rows (both tables)

P1_1-K1

P1_1-K4

P1_1-K3

P1_1-K2

P1_2-K1

My full database

-- phpMyAdmin SQL Dump

-- version 4.3.11

-- http://www.phpmyadmin.net

-- Host: 127.0.0.1 -- Generation Time: Jul 23, 2015 at 12:16 PM -- Server version: 5.6.24 -- PHP Version: 5.6.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00";

--

-- Database: `knightit`

CREATE DATABASE IF NOT EXISTS knightit DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; USE knightit;

--

-- Table structure for table `admin`

DROP TABLE IF EXISTS admin; CREATE TABLE IF NOT EXISTS admin ( id varchar(16) COLLATE utf8_unicode_ci NOT NULL, password binary(60) NOT NULL, level int(2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `employers`

DROP TABLE IF EXISTS employers; CREATE TABLE IF NOT EXISTS employers ( emp_id varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', password longtext COLLATE utf8_unicode_ci NOT NULL, email varchar(32) COLLATE utf8_unicode_ci NOT NULL, emp_name text COLLATE utf8_unicode_ci, coins bigint(20) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `knights`

DROP TABLE IF EXISTS knights; CREATE TABLE IF NOT EXISTS knights ( knight_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, password longtext COLLATE utf8_unicode_ci NOT NULL, knight_name text COLLATE utf8_unicode_ci, email varchar(32) COLLATE utf8_unicode_ci NOT NULL, s_id varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, points int(11) DEFAULT '0', coins int(32) NOT NULL DEFAULT '0', status bit(2) DEFAULT b'1', bio longtext COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `message`

DROP TABLE IF EXISTS message; CREATE TABLE IF NOT EXISTS message ( m_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, content text COLLATE utf8_unicode_ci NOT NULL, send_date date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `message_send`

DROP TABLE IF EXISTS message_send; CREATE TABLE IF NOT EXISTS message_send ( m_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, receiver_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, status bit(1) NOT NULL DEFAULT b'0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `projects`

DROP TABLE IF EXISTS projects; CREATE TABLE IF NOT EXISTS projects ( pro_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, pro_name text COLLATE utf8_unicode_ci NOT NULL, emp_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, p_describe text COLLATE utf8_unicode_ci NOT NULL, price int(32) NOT NULL, cut int(32) NOT NULL, s_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, status bit(2) NOT NULL DEFAULT b'1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `project_milestone`

DROP TABLE IF EXISTS project_milestone; CREATE TABLE IF NOT EXISTS project_milestone ( pm_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, pro_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, milestone int(2) NOT NULL, job text COLLATE utf8_unicode_ci, deadline int(3) DEFAULT NULL, knight_amt int(2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `project_waiting`

DROP TABLE IF EXISTS project_waiting; CREATE TABLE IF NOT EXISTS project_waiting ( pm_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, knight_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, chosen bit(1) NOT NULL DEFAULT b'0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `project_working`

DROP TABLE IF EXISTS project_working; CREATE TABLE IF NOT EXISTS project_working ( pm_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, knight_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, submit date DEFAULT NULL, approve tinyint(1) DEFAULT NULL, paid tinyint(1) DEFAULT NULL, coins_paid int(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `security`

DROP TABLE IF EXISTS security; CREATE TABLE IF NOT EXISTS security ( name varchar(100) COLLATE utf8_unicode_ci NOT NULL, value longtext COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Table structure for table `skills`

DROP TABLE IF EXISTS skills; CREATE TABLE IF NOT EXISTS skills ( s_id varchar(16) COLLATE utf8_unicode_ci NOT NULL, skill_name text COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

-- Indexes for dumped tables

--

-- Indexes for table `admin`

ALTER TABLE admin ADD PRIMARY KEY (id);

--

-- Indexes for table `employers`

ALTER TABLE employers ADD PRIMARY KEY (emp_id);

--

-- Indexes for table `knights`

ALTER TABLE knights ADD PRIMARY KEY (knight_id), ADD KEY s_id (s_id);

--

-- Indexes for table `message`

ALTER TABLE message ADD PRIMARY KEY (m_id);

--

-- Indexes for table `message_send`

ALTER TABLE message_send ADD KEY m_id (m_id);

--

-- Indexes for table `projects`

ALTER TABLE projects ADD PRIMARY KEY (pro_id), ADD KEY emp_id (emp_id), ADD KEY s_id (s_id);

--

-- Indexes for table `project_milestone`

ALTER TABLE project_milestone ADD PRIMARY KEY (pm_id), ADD KEY pm_id (pm_id,pro_id,milestone), ADD KEY project_milestone_ibfk_1 (pro_id);

--

-- Indexes for table `project_waiting`

ALTER TABLE project_waiting ADD PRIMARY KEY (knight_id,pm_id), ADD KEY knight_id (knight_id), ADD KEY pm_id (pm_id);

--

-- Indexes for table `project_working`

ALTER TABLE project_working ADD PRIMARY KEY (pm_id,knight_id), ADD KEY pm_id (pm_id,knight_id), ADD KEY knight_id (knight_id);

--

-- Indexes for table `security`

ALTER TABLE security ADD PRIMARY KEY (name);

--

-- Indexes for table `skills`

ALTER TABLE skills ADD PRIMARY KEY (s_id);

--

-- Constraints for dumped tables

--

-- Constraints for table `knights`

ALTER TABLE knights ADD CONSTRAINT knights_ibfk_1 FOREIGN KEY (s_id) REFERENCES skills (s_id) ON DELETE CASCADE ON UPDATE CASCADE;

--

-- Constraints for table `message_send`

ALTER TABLE message_send ADD CONSTRAINT message_send_ibfk_1 FOREIGN KEY (m_id) REFERENCES message (m_id) ON DELETE CASCADE ON UPDATE CASCADE;

--

-- Constraints for table `projects`

ALTER TABLE projects ADD CONSTRAINT projects_ibfk_1 FOREIGN KEY (emp_id) REFERENCES employers (emp_id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT projects_ibfk_2 FOREIGN KEY (s_id) REFERENCES skills (s_id) ON DELETE CASCADE ON UPDATE CASCADE;

--

-- Constraints for table `project_milestone`

ALTER TABLE project_milestone ADD CONSTRAINT project_milestone_ibfk_1 FOREIGN KEY (pro_id) REFERENCES projects (pro_id);

--

-- Constraints for table `project_waiting`

ALTER TABLE project_waiting ADD CONSTRAINT project_waiting_ibfk_1 FOREIGN KEY (pm_id) REFERENCES project_milestone (pm_id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT project_waiting_ibfk_2 FOREIGN KEY (knight_id) REFERENCES knights (knight_id) ON DELETE CASCADE ON UPDATE CASCADE;

--

-- Constraints for table `project_working`

ALTER TABLE project_working ADD CONSTRAINT project_working_ibfk_1 FOREIGN KEY (pm_id) REFERENCES project_milestone (pm_id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT project_working_ibfk_2 FOREIGN KEY (knight_id) REFERENCES knights (knight_id) ON DELETE CASCADE ON UPDATE CASCADE;

yes, I update the project_waiting then insert a new in project_working — Red Wei
Do you use the same user for PHPMyAdmin and your PHP-code? Your database user has sufficient rights for updating queries? — Stefan Herijgens
when I run it on SQL command from localhost/phpmyadmin. It runs perfectly. I use XAMPP 5.6.8 , PHP 5.6.8, MYSQL 5.0.11 — Red Wei

Red Wei Red Wei · Accepted Answer · 2015-07-26T07:04:18

I have solved the problem with combine all of them into one query:

insert into `project_working`(`pm_id`,`knight_id`) values('P1_1','K1'),('valueX','valueY');
update set `chosen`=true where (`pm_id`='P1_3' and `knight_id`='K2') or (`pm_id`='P1_1' and `knight_id`='K1') or(...);

SQL insert and update doesn't update or insert all the records, only a few record

-- http://www.phpmyadmin.net

-- Database: knightit

-- Table structure for table admin

-- Table structure for table employers

-- Table structure for table knights

-- Table structure for table message

-- Table structure for table message_send

-- Table structure for table projects

-- Table structure for table project_milestone

-- Table structure for table project_waiting

-- Table structure for table project_working

-- Table structure for table security

-- Table structure for table skills

-- Indexes for dumped tables

-- Indexes for table admin

-- Indexes for table employers

-- Indexes for table knights

-- Indexes for table message

-- Indexes for table message_send

-- Indexes for table projects

-- Indexes for table project_milestone

-- Indexes for table project_waiting

-- Indexes for table project_working

-- Indexes for table security

-- Indexes for table skills