Most Efficient Way to Change PHP Variable in SQL Query?

Question

I have a HTML table that displays a bunch of reporting data from a sql table. Right now the query defaults to the current date, but I'm looking for the most efficient way to change the start/end date variables and refresh the page. My query now looks like this:

$sts = date('Y-m-d');
$ets = date('Y-m-d', time()+86400);
$q = "SELECT *  FROM data WHERE `ts` BETWEEN '$sts' AND '$ets'";

I've then created basic HTML selects to choose a new date range, and on the form submit it'll store all of that information in POST. What is the most simple way of changing that $sts and $ets variable to reflect the post information? I'm kind of guessing that it will be to make the form action 'index.php', and handle the variable change using AJAX. But tbh I'm not sure.

Thank you for the help!

Answer 1

try

$sts="";
$ets="";
if (!isset($_POST['start_date']))
    $sts = date('Y-m-d');
else
    $sts = $_POST['start_date'];
if (!isset($_POST['end_date']))
    $ets = date('Y-m-d', time()+86400);
else
    $ets = $_POST['end_date'];

$q = "SELECT *  FROM data WHERE `ts` BETWEEN '$sts' AND '$ets'";`

and include a form to change the date.

This way, when the form first loads, there is no post data and it will load the default date range. When the user changes the date range in the form and submit, it will load the new range.

before you use the information you get from http get or post you should perform a

mysqli_real_escape_string i.e.

$q = "SELECT *  FROM data WHERE `ts` BETWEEN 'mysqli_real_escape_string($sqlconn,$sts)' AND 'mysqli_real_escape_string($sqlconn,$ets)'";`

Answer 2

If you're trying to adjust the range based on user input, you'd probably be looking to incorporate arguments from $_GET or $_POST depending on how you're activating this. Typically $_GET is easier to handle.

If you're working with date ranges, you can send in a UNIX timestamp number and use that to calculate the offsets.

Then your query would look something like this:

$start_t = date('%Y-%m-%d', $_GET['from']);
$end_t = date('%Y-%m-%d', $_GET['from'] + 86400);

$sth = $db->prepare("SELECT * FROM DATA WHERE `ts` BETWEEN :start_t AND :end_t");
$sth->bind(array('start_t' => $start_t, 'end_t' => $end_t));

Answer 3

First, you need to look into using prepared statements for your queries either with PDO or mysqli (assuming MySQL usage).

Next, just need to rerun the query you already have but instead of hardcoding the values into the variables, pull them out of $_POST

Example (you can substitue $_GET for this if needed):

$sts = isset($_POST['sts']) ? htmlspecialchars($_POST['sts'],ENT_QUOTES) : date('Y-m-d');
$ets = isset($_POST['ets']) ? htmlspecialchars($_POST['ets'],ENT_QUOTES) : date('Y-m-d', time()+86400);
$q = "SELECT *  FROM data WHERE `ts` BETWEEN ? AND ?";
$stmt = $db->prepare($q);
$stmt->execute(array($sts,$ets);

See http://www.php.net/manual/en/pdo.prepare.php and http://www.php.net/manual/en/pdostatement.execute.php for starters

Answer 4

Okay sorry, I misunderstood the question. You could do it via get variables:

http://website.com/index.php?sts=2013-02-03&ets=2013-02-05

$sts = strtotime(mysqli_real_escape_string($_GET['sts']));
$ets = strtotime(mysqli_real_escape_string($_GET['ets']));
$q = "SELECT *  FROM data WHERE `ts` BETWEEN '$sts' AND '$ets'";

Then use ajax calls to change the content in your table.