I'm struggling for this, please some one help. I'm not a html or php expert.
I've created a simple webpage that pulls the records from mysql database from last 7 days, 10 days etc by changing the select statement every time. Works fine till here. But it is a pain to change the select statement every time, instead I want to create a simple drop down list.
I've added simple drop down with the list of the dates like 1(indicates one day of records), 7- 7 days of records etc. Once the user select 7, then it has to jump to that query that pulls 7 day records and then continue. But what am I doing wrong here, I can't able to execute it.
Here is my code
<!DOCTYPE html>
<html>
<head>
<title>PHP</title>
</head>
<body>
<p>
<select name="value">
<option value="7">7</option>
<option value="1">1</option>
</select>
<?php
$dbh = mysqli_connect("","", "", "");
if (!$dbh) {
echo "Error: Unable to connect to mysqli." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
}
print ("<h1>Metrics</h1>");
print ("<table class=\"hovertable\">
");
if($_POST['value'] == '7')
{
// query to get 7 records
$select="select * from table where FROM_UNIXTIME(date)>= (NOW() - INTERVAL 7 DAY)";
}
else
{
// query to get 1 records
$select="select * from table where FROM_UNIXTIME(date)>= (NOW() - INTERVAL 1 DAY)";
}
$result = $dbh->query($select);
print "Total records :". ' '. $result->num_rows ;
if ($result->num_rows > 0)
{
while ($row = $result->fetch_assoc())
{
// Capture each record
$data = array('row'=>$row);
print ("<tr>");
print ("<td style=\"white-space: nowrap\"> $ID</td>");
print ("<td style=\"white-space: nowrap\"> $department</td>");
print ("<td style=\"white-space: nowrap\"> $customername</td>");
print ("<td style=\"white-space: nowrap\"> $date</td>");
print ("</tr>
");
}
}
else
{
echo "0 results";
}
exit;
?>
$dbh->close();
</p>
</body>
</html>
I see that you are checking for the user posted value here :
$_POST['value']
But I did not see where and how you are posting this value to the page. Maybe you need a form and a submit button to post the selected value:
<form action="youpage.php" method="post">
//your dropdown code here
<input type="submit" value="Submit">
</form>
You most post the value of the select by either a form or JavaScript.
Using a form would look like:
<form method="post">
<select name="value">
<option value="7">7</option>
<option value="1">1</option>
</select>
<input type="submit" value="go" />
</form>
Using JavaScript
<form method="post" >
<select name="value" onchange="this.form.submit()">
<option value="7">7</option>
<option value="1">1</option>
</select>
</form>
If you don't want the whole page to reload, you'd want to separate or copy the query and table into another PHP page, and submit the form using an XHR.
Updated to include a function to create the select options.
<?php
Function ddform_Interval($array) {
// generates the dropdown selects based on values in array
$result = "";
$post = 1; // default post value
IF (isset($_POST['value'])) {
// validate the post value. It must match a value in array
IF (in_array($_POST['value'], $array)) { $post = $_POST['value']; }
}
Foreach($array as $value) {
IF ($post == $value) { $result .= "<option selected value=\"".$value."\">".$value."</option>";
}ELSE{ $result .= "<option value=\"".$value."\">".$value."</option>";
}
}
return $result;
}
$allowed_postvals = array('1', '7'); // add more values to add select options
$result = "";
$dbh = mysqli_connect("","", "", "");
if (!$dbh) {
echo "Error: Unable to connect to mysqli." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
}
// set & validate post value
IF (isset($_POST['value'])) {
IF (in_array($_POST['value'], $allowed_postvals)) { $postvalue = $_POST['value']; }ELSE{ $postvalue = '1'; }
}ELSE{
$postvalue = 1;
}
$query = "SELECT * FROM table WHERE FROM_UNIXTIME(date)>= (NOW() - INTERVAL ".$postvalue." DAY)";
$sql = $dbh->query($query);
if ($sql->num_rows > 0) {
$result .= "Total records :". ' '. $sql->num_rows ;
$result .= "<table class=\"hovertable\">
";
while ($row = $sql->fetch_assoc()) {
// Capture each record
$data = array('row'=>$row);
$result .= "<tr>";
$result .= "<td style=\"white-space: nowrap\"> ".$row['ID']."</td>";
$result .= "<td style=\"white-space: nowrap\"> ".$row['department']."</td>";
$result .= "<td style=\"white-space: nowrap\"> ".$row['customername']."</td>";
$result .= "<td style=\"white-space: nowrap\"> ".$row['date']."</td>";
$result .= "</tr>
";
}
$result .= "</table>
";
}else{
$result .= "0 results";
}
$dbh->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>PHP</title>
</head>
<body>
<p>
<form action="" name="form" method="post">
<select name="value"><?php echo(ddform_Interval($allowed_postvals)); ?></select>
<input type="submit" name="submit" value="Submit">
</form>
</p>
<h1>Metrics</h1>
<?php echo($result); ?>
</body>
</html>