PHP - Can This Be Improved?
Hi,
Im currently using the following code, to list 'Todays 5', however can the following be improved im uneasy with using php for the date comparison - perhaps have it within the sql? DB Structu I have a column named 'flash_date' (and the type is: varchar(32)) which contains a timestamp generated by time() $yesterday = strtotime('-1 day'); $time = time(); $result = mysql_query("SELECT * FROM site_flash WHERE flash_date > '{$yesterday}' AND flash_date < '{$time}' DESC LIMIT 5"); Similar TutorialsI've used a showQuery() method for years which accepted a SQL string and data array. Sometimes, I just have the PDO statement and I need to add temporary script just to keep track of the SQL string. Was looking at debugDumpParams() today which prompted me to create the new showStmtQuery() method. My approach of extracting the table from debugDumpParams's printed results is a little clunky. Any recommendations? Could/should other information from debugDumpParams's output be used? I typically don't use bound parameters but know I should more often. Suggestions how this could be changed to work with bound parameters? Thanks
function showQuery(string $sql, arrray $data, bool $keepLineBreaks=false):string { $keys = []; $values = []; foreach ($data as $key=>$value) { $keys[] = is_string($key)?'/:'.$key.'/':'/[?]/'; $values[] = is_numeric($value)?$value:"'$value'"; } $sql = preg_replace($keys, $values, $sql, 1, $count); return $keepLineBreaks?$sql:str_replace(array("\r", "\n"), ' ', $sql); } function showStmtQuery(\PDOStatement $stmt, array $data, bool $keepLineBreaks=false):string { ob_start(); $stmt->debugDumpParams(); $sql = ob_get_contents(); ob_end_clean(); //$sql = strtok(substr($sql, strpos($sql, '] ')+2), "\n"); $start=strpos($sql, '] ')+2; $sql = substr($sql, $start, strpos($sql, "\nParams:") - $start); return showQuery($sql); } $data=[5,50]; $sth = $pdo->prepare('SELECT * FROM my_table WHERE id > ? AND id < ?'); echo(showStmtQuery($sth, $data).PHP_EOL); //SELECT * FROM my_table WHERE id > 5 AND id < 50 $data=['lowId' => 5, 'highId' => 50]; $sth = $pdo->prepare('SELECT * FROM my_table WHERE id > :lowId AND id < :highId'); echo(showStmtQuery($sth, $data).PHP_EOL); //SELECT * FROM my_table WHERE id > 5 AND id < 50
Changed to use original showQuery function |