I have read through hundreds of posts on here and on other sites. I am not overly familiar with PHP and mysql, but already have my tables built in mysql, have called them using single checklists and filtered them based on date and time. However, in the multi-checkbox arena, I cannot seem to pin down what I'm doing wrong for an array code and customizing the resulting table. This is the basic html:
<html> <body> <form method="POST" action="php/minute_feedback.php">
Date: <input type="text" name="from_date" id="from_date" value="2016-04-07">
<input type="checkbox" name="check_list[]" value="gate" id="gate">
<input type="checkbox" name="check_list[]" value="pressure" id="pressure">
<input type="checkbox" name="check_list[]" value="flow" id="flow">
<input type="submit" name="submit" value="submit">
This is the basic PHP (I'll only include the date parameters vs. date & time to keep it shorter since the inclusion will be the same):
<?php
$servername = "myhostaddress.com";
$username = "myusername";
$password = "mypassword";
$dbname = "mydatabasename";
$conn=new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);}
if($_POST['submit'] == "submit") {
$from_date = ($_POST['from_date']);
$to_date = ($_POST['from_date']);}
if(isset($_POST['submit'])) {
Now ... I know there should be some sort of an array code in here like:
$checklist[] = $_POST[check_list];
But - I'm not sure what the correct syntax/code is. Next question is about my query. I've seen most queries using a WHERE + IN combo and a '*' for SELECT. However, I need something closer to:
$sql = "SELECT Fdate, Ftime, (list of variable columns from the checked options such as 'gate', 'pressure', 'flow' --> but only if they've been checked) FROM minute_db WHERE Fdate BETWEEN '$from_date' AND '$to_date'";
$result = $conn->query($sql);}
if ($result->num_rows > 0) {
Now - as for my table headers, I can use the following code if I had a single value being looked up:
echo "<table><tr><th>Date</th><th>Time</th><th>Level (ft)</th></tr>";}
But, I need the header to be dynamically populated with the results of the checked items (1, 2, or 3 or more variables as needed) plus the date and time records.
echo "<table>";}
else {
echo "0 results";}
$conn->close();
?>
My end result is that the user can enter a date and time range and choose which data to display, so for example, if they want to know just the gate setting and pressure on 4/7/2016, they'd enter that date and check those two boxes so they will get a table (no formatting below - just trying to represent), for example:
Date | Time | Gate Setting | Pressure
----------------------------------------------------
2016-04-07 | 11:00 | 21 | 50
I will ultimately have a table with tens of thousands of rows of data to select from (and will include limiters to the amount of data that can be pulled), and up to 56 columns of variables to select from. I'm not using any special/fancy math functions to group time periods, etc. - I've already manually separated it all out and uploaded it so it's ready to go. I've tried a lot of different solutions from the internet, but have ultimately failed. The * option for SELECT doesn't seem like what I what, neither does the IN option - but I am not sure (just speculating as neither has worked). I'm on a netfirms mysql platform, so it's pretty updated (and I'll be changing everything to mysqli to avoid whatever problems stem from not using it - so any feedback with that in advance would be AWESOME!!. I appreciate any ideas you have!
$checklist = $_POST['check_list'];
$allowedFields = [
'gate' => 'Gate',
'pressure' => 'Pressure',
'flow' => 'Flow',
'etc' => 'Other field title'
];
$wantedFields = [];
$tableDynFields = [];
foreach ($checklist as $field) {
if (array_key_exists($field, $allowedFields)) {
$wantedFields[] = $field;
$tableDynFields[] = $allowedFields[$field];
}
}
if ($wantedFields) {
$wantedFields = join(',', $wantedFields) . ',';
} else {
$wantedFields = '';
}
$sql = "SELECT Ftime, {$wantedFields} Fdate FROM ...';
// and here you have $tableDynFields array for table generation
Thanks to Deep's solution above - it works perfectly as-is! AWESOME. As for the use of the table array, I was struggling because I could populate the table, but was getting duplicates of date and time (as noted in my replies above). However - I simply moved the script around a bit and it works. Now, the user will get back columns with the date, time, and then the additional variables they are looking for, side by side, across the page, as comparative values (note - I have not yet gotten to the point of mysqli or pdo, so if this is old, please update as you need). Hopefully this helps - and thanks again! So - from the sql line from Deep's post above (fully filled out), this is the table -->
$sql = "SELECT Fdate, {$wantedFields} Ftime FROM minute_db WHERE Fdate BETWEEN '$from_date' AND '$to_date' AND Ftime BETWEEN '$from_time' AND '$to_time'";
$result = $conn->query($sql);
}
if ($result->num_rows > 0) {
//header fields
echo "<table><tr><th>Date</th><th>Time</th>";
foreach($tableDynFields as $c) {
echo "<th>$c</th>";
}
echo "</tr>";
//results
while ($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["Fdate"]."</td><td>".$row["Ftime"]." </td>";
foreach($tableDynFields as $c) {
echo "<td>".$row[$c]."</td>";
}
echo "</tr>";
}
echo "</table>";
//result set free
$result->free();
} else {
echo "query failed.<hr/>";
}
$conn->close();
?>
The only thing I'll add is if you're having to filter and sort through as much data as I am - tis good to either put a forced limiter at the end of the select statement, or something to that effect!