pcguru pcguru - 1 year ago 89
PHP Question

how to do multi-part PHP query of mysql db

So - 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):

$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";}

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!!. Thanks to all. I appreciate any ideas you have!

Answer Source
$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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download