pgsql date range query from html form

Question

I am attempting to build a page that will allow custom report generation from a pgsql database. In PHP I have declared the variables $table, $datea and &datez... I then have an html form where a user can post the selected table and dates for these variables for the query. However, I am getting an error message (ERROR 500 Page isn't working, unable to handle this request). Can anyone offer some advice?

$datea= $_POST["userDatea"];
$table= $_POST["userTable"];
$datez= $_POST["userDatez"];

if(isset($_POST['submit'])
// Create connection
$conn = pg_connect("host=xx.xx.xx.xx port=xxxx dbname=fpscdb001 user=xxx password=xxxxxxxxxxxxxx");

// Check connection
if (!$conn) {
echo "Did not connect.\n";
exit;
}
$result = pg_query($conn, "SELECT *
FROM 
fpscdb001_ws_001.$table
WHERE 
$table.created_on BETWEEN '$datea' AND '$datez' AND
$table.soft_delete_id = '0';");

Form:
<form method="post" id="report" action="custom.php">
<div class="formitem" style="max-width: 200px;">
<p style="font-color: white" style="font-weight: 800px">Select a Table:</p>
<p><select name="table" class="form-control" id="userTable">
<option value="dispatch_1">Dispatch</option>
<option value="normal_usb__a_t">Inventory</option>
<option value="incident">Real Estate</option>
<option value="tech_support2">Tech-Support</option>
</select></p>
</div>
<div class="formitem" style="max-width: 200px" style="margin-bottom: 20px">
<p>FROM DATE</p> <input type="DATE" class="textarea" id="userDatea" style="height: 30px; border-radius: 5px;"><br><br>
<p>TO DATE</p> <input type="DATE" class="textarea" id="userDatez" style="height: 30px; border-radius: 5px;"><br><br>
</div>                          
<div style="padding-bottom: 120px">
<input type="submit" class="btn btn-small greyBtn light submit" value="Submit" style="max-width: 200px; max-height: 125px"> 
</div>
</form>

Update your question with the error message.

bassxzero
– bassxzero

2016-12-20 19:44:16 +00:00
Commented Dec 20, 2016 at 19:44 — bassxzero
– bassxzero, Commented Dec 20, 2016 at 19:44
... page isn't workingHTTP ERROR 500

KevMoe
– KevMoe

2016-12-20 19:45:53 +00:00
Commented Dec 20, 2016 at 19:45 — KevMoe
– KevMoe, Commented Dec 20, 2016 at 19:45
what do you see in /var/log/apache2/error.log?

Nathan
– Nathan

2016-12-20 19:55:45 +00:00
Commented Dec 20, 2016 at 19:55 — Nathan
– Nathan, Commented Dec 20, 2016 at 19:55

bassxzero · Accepted Answer · 2016-12-20 19:54:29Z

1

Try this. See my comments in the code.

<?php
    // show error messages
    ini_set('error_reporting', E_ALL);
    ini_set("display_errors", 1);

    $datea= $_POST["userDatea"];
    $table= $_POST["userTable"];
    $datez= $_POST["userDatez"];

    // You need to do all of this if and only if this is a post request
    // Also this method of detecting a post request is more consistent
    if( !empty($_SERVER['REQUEST_METHOD']) && (strcasecmp($_SERVER['REQUEST_METHOD'], 'post')===0)  ) {
        // Create connection
        $conn = pg_connect("host=xx.xx.xx.xx port=xxxx dbname=fpscdb001 user=xxx password=xxxxxxxxxxxxxx");

        // Check connection
        if (!$conn) {
            echo "Did not connect.\n";
            exit;
        }

        $result = pg_query($conn,
            "SELECT *
            FROM 
            fpscdb001_ws_001.$table
            WHERE 
            $table.created_on BETWEEN '$datea' AND '$datez' AND
            $table.soft_delete_id = '0';"
        );


        if (!$result) {
            echo "Query failed\n";
            exit;
        }

        exit('Success?');
    }
?>
<!-- Make sure your HTML is well formed  -->
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width">
    <title>Test</title>
</head>
<body>
Form:
<form method="post" id="report" action="custom.php">
    <div class="formitem" style="max-width: 200px;">
        <p style="font-color: white" style="font-weight: 800px">Select a Table:</p>
        <!-- The attribute name is used as the key for the $_POST array. Without a name the form control will not be submitted   -->
        <p><select name="userTable" class="form-control" id="userTable">
                <option value="dispatch_1">Dispatch</option>
                <option value="normal_usb__a_t">Inventory</option>
                <option value="incident">Real Estate</option>
                <option value="tech_support2">Tech-Support</option>
            </select></p>
    </div>
    <div class="formitem" style="max-width: 200px" style="margin-bottom: 20px">
        <!-- The attribute name is used as the key for the $_POST array. Without a name the form control will not be submitted   -->
        <p>FROM DATE</p> <input type="DATE" class="textarea" id="userDatea" name="userDatea" style="height: 30px; border-radius: 5px;"><br><br>
        <!-- The attribute name is used as the key for the $_POST array. Without a name the form control will not be submitted   -->
        <p>TO DATE</p> <input type="DATE" class="textarea" id="userDatez" name="userDatez"  style="height: 30px; border-radius: 5px;"><br><br>
    </div>
    <div style="padding-bottom: 120px">
        <input type="submit" class="btn btn-small greyBtn light submit" value="Submit" style="max-width: 200px; max-height: 125px">
    </div>
</form>
</body>
</html>

answered Dec 20, 2016 at 19:54

bassxzero

5,01924 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

KevMoe Over a year ago

Thank you, though I'm getting the same response.

bassxzero Over a year ago

@KevMoe 500 means that you had a fatal error. Check your logs like Nathon said.

bassxzero Over a year ago

@KevMoe also dbname=fpscdb001 is not the same as fpscdb001_ws_001 Make sure you're using the right database

KevMoe Over a year ago

Query failed: ERROR: syntax error at or near "''" LINE 5: .created_on BETWEEN '' AND '' AND ^ in /home2/rightme1/public_html/networks/reports/customphp.php on line 29

bassxzero Over a year ago

@KevMoe did you copy and paste the entire script because that error means that you're not getting post data. If you have names on your html form controls and you actually entered a value then it should work.

|

Collectives™ on Stack Overflow

pgsql date range query from html form

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related