doudi2520
2018-04-02 07:55
浏览 59
已采纳

不能用php将json_encode插入mysql

im going to insert json_encode which is $repeats to mysql, but i got this error

i want to save something like this in my table {"Monday":"y","Tuesday":"n","Wendesday":"y",...}

SQL syntax error.
INSERT INTO classes SET class_id = "9560326485ac1e0da1111b5.56470604", owner = "asd", created = NOW(), lesson_id = "323795a93ef320371c6.17773953", school_id = "1", subject = "1", teacher_id = "307285a93a6c4a0d208.30553961", description = "asd", start_datetime = "2018/04/17 15:00", end_time = "15:30", end_date = "2018/04/25", repeats = "{"Monday":"n","Tuesday":"y","Wednesday":"n","Thursday":"n","Friday":"n","Saturday":"n","Sunday":"n"}";
MySQL says: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Monday":"n","Tuesday":"y","Wednesday":"n","Thursday":"n","Friday":"n","Saturday"' at line 1

here is my code

$repeats = json_encode(array('Monday' => $this->monday,
        'Tuesday' => $this->tuesday,
        'Wednesday' => $this->wednesday,
        'Thursday' => $this->thursday,
        'Friday' => $this->friday,
        'Saturday' => $this->saturday,
        'Sunday' => $this->sunday ));

        $sql = 'INSERT INTO classes SET ';
        $sql .= 'class_id = "'.$this->class_id.'", ';
        $sql .= 'owner = "'.$this->owner.'", ';
        $sql .= 'created = NOW(), ';
        $sql .= 'lesson_id = "'.$this->lesson_id.'", ';
        $sql .= 'school_id = "'.$id.'", ';
        $sql .= 'subject = "'.$lesson['lesson_title'].'", ';
        $sql .= 'teacher_id = "'.$this->teacher_id.'", ';
        $sql .= 'description = "'.$this->description.'", ';
        $sql .= 'start_datetime = "'.$this->start_datetime.'", ';
        $sql .= 'end_time = "'.$this->end_time.'", ';
        $sql .= 'end_date = "'.$this->end_date.'", ';
        $sql .= 'repeats = "'.$repeats.'";';

        $this->db->query($sql);

Thank you

Here is my table structure Table structure for table classes

CREATE TABLE `classes` (
  `class_id` varchar(33) NOT NULL,
  `owner` varchar(33) NOT NULL,
  `created` datetime NOT NULL,
  `lesson_id` varchar(33) NOT NULL,
  `school_id` varchar(33) NOT NULL,
  `status` enum('Pending Schedule Replacement','Pending Teacher Replacement','Approved','Archived') NOT NULL DEFAULT 'Approved',
  `subject` varchar(100) NOT NULL,
  `teacher_id` varchar(33) NOT NULL,
  `start_datetime` datetime DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `end_date` date NOT NULL,
  `repeats` longtext,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` (`class_id`, `owner`, `created`, `lesson_id`, `school_id`, `status`, `subject`, `teacher_id`, `start_datetime`, `end_time`, `end_date`, `repeats`, `description`) VALUES
('1', 'budi', '2018-04-02 03:10:00', '1', '2', 'Approved', 'tes', '3', '2018-04-02 00:00:00', '05:00:00', '2018-04-21', '{\"Monday\":\"1\",\"Tuesday\":\"1\"}', 'hehehe'),
('13738279275ac1d9d18e65c3.96865776', 'qwe', '2018-04-02 14:20:49', '323795a93ef320371c6.17773953', '1', 'Approved', '1', '307285a93a6c4a0d208.30553961', '2018-04-02 14:00:00', '16:00:00', '2018-04-07', '}{}{}{', 'qqw'),
('15540181595ac1da25722f64.02626841', 'asdas', '2018-04-02 14:22:13', '235175a93e2ea3ac440.25406144', '1', 'Approved', 'lesson 1', '1', '2018-04-02 15:00:00', '16:30:00', '2018-04-11', '{\'Monday\':\'1\'}', 'q12');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `classes`
--
ALTER TABLE `classes`
  ADD PRIMARY KEY (`class_id`);
ALTER TABLE `classes` ADD FULLTEXT KEY `ft_idx` (`subject`,`description`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongpu1879 2018-04-02 12:24
    已采纳

    I have checked its working fine with me. Please see below code:

    <?php
    $requests = '{"Monday":"n","Tuesday":"y","Wednesday":"n","Thursday":"n","Friday":"n","Saturday":"n","Sunday":"n"}';
    $yourdate = "2018/04/17 15:00";
    $date = date('d-m-Y h:i',strtotime($yourdate));
    $query = "INSERT INTO classes SET 
        `class_id` = '9560326485ac1e0da1111b5.56470604', 
        `owner` = 'asd', 
        `created` = NOW(), 
        `lesson_id` = '323795a93ef320371c6.17773953', 
        `school_id` = '1', 
        `subject` = '1', 
        `teacher_id` = '307285a93a6c4a0d208.30553961', 
        `description` = 'asd', 
        `start_datetime` = '".$date."', 
        `end_time` = '15:30', 
        `end_date` = '2018/04/25', 
        `repeats` ='".$requests."'";
    
    $conn = mysqli_connect("localhost","root","","test");
    $ins = mysqli_query($conn,$query);
    
    ?>
    
    打赏 评论
  • douxinghuai3150 2018-04-02 08:28

    You can do below method: 1. Make sure you repeats column's datatype id text 2. Change the date format

    $data = jsone_decode($repeats);
    $yourdate = "2018/04/17 15:00";
    $date = date('d-m-Y h:i',strtotime($yourdate))
    $query = "INSERT INTO classes SET `class_id` = '9560326485ac1e0da1111b5.56470604', `owner` = 'asd', `created` = NOW(), `lesson_id` = '323795a93ef320371c6.17773953', `school_id` = '1', `subject` = '1', `teacher_id` = '307285a93a6c4a0d208.30553961', `description` = 'asd', `start_datetime` = '"$date"', `end_time` = '15:30', `end_date` = '2018/04/25', `repeats` = '{"Monday":"n","Tuesday":"y","Wednesday":"n","Thursday":"n","Friday":"n","Saturday":"n","Sunday":"n"}'";
    
    打赏 评论

相关推荐 更多相似问题