if elseif indicates that the input field strength uses the option value

advertisements

First of all, my select option is working but my input field is not working when I submit. I'm using if else if statement to query my database based on the option.

I suspect this part elseif not working:

elseif ($option == "") {
    $start_date = $_POST['start'];
    $end_date = $_POST['end'];
    $start = DateTime::createFromFormat('d-m-Y', $start_date);
    $end = DateTime::createFromFormat('d-m-Y', $end_date);
    $query = $db->prepare("SELECT * FROM log WHERE log_datetime >= :start AND log_datetime < :end + INTERVAL 1 DAY;");
    $success = $query->execute(array(':start'=>$start->format('Y-m-d'), ':end'=>$end->format('Y-m-d')));
} else {
    echo "No Data Input";
}

But for sure my query definately work because I tested in a separate file, but when I combine both option and input, the input couldn't work and it automatic go to today option whch is the first if statement.

Here is my form code:

<form method="post" action="result.php">
    <select name="report">
    <option id="">Select Report Time</option>
    <option id="today">Today</option>
    <option id="yesterday">Yesterday</option>
    <option id="thisweek">This Week</option>
    <option id="thismonth">This Month</option>
    <option id="thisyear">This Year</option>
    <option id="lastweek">Last Week</option>
    <option id="lastmonth">Last Month</option>
    <option id="lastyear">Last Year</option>
</select><br />
From: <input type="text" name="start" /><br />
End: <input type="text" name="end" /><br />
<input type="submit" value="Go" />
</form>

And here is my all result.php php code excluding foreach :

if(empty($_POST['option']) && empty($_POST['start']) && empty($_POST['end'])) {
   echo 'Please don'."'".'t try to hack!<br /><a href="./" title="Retry">Click here to <b>retry</b> again</a>';
   exit;
}
$option = $_POST['report'];

if($option == "Today") {
    $query = $db->prepare("SELECT * FROM log WHERE log_datetime >= CURDATE();");
    $success = $query->execute();
    $startreportdate = date('d - M - Y');
    $endreportdate = date('d - M - Y');
} elseif ($option == "Yesterday") {
    $query = $db->prepare("SELECT * FROM log WHERE log_datetime >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND log_datetime < CURDATE();");
    $success = $query->execute();
    $startreportdate = date('d - M - Y', strtotime("-1 days"));
    $endreportdate = date('d - M - Y', strtotime("-1 days"));
} elseif ($option == "This Week") {
    $query = $db->prepare("SELECT * FROM log WHERE log_datetime >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 0 DAY);");
    $success = $query->execute();
    $startreportdate = date('d - M - Y');
    $endreportdate = date('d - M - Y', strtotime("+6 days"));
} elseif ($option == "This Month") {
    $query = $db->prepare("SELECT * FROM log WHERE log_datetime >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY);");
    $success = $query->execute();
    $startreportdate = date('d - M - Y', strtotime("first day of this month"));
    $endreportdate = date('d - M - Y', strtotime("last day of this month"));
} elseif ($option == "This Year") {
    $query = $db->prepare("SELECT * FROM log WHERE YEAR(log_datetime) = YEAR(CURDATE());");
    $startreportdate = date('d - M - Y', strtotime("first day of this month"));
    $endreportdate = date('d - M - Y', strtotime("last day of this year"));
    $success = $query->execute();
} elseif ($option == "Last Week") {
    $query = $db->prepare("SELECT * FROM log WHERE log_datetime >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND log_datetime < CURDATE();");
    $success = $query->execute();
    $startreportdate = date('d - M - Y', strtotime("-7 days"));
    $endreportdate = date('d - M - Y', strtotime("-1 days"));
} elseif ($option == "Last Month") {
    $query = $db->prepare("SELECT * FROM log WHERE YEAR(log_datetime) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(log_datetime) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH);");
    $success = $query->execute();
    $startreportdate = date('d - M - Y', strtotime("first day of last month"));
    $endreportdate = date('d - M - Y', strtotime("last day of last month"));
} elseif ($option == "Last Year") {
    $query = $db->prepare("SELECT * FROM log WHERE YEAR(log_datetime) = YEAR(CURDATE() - INTERVAL 1 YEAR);");
    $success = $query->execute();
    $startreportdate = date('d - M - Y', strtotime("first day of last year"));
    $endreportdate = date('d - M - Y', strtotime("last day of last year"));
} elseif ($option == "") {
    $start_date = $_POST['start'];
    $end_date = $_POST['end'];
    $start = DateTime::createFromFormat('d-m-Y', $start_date);
    $end = DateTime::createFromFormat('d-m-Y', $end_date);
    $query = $db->prepare("SELECT * FROM log WHERE log_datetime >= :start AND log_datetime < :end + INTERVAL 1 DAY;");
    $success = $query->execute(array(':start'=>$start->format('Y-m-d'), ':end'=>$end->format('Y-m-d')));
} else {
    echo "No Data Input";
}

$rows = $query->fetchAll(PDO::FETCH_OBJ);

May I know where did I go wrong? Sorry for my messy code, I'm still learning and I'm using SO and php manual as reference, but for this problem I search Google and SO high and low and yet I couldn't find the exact question that can help me and I have to ask the question.

Appreciate to everyone who help and take your time to read this question. Thank you.


Your immediate problem is caused by the fact that there is no $_POST['option'] in your request you should check for $_POST['report'] instead.

Now I'd suggest some improvements.

First of all change markup for select control in your html form by:

  • introducing a value attribute for options
  • adding a new option Custom to make it more obvious for user that he or she needs to choose your his or her own dates

That being said it might look like

<select name='report'>
    <option value=''>Select Report Time</option>
    <option value='today'>Today</option>
    <option value='yesterday'>Yesterday</option>
    <option value='thisweek'>This Week</option>
    <option value='lastweek'>Last Week</option>
    <option value='thismonth'>This Month</option>
    <option value='lastmonth'>Last Month</option>
    <option value='thisyear'>This Year</option>
    <option value='lastyear'>Last Year</option>
    <option value='custom'>Custom</option>
</select>

UPDATE2: Secondly you can check for request parameters and produce start and date in a following consistent way.

$periods = array(
    'today'     => array('start' => 'now', 'end' => 'now'),
    'yesterday' => array('start' => 'yesterday', 'end' => 'yesterday'),
    'thisweek'  => array('start' => 'monday this week', 'end' => 'sunday this week'),
    'lastweek'  => array('start' => 'monday last week', 'end' => 'sunday last week'),
    'thismonth' => array('start' => 'first day of this month', 'end' => 'last day of this month'),
    'lastmonth' => array('start' => 'first day of last month', 'end' => 'last day of last month'),
    'thisyear'  => array('start' => 'first day of january this year', 'end' => 'last day of december this year'),
    'lastyear'  => array('start' => 'first day of january last year', 'end' => 'last day of december last year')
);

if (isset($_POST['report']) && $_POST['report']) {
    $period = $_POST['report'];
    switch ($period) {
        case 'today':
        case 'yesterday':
        case 'thisweek':
        case 'lastweek':
        case 'thismonth':
        case 'lastmonth':
        case 'thisyear':
        case 'lastyear':
            $start = date('Y-m-d', strtotime($periods[$period]['start']));
            $end   = date('Y-m-d', strtotime($periods[$period]['end']));
            break;
        case 'custom':
            if (isset($_POST['start']) && $_POST['start'] &&
                isset($_POST['end']) && $_POST['end']) {
                $dt_start = DateTime::createFromFormat('d-m-Y', $_POST['start']);
                $dt_end   = DateTime::createFromFormat('d-m-Y', $_POST['end']);
                if ($dt_start > $dt_end) {
                    echo 'Start date should be less than end date.';
                    exit;
                }
                $start = $dt_start->format('Y-m-d');
                $end   = $dt_end->format('Y-m-d');
            } else {
                echo 'Enter dates for report period.';
                exit;
            }
            break;
        default:
            echo 'Invalid report period.';
            exit;
    }
} else {
    echo 'Report period has not been selected';
    exit;
}

Thirdly PDO part stays absolutely the same and you use it only once

// assumption is that $db is properly instantiated
$sql = "SELECT * FROM log WHERE log_datetime >= :start AND log_datetime < :end + INTERVAL 1 DAY";
$query = $db->prepare($sql);
$query->execute(array(':start'=>$start, ':end'=>$end));
$rows = $query->fetchAll(PDO::FETCH_OBJ);


You can also build your conditions in SQL

-- today
SELECT *
  FROM log
 WHERE log_datetime >= CURDATE()
   AND log_datetime <  CURDATE() + INTERVAL 1 DAY;
-- yesterday
SELECT *
  FROM log
 WHERE log_datetime >= CURDATE() - INTERVAL 1 DAY
   AND log_datetime <  CURDATE();
-- this week
SELECT *
  FROM log
 WHERE log_datetime >= CURDATE() + INTERVAL 1 - DAYOFWEEK(CURDATE()) DAY
   AND log_datetime <  CURDATE() + INTERVAL 7 - DAYOFWEEK(CURDATE()) + 1 DAY;
-- this month
SELECT *
  FROM log
 WHERE log_datetime >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
   AND log_datetime <  LAST_DAY(CURDATE()) + INTERVAL 1 DAY;
-- this year
SELECT *
  FROM log
 WHERE log_datetime >= CURDATE() - INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY
   AND log_datetime <  CURDATE() - INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY + INTERVAL 1 YEAR;
-- last year
SELECT *
  FROM log
 WHERE log_datetime >= CURDATE() - INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY - INTERVAL 1 YEAR
   AND log_datetime <  CURDATE() - INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY;

Here is SQL Fiddle demo

You can even wrap it in a stored procedure with an input parameter that tells what kind of period you need to fetch your data for.


Now what you don't want to do is to apply any functions (like this YEAR(log_datetime)) to columns in WHERE clause. By doing this you prevent MySQL from using an index(indices) on that column and effectively causing a full scan.