How to replace the multiple %s inside sprintf in a date_format?

Question

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();

A few things - there is a missing `'` at the end of the string part in sprintf. You may be better off using SQL BETWEEN rather than the way your doing it and Please use prepared statements. — Nigel Ren, Feb 05 '19 at 08:19
Not sure which part you mean but - https://stackoverflow.com/questions/15438230/php-mysql-search-between-two-dates and https://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php — Nigel Ren, Feb 05 '19 at 08:27
I mean how to change my query to the one that u r telling me to use. i need to show the data of the the 10 dates. can u edit my query?? — user7500984, Feb 05 '19 at 08:30

xDiff · Answer 1 · 2019-02-05T13:38:09.533

0

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

edited Feb 05 '19 at 13:38

answered Feb 05 '19 at 09:24

xDiff

691
1
5
5

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_FORMAT("2019-02-05, 2019-02-06, 2019-02-07, 2019-02-08, 2019-02-09, 2019-02' i got ths error after using ur query xDiff – user7500984 Feb 05 '19 at 09:51
Oops, my bad, I overlooked in that `DATE_FORMAT` only accepts two parameters. You should use BETWEEN *day1* AND *day10* instead, updated the query – xDiff Feb 05 '19 at 13:40
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near * day1 * AND * day10 * now this is the error i m getting. what is the meaning of * character ? xDiff – user7500984 Feb 05 '19 at 14:29
I have used WHERE DATE_FORMAT(date, "%%m-%%d") BETWEEN DATE_FORMAT("%1$s", "%%m-%%d") AND DATE_FORMAT("%10$s", "%%m-%%d") but it is returning me just the last date not the range of dates – user7500984 Feb 06 '19 at 04:05

How to replace the multiple %s inside sprintf in a date_format?

1 Answers1