POST variables in SQL query

MyU3A Incorporated commented on Jul 18, 2023

Hello, I'd like to see an example using a POST variable value in an SQL query. This is (almost) the topic of Tutorial 8: Report Parameters and Inputs Package. However, even though I did an exact copy and paste of the Tutorial 8 code, I cannot get it to work. I did find a couple of syntax errors, like an extra comma after "year"=>date("Y"), but still no joy. Can you help?

KoolReport commented on Jul 18, 2023

Can you post your code and also the error?

MyU3A Incorporated commented on Jul 21, 2023

I got Tutorial to work on my own AFTER making several syntax corrections (noted with "rwh" below).

<?php

//index.php

require_once "SaleReport.php";       // added line rwh

$report = new SaleReport;
$report->run()->render();

<?php

//SaleReport.php

require "../koolreport/core/autoload.php";

class SaleReport extends \koolreport\KoolReport
{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        return array(
            "year"=>date("Y")    // remove "," rwh
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "year"=>"yearInput"    // remove "," rwh
        );
    }   

    function settings()
    {
        return array(
            "dataSources"=>array(
                "mydata"=>array(
                    "connectionString"=>
                        "mysql:host=localhost;dbname=test_sales",
                    "username"=>"rwh",
                    "password"=>"1234",
                    "charset"=>"utf8"
                )
            )
        );  // add ";" rwh
    }

    function setup()
    {
        $this->src("mydata")
        ->query("select * from orders where year=:year")
        ->params(array(
            ":year"=>$this->params["year"]
        ))
        ->pipe($this->dataStore("orders_in_year"));  // add ";" rwh
    }
}

<?php
//SaleReport.view.php

    use \koolreport\widgets\koolphp\Table;
    use \koolreport\inputs\TextBox;
?>
<h1ml>
    <head>
        <title>SaleReport</title>
    </head>
    <body>
        <form method="post">
            <label>Year</label>
            <?php TextBox::create(array(
                "name"=>"yearInput"    // remove "," rwh
            ));?>
            <button>Submit</button>
        </form>
        <?php
        Table::create(array(
            "dataStore"=>$this->dataStore("orders_in_year")
        ));
        ?>
    </body>
</html>

David Winterburn commented on Jul 27, 2023

Would you pls post error messages and stack traces for us to check it for you? Tks,

KoolReport's Forum

POST variables in SQL query #3097

Build Your Excellent Data Report