douba9776 2019-02-05 08:15
浏览 163

如何在date_format中替换sprintf中的多个%s?

I am trying to replace all the %s in the query with the given days. But it throws me and error at the Date_format. The %s in the fcst_date is getting replaced correctly. Please help

    $day1 = date('Y-m-d');
    $day2 = date('Y-m-d', strtotime("+1 days"));
    $day3 = date('Y-m-d', strtotime("+2 days"));
    $day4 = date('Y-m-d', strtotime("+3 days"));
    $day5 = date('Y-m-d', strtotime("+4 days"));
    $day6 = date('Y-m-d', strtotime("+5 days"));
    $day7 = date('Y-m-d', strtotime("+6 days"));
    $day8 = date('Y-m-d', strtotime("+7 days"));
    $day9 = date('Y-m-d', strtotime("+8 days"));
    $day10 = date('Y-m-d', strtotime("+9 days"));
    $query = sprintf('SELECT blk_id,blk_name,fcst_date,temp_max,max_temp,date,temp_stn_block.stn_id FROM temp_stn_block RIGHT JOIN temp_stn_normals ON temp_stn_block.stn_id=temp_stn_normals.stn_id INNER JOIN block_imd_gfs_forecast ON blk_id=block_id JOIN block_s ON block_s.id=blk_id WHERE DATE_FORMAT(date, "%%m-%%d") IN DATE_FORMAT("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s", "%%m-%%d") AND fcst_date IN (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", $day1, $day2, $day3, $day4,$day5, $day6, $day7,$day8,$day9,$day10, $day1, $day2, $day3, $day4,$day5, $day6, $day7,$day8,$day9,$day10);
    $res = $this->db->query($query);
    return $res->result();
  • 写回答

1条回答 默认 最新

  • donglian4879 2019-02-05 09:24
    关注

    I would rather used prepared statements with named param for this just for a more readable code. But then you would have to update your codeigniter db config to use PDO driver.

    For sprintf, just used numbered placeholders:

    
    $qry_str = 'SELECT 
     blk_id,blk_name,fcst_date,temp_max,max_temp,date,temp_stn_block.stn_id 
     FROM temp_stn_block 
     RIGHT JOIN temp_stn_normals 
       ON temp_stn_block.stn_id=temp_stn_normals.stn_id 
     INNER JOIN block_imd_gfs_forecast 
       ON blk_id=block_id JOIN block_s ON block_s.id=blk_id 
     WHERE 
       DATE_FORMAT(date, "%%m-%%d") BETWEEN *day1* AND *day10*
     AND 
       fcst_date IN (%1$s, %2$s, %3$s, %4$s, %5$s, %6$s, %7$s, %8$s, %9$s, %10$s)"
    ';
    
    $query = sprintf($qry_str, $day1, $day2, $day3, $day4, $day5, $day6, $day7, $day8, $day9, $day10);
    
    $res = $this->db->query($query);
    
    

    I think the issue with your query is the usage of %s which is a specifier for 'seconds' in DATE_FORMAT

    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题