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

不能用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);
    
    ?>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 计算300m以内的LD衰减
  • ¥15 Lxml库中Xpath查找指定节点
  • ¥15 数据爬取,python
  • ¥15 怎么看 cst中一个面的功率分布图,请说明详细步骤。类似下图
  • ¥15 为什么我的pycharm无法用pyqt6的QtWebEngine
  • ¥15 FOR循环语句显示查询超过300S错误怎么办
  • ¥15 数电设计题 没有设计思路 不知道用什么芯片进行设计 求提供设计思路
  • ¥60 设计一种优化算法结合案例给出智能仓储四向穿梭车的调度计划
  • ¥15 Errno2:No such file or directory,在当前文件确实没有该图片,怎么解决?
  • ¥15 博世摄像头数据存储的问题(iscsi)