Inserting data into database with PHP

Question

The question is: How can I send the data entered by the user to the database, after it has been filtered by filter_input and saved into an associative array. The way I am trying to do it, I get errors saying undefined index for each field of filtered_inputs array even though I'm filling up the array with filtered, sanitized inputs. Also, if anyone knows an alternative or a better way to do this, please do tell. The database in question is MariaDB mySQL dialect. Everything is placed on a server up and running and using phpmyadmin allocated to the server.

<?php require_once("Filtering.php");

$filter = new Filtering();

$filter->connect();

if (isset($_POST['submit'])){
    $filter->insertAll();
}

?>
<!DOCTYPE html>
<html lang="en">

<head>
</head>

<body>

<div class="container-fluid">
    <form action="<?php $_SERVER['PHP_SELF']; ?>" method="POST" name="order" novalidate>
        <fieldset>
            <legend>Order Summary</legend>
            <br>
            <div class="row">
                <div class="col-12 d-flex flex-row">
                    <div class="col-4">
                        <span><strong>Product Name</strong></span>
                    </div>
                    <div class="col-2 justify-content-center">
                        <span><strong>Quantity</strong></span>
                    </div>
                    <div class="col-3 justify-content-center">
                        <span><strong>Price</strong></span>
                    </div>
                </div>
            </div>
            <br>
            <div class="row">
                <div class="col-12 d-flex flex-row">
                    <div class="col-4">
                        <span>Gigabyte GTX1060 G1 Gaming 3GB GDDR5</span>
                    </div>
                    <div class="col-2 justify-content-center">
                        <select class="form-control col-3" id="quantity">
                        </select>
                    </div>
                    <div class="col-3 justify-content-center">
                        <span id="price">$ 349.99</span>
                    </div>
                </div>
            </div>
        </fieldset>
        <br>
        <fieldset>
            <legend>Customer Information</legend>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-2">
                        <label class="col-form-label">First Name<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control" placeholder="Enter your first name" type="text" name="firstName">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterFirstName()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty.</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-2">
                        <label class="col-form-label">Last Name<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control" placeholder="Enter your last name" type="text" name="lastName">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterLastName()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty.</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-2">
                        <label class="col-form-label">E-mail<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control" placeholder="Enter your e-mail" type="text" name="email">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterEmail()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty.</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-2">
                        <label class="col-form-label">Number<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control noButton" placeholder="Enter your number" type="number"
                               name="number">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterNumber()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty.</small>
                             </div>
                          </div>";
                }
            }
            ?>
        </fieldset>
        <br>
        <br>
        <fieldset>
            <legend>Payment Information</legend>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">Card Number<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control noButton" placeholder="xxxx-xxxx-xxxx-xxxx" type="number"
                               name="cardNum">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterCard()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty. Must not be longer than 16 digits</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">Expiration Date<span class="required">*</span></label>
                    </div>
                    <div class="col-2">
                        <input class="form-control" placeholder="MM/YY" type="text" name="expDate">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterExpDate()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty. Enter a valid date format: 'MM/YY'</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">Security Number<span class="required">*</span></label>
                    </div>
                    <div class="col-2">
                        <input class="form-control noButton" placeholder="xxxz" type="number" name="secNum">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterSecurityNum()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty. Must be at most 4 digits long</small>
                             </div>
                          </div>";
                }
            }
            ?>
        </fieldset>
        <br>
        <br>
        <fieldset>
            <legend>Delivery Information</legend>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <label class="form-check-label">
                        <strong>Delivery Option</strong>
                    </label>
                </div>
            </div>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <label class="form-check-label">
                        <input class="form-check-input" type="radio" name="exampleRadios" id="exampleRadios1"
                               value="option1" checked>
                        Standard Delivery - Free of charge
                    </label>
                </div>
                <div class="col-8 d-flex flex-row">
                    <label class="form-check-label">
                        <input class="form-check-input" type="radio" name="exampleRadios" id="exampleRadios1"
                               value="option1">
                        Express Delivery - $ 5.99
                    </label>
                </div>
            </div>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">Address Line 1<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control" placeholder="Enter your address" type="text" name="address">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterAddress()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty.</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">Address Line 2</label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control" placeholder="Optional" type="text" name="address2">
                    </div>
                </div>
            </div>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">Country</label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control" value="Croatia" type="text" name="country" disabled>
                    </div>
                </div>
            </div>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">County</label>
                    </div>
                    <div class="col-auto">
                        <select class="custom-select" id="county">
                        </select>
                    </div>
                </div>
            </div>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">City<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control" placeholder="Enter your city" type="text" name="city">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterCity()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty.</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-3">
                        <label class="col-form-label">Zipcode<span class="required">*</span></label>
                    </div>
                    <div class="col-auto">
                        <input class="form-control noButton" placeholder="Enter your zipcode" type="number" name="zip">
                    </div>
                </div>
            </div>
            <?php
            if ($_SERVER["REQUEST_METHOD"] == "POST") {
                if (!$filter->filterZip()) {
                    echo "<div class='row'>
                             <div class='col-5'>
                                  <small class='text-muted ml-3 warning'>! Field must not be empty. Must be at most 5 digits long</small>
                             </div>
                          </div>";
                }
            }
            ?>
            <br>
            <div class="col-auto">
                <label class="custom-control custom-checkbox mb-2 mr-sm-2 mb-sm-0">
                    <input type="checkbox" class="custom-control-input">
                    <span class="custom-control-indicator"></span>
                    <span class="custom-control-description">Receive mails about news and special offers</span>
                </label>
            </div>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-auto">
                        <label class="col-form-label">Additional Information</label>
                    </div>
                </div>
            </div>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <div class="col-5">
                        <textarea class="form-control" id="txtarea" rows="5" name="additionalInfo"></textarea>
                    </div>
                </div>
            </div>
            <br>
            <div class="row">
                <div class="col-8 d-flex flex-row">
                    <input class="btn btn-primary col-2 ml-3" type="submit" name="submit" value="Submit">
                </div>
            </div>
        </fieldset>
    </form>
</div>
<script src="js/script.js"></script>
</body>

</html>

And here is the PHP class: (I am using the same function for filtering almost every input field, but I deleted them so there's less code here. Basically every filter method adds a new element to the array with it's associative name: firstName, lastName, email etc.) For some reason when I try to insertAll() which is supposed to send the data to the database, the array seems to be empty.

<?php
class Filtering {

    public $filtered_inputs = array();
    public $error = array();

    var $tableName = "contact";
    var $connection;

    public function connect() {

        $this->connection= mysqli_connect($this->DBHostName, $this->DBUserName, $this->DBPassword, $this->DBName);

        if(!$this->connection) {
            print("Failed to connect to DB");
            die("Connection to database failed. " . mysqli_error($this->connection));
        }
        else {
            print("Connected to DB");
        }
    }

    public function filterFirstName() {
        global $filtered_inputs;
        global $error;
        $check = false;

        if (filter_has_var(INPUT_POST, 'firstName')) {
            if (strlen($_POST['firstName']) == 0) {
                $check = false;
            } else {
                $filtered_inputs['firstName'] = filter_input(INPUT_POST, 'firstName', FILTER_SANITIZE_STRING);
                $check = true;
            }
        }
        $error['firstName'] = $check;
        return $check;
    }


    public function insertAll(){
        var_dump($this->filtered_inputs);

        $firstName = $this->filtered_inputs['firstName'];
        $lastName = $this->filtered_inputs['lastName'];
        $eMail = $this->filtered_inputs['email'];
        $number = $this->filtered_inputs['number'];
        $cardNum = $this->filtered_inputs['cardNum'];
        $secNum = $this->filtered_inputs['secNum'];
        $address = $this->filtered_inputs['address'];
        $city = $this->filtered_inputs['city'];
        $zip = $this->filtered_inputs['zip'];;

        $query = "INSERT INTO contact (firstName, lastName, eMail, number, cardNum, secNum, address, city, zip) VALUES ($firstName, $lastName, $eMail, $number, $cardNum, $secNum, $address, $city, $zip);";

        print(mysqli_error($this->connection));
    }

}

You should avoid using global variables .. well .. anywhere. Also, you should learn how to use [prepared statements](https://www.youtube.com/watch?v=nLinqtCfhKY). — tereško, Nov 26 '17 at 22:53

score 1 · Answer 1 · answered Nov 26 '17 at 23:32

There are several issues (see comments), but the main problem here is the order in which you call your functions.

The individual filter functions are embedded in the form itself, and therefore are called after this line:

$filter->insertAll();

So in fact the array is still empty, and the function is trying to insert empty values.

You need to rearrange so that the individual functions are called before you attempt to insert the values.

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Your first problem is here:

class Filtering {
    ...
    var $connection;

    public function connect() {

        $this->connection= mysqli_connect($this->DBHostName, $this->DBUserName, $this->DBPassword, $this->DBName);

You see the $this-> means look inside the current class for this variable, so the Database connection is looking for:

class Filtering {
    var $connection = "something"; // var is optional and not really needed.
    $DBHostName = "host";
    $DBUserName = "user"; 
    $DBPassword = "pass"; 
   // Etc.

You have not shown that you've set up these connection details so that your script will never connect to the Database.

Next, you global usage. As referenced by tereško, it is unwise to use global, and you simply don't need to at all in this code.

You are referencing a value already established in the class; so simply call it using the magic $this-> functionality (amazingly enough I can't find any decent reference material specifically for $this), so that you do not gobalise the vaiables but instead keep then tidy in the class.

Example:

  public function filterFirstName() {
        $this->filtered_inputs['firstName'] = "something";

Your requested issue:

Next; your issue with var_dump($this->filtered_inputs); not outputting the data, you are calling and dumping the variable before it has been given any values.

Read your form/HTML code; you are calling $filter->insertAll(); before later in the page, calling the filterFirstName() function that actually saves any values to the variable.

It is bad form to submit forms to the same page that the form came from; if I refresh the page then the $_POSTed data will re-submit, every time. (there's also a multitude of other issues with this codebase)

Also you do not seem to have any code which sends the data to the MySQL database, maybe you're just not showing it but you do have the SQL string ($query) but you should have an instruction using this string:

 $this->connection->query($query);

This is a very basic example above, and it's highly recommended you explore -and use- Prepared Statements.

Streamline your code:

Improvements are a constant rolling effort, your issue appears to be you're writing hardcode for each input given to PHP by the form; scrap that; it's repetative and wasteful.

Try using foreach to run through each element in an array:

public function processFilter() {
    $check = false;

    foreach($_POST as $key=>$value){
        if (strlen($_POST[$key]) == 0 && $key == 'firstName') {
            $check = false;
        } 
       else {
            $this->filtered_inputs[$key] = filter_input(INPUT_POST, $key, FILTER_SANITIZE_STRING);
            $check = true;
        }
    }
    unset($key,$value);

    return $check;
}

This code could be further reduced and made more efficient, but I wanted to just give you a startoff; this function will now seemlessly populate your ->filtered_inputs array with every POST statement and will check the firstName key is not a zero length string.

Inserting data into database with PHP

2 Answers2

Your requested issue:

Streamline your code: