Database Connected But Data is not Inserted Into it

Question

<?php
//index.php

$connect = new PDO("mysql:host=localhost;dbname=sales", "root", "");

function e_type($connect) {
    $output1 = '';
    $query = "SELECT * FROM elimo_type ORDER BY type ASC";
    $statement = $connect->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();
    foreach ($result as $row) {
        $output1 .= '<option value="' . $row["type"] . '">' . $row["type"] . '</option>';
    }
    return $output1;
}

function hw_type($connect) {
    $output2 = '';
    $query = "SELECT * FROM hw_version ORDER BY type ASC";
    $statement = $connect->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();
    foreach ($result as $row) {
        $output2 .= '<option value="' . $row["type"] . '">' . $row["type"] . '</option>';
    }
    return $output2;
}

function sw_type($connect) {
    $output3 = '';
    $query = "SELECT * FROM sw_version ORDER BY type ASC";
    $statement = $connect->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();
    foreach ($result as $row) {
        $output3 .= '<option value="' . $row["type"] . '">' . $row["type"] . '</option>';
    }
    return $output3;
}
?>
<?php
include 'header.php';
?>
<div class="container">
    <h3 align="center">Purchase</h3>
    <br />
    <h4 align="center">Enter Purchase Details</h4>
    <br />
    <form method="post" id="insert_form">
        <div class="table-repsonsive">
            <span id="error"></span>
            <table class="table table-bordered" id="item_table">
                <tr>
                    <th>Serial No</th>
                    <th>Type</th>
                    <th>Hardware Version</th>
                    <th>Software Version</th>
                    <th>Key</th>
                    <th><button type="button" name="add" class="btn btn-success btn-sm add"><span class="glyphicon glyphicon-plus"></span></button></th>
                </tr>
            </table>
            <div align="center">
                <input type="submit" name="submit" class="btn btn-info" value="Insert" />
            </div>
        </div>
    </form>
</div>
</body>
</html>

<script>
    $(document).ready(function () {

        $(document).on('click', '.add', function () {
            var html = '';
            html += '<tr>';
            html += '<td><input type="text" name="serial_no[]" class="form-control serial_no" /></td>';
            html += '<td><select name="e_type[]" class="form-control e_type"><option value="">Select Type</option><?php echo e_type($connect); ?></select></td>';
            html += '<td><select name="hw_type[]" class="form-control hw_type"><option value="">Select Hardware Version</option><?php echo hw_type($connect); ?></select></td>';
            html += '<td><select name="sw_type[]" class="form-control sw_type"><option value="">Select Software Version</option><?php echo sw_type($connect); ?></select></td>';

            html += '<td><input type="text" name="key[]" class="form-control key" /></td>';
            html += '<td><button type="button" name="remove" class="btn btn-danger btn-sm remove"><span class="glyphicon glyphicon-minus"></span></button></td></tr>';
            $('#item_table').append(html);
        });

        $(document).on('click', '.remove', function () {
            $(this).closest('tr').remove();
        });

        $('#insert_form').on('submit', function (event) {
            event.preventDefault();
            var error = '';
            $('.serial_no').each(function () {
                var count = 1;
                if ($(this).val() == '')
                {
                    error += "<p>Enter Serial no at " + count + " Row</p>";
                    return false;
                }
                count = count + 1;
            });

            $('.e_type').each(function () {
                var count = 1;
                if ($(this).val() == '')
                {
                    error += "<p>Select Type at " + count + " Row</p>";
                    return false;
                }
                count = count + 1;
            });

            $('.hw_type').each(function () {
                var count = 1;
                if ($(this).val() == '')
                {
                    error += "<p>Select Hardware Version  at " + count + " Row</p>";
                    return false;
                }
                count = count + 1;
            });

            $('.sw_type').each(function () {
                var count = 1;
                if ($(this).val() == '')
                {
                    error += "<p>Select Software Version  at " + count + " Row</p>";
                    return false;
                }
                count = count + 1;
            });

            $('.key').each(function () {
                var count = 1;
                if ($(this).val() == '')
                {
                    error += "<p>Enter Key at " + count + " Row</p>";
                    return false;
                }
                count = count + 1;
            });
            var form_data = $(this).serialize();
            if (error == '')
            {
                $.ajax({
                    url: "insert.php",
                    method: "POST",
                    data: form_data,
                    success: function (data)
                    {
                        if (data == 'ok')
                        {
                            $('#item_table').find("tr:gt(0)").remove();
                            $('#error').html('<div class="alert alert-success">Purchase Details Saved</div>');
                        }
                    }
                });
            } else
            {
                $('#error').html('<div class="alert alert-danger">' + error + '</div>');
            }
        });

    });
</script>
<?php
//insert.php;

if (isset($_POST["serial_no"])) {
    $connect = new PDO("mysql:host=localhost;dbname=sales", "root", "");
    $id = uniqid();
    for ($count = 0; $count < count($_POST["serial_no"]); $count++) {
        $query = "INSERT INTO elimo_purchase 
      (id,serial_no, e_type, hw_type, sw_type,key) 
      VALUES (:id,:serial_no, :e_type, :hw_type, :sw_type,:key)";
        $statement = $connect->prepare($query);
        $statement->execute(
                array(
                    ':id' => $id,
                    ':serial_no' => $_POST["serial_no"][$count],
                    ':e_type' => $_POST["e_type"][$count],
                    ':hw_type' => $_POST["hw_type"][$count],
                    ':sw_type' => $_POST["sw_type"][$count],
                    ':key' => $_POST["key"][$count]
                )
        );
    }
    $result = $statement->fetchAll();
    if (isset($result)) {
        echo 'ok';
    }
}
?>

I'm getting output as purchase details saved but result is not stored into database.

You can't use `fetchAll()` for an `INSERT` statement, it can only be used with `SELECT`. — Barmar, Apr 07 '18 at 06:13
You don't need to prepare the statement inside the loop. Prepare it once, then call `execute()` in a loop with different parameter arrays. — Barmar, Apr 07 '18 at 06:15
To tell if the `INSERT` was successful, check the return value of `$statement->execute()` — Barmar, Apr 07 '18 at 06:15
I was talking about the code in `insert.php`, not `index.php`. — Barmar, Apr 07 '18 at 06:30

score 2 · Answer 1 · answered Apr 07 '18 at 06:16

2

You need to debug as per below,

Check all the values which you pass in insert query print all and check values.
Then print the query and fire the same query manually and check it was inserted or not

answered Apr 07 '18 at 06:16

Priyanka Khade

450
4
18

score -1 · Answer 2 · answered Apr 07 '18 at 06:19

When you're doing an INSERT query, you can't use a fetch function, because it doesn't return any data. That can only be used with SELECT. You need to check the result of $statement->execute().

if(isset($_POST["serial_no"]))
{
    $connect = new PDO("mysql:host=localhost;dbname=sales", "root", "");
    $id = uniqid();
    $query = "INSERT INTO elimo_purchase (id,serial_no, e_type, hw_type, sw_type,key) 
              VALUES (:id,:serial_no, :e_type, :hw_type, :sw_type,:key)";
    $statement = $connect->prepare($query);
    for($count = 0; $count < count($_POST["serial_no"]); $count++)
    {  
        if (!$statement->execute(
                array(
                    ':id'   => $id,
                    ':serial_no'   => $_POST["serial_no"][$count],
                    ':e_type'  => $_POST["e_type"][$count], 
                    ':hw_type' => $_POST["hw_type"][$count], 
                    ':sw_type' => $_POST["sw_type"][$count], 
                    ':key'  => $_POST["key"][$count]
                    )
                )) {
            die('not ok');
        }
    }
    echo 'ok';
}

nope now it is not working at all. – Manali Naik Apr 07 '18 at 06:25 — Manali Naik, Apr 07 '18 at 06:25
e_type,hw_type and sw_type are data of another table – Manali Naik Apr 07 '18 at 06:25 — Manali Naik, Apr 07 '18 at 06:25
I thought they come from the form in `index.php`. – Barmar Apr 07 '18 at 06:28 — Barmar, Apr 07 '18 at 06:28

Database Connected But Data is not Inserted Into it

2 Answers2