Sqlsrv_fetch($sql, SQLSRV_FETCH_ASSOC) being replaced with pdo::fetch_assoc


#1

Howdy all. Back again, as I've spent most of my day trying to wrap my head around how to submit multiple table rows to the database in one function. It appears that someone else has attempted to do the same thing here: https://www.sitepoint.com/community/t/how-to-insert-multiple-rows-in-mysql-database-using-php/275646 I actually emailed @sadock016 to see if they were able to get it working, but have not yet heard anything. I guess one of my frustrations is that by replaceing sqlsrv_fetch with pdo, my result sets are screwed up (by "screwed up" I mean that the result sets are no longer simple 1-dimensional associative arrays with column-name/value pairs. Rather, now I'm dealing with 2-dimensional arrays. What a HEADACHE!! Does anyone have any suggested reading on how to switch from sqlsrv_fetch to pdo? I'm really confused about it and not sure where to go from here. Thank you in advance for your help. - Jonathan


#2

It's a bit difficult to suggest much without seeing the code and the specific problems you are having.

I had a quick look at the thread you linked to, but there are quite a few flaws in the code posted there, not least the fact that some of the form input fields aren't named properly to allow them to be duplicated (and some are), and the save function doesn't iterate through more than one input field. So probably not a thing you want to base new code on.

I haven't done much (if anything) with SQL server and PHP, but it seems to me that the function you describe would return values in the same format as using $result = $qry->fetch(PDO::FETCH_ASSOC). Is that not the case? I have used PDO quite a lot, and the only way I can think you'd get 2-d arrays is if you use fetchAll(), but then you can iterate through that result set with a simple foreach() loop in any case.


#3

@droopsnoot Howdy sir, and thank you for weighing in on my query. Attached is my code with modifications to use PDO: You'll see that my AddRow and DeleteRow functions are a bit better than the one I linked to in my original post. However, the problem still remains of not having unique "name" attributes to submit to the Post array. :worried:

To resolve the "non-unique name" issue, should I to iterate through the row elements using a loop and set the "name" attribute to a unique value?

$selectIDquery = "SELECT IDENT_CURRENT('Order') as OrderID";
//var_dump($selectIDquery);

$resultsOfIDquery = $db->query($selectIDquery);
$row = $resultsOfIDquery->fetchAll(PDO::FETCH_ASSOC);
//
//// prepare query
//$preparedQuery = $db->prepare($selectIDquery);
////$resultsOfIDquery = PDO::query($selectIDquery);
//// execute query
//$preparedQuery->execute();

////works:
//$resultsOfIDquery = sqlsrv_query($db, $selectIDquery);
//$row = sqlsrv_fetch_array($resultsOfIDquery, SQLSRV_FETCH_ASSOC);
//
//echo "<pre>";
//var_dump($row);
//echo "</pre>";
//die(1);

//    echo $row['CrimeID'];

if (isset($_POST['submitOrder'])){
//    echo "hello";
}
?>
<div align="center">

        <h1>Purchase Order #: BCIT-<?php echo $row['OrderID'] + 1 ?></h1>







        <form class="formLayout" action="index.php" method="POST">
            <fieldset class="table">
                <legend>Supplier</legend>


                <div class="tr">

                    <div class="td right">Supplier:</div>
                    <div class="td">


                        <select name="supplier" id="suppSelID" onchange="showVendor(this.value)">
                            <?php
                            $queryForSuppliers = "SELECT SupplierID, SupName FROM Supplier";
                            $sql = sqlsrv_query($db, $queryForSuppliers);
                        echo "hello";
                            while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                                echo '<option value="' . $row['SupplierID'] . '">' . $row['SupName'] . '</option>';
                                var_dump($row);
                            }

                            ?>
                        </select>
<!--                    </div>-->
<!--                </div>-->
<!--            </fieldset>-->
<!--        </form>-->
        <br/>
        <div id="txtHint"></div>
                        <?php
                        echo $row['SupplierID'];
                        //                        var_dump($row['SupplierID'])

                        ?>


                    </div>

                </div>

                
            </fieldset>
            <fieldset class="table">
                <legend>Order</legend>
                <div class="tr">
                    <div class="td right">Ordered for Office:</div>
                    <select name="office" id="officeSelID">
                        <?php
                        $queryForOffices = "SELECT OfficeID,OffDescription FROM Office";
                        $sql = sqlsrv_query($db, $queryForOffices);
                        echo "hello";
                        while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                            echo '<option value="' . $row['OfficeID'] . '">' . $row['OffDescription'] . '</option>';
                            var_dump($row);
                        }

                        ?>
                    </select>
                </div>

                <div class="tr">
                    <div class="td right">Ordered by:</div>
                    <select name="office" id="officeSelID">
                        <?php
                        $queryForPuchaser= "SELECT PurchaserID,PurName FROM Purchaser";
                        $sql = sqlsrv_query($db, $queryForPuchaser);
                        echo "hello";
                        while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                            echo '<option value="' . $row['PurchaserID'] . '">' . $row['PurName'] . '</option>';
                            var_dump($row);
                        }

                        ?>
                    </select>
                </div>

                <div class="tr">
                    <div class="td right">Date Ordered:</div>
                    <div class="td"><input type="text" class="autoFillDate" name="callDate"></div>

                    <div class="td right">Date Received:</div>
                    <div class="td"><input type="text" class="datepicker2" name="crimeDate"></div>
                </div>


                <div class="tr">
                    <div class="td right">Order complete?</div>
                    <div class="td"><input type="checkbox" id="subscribeNews" name="subscribe" value="newsletter"></div>
                </div>


            </fieldset>

            <fieldset>
                <legend>Items:</legend>

                <input type="button" value="Add" onClick="addRow('dataTable', 'grandTotal')" />
                <input type="button" value="Remove" onClick="deleteRow('dataTable')" />
                <table border="1">
                <tr>
                    <th id="lineNum">Line#</th>
                    <th id="chkBxHdr">&#x2713;</th>
                    <th id="contractNum">Contract#</th>
                    <th id="descr">Description</th>
                    <th id="qty">Qty</th>
                    <th id="priceEach">Each</th>
                    <th id="priceTotal">Line item total:</th>
                </tr>
                </table>
                <table id="dataTable" border="1">
                    <tbody>
                    <tr id='row_0'>
                        <p>
                        <td id="tdLineNum">
<!--                            <input type="text" id="tdLineNum"  required="required" name="lineNum" readonly value="1">-->
                        </td>
                        <td>
                            <input type="checkbox" id="chkBx">
                        </td>
                        <td>
                            <input type="text" id="tdContractNum" required="required" name="contractNum">
                        </td>
                        <td>
                            <input type="text" id="tdDesc" required="required" name="desc">
                        </td>
                        <td>
                            <input type="text" id="tdQty" required="required" name="qty" oninput="calculate('row_0')">
                        </td>
                        <td>
                            <input type="text" id="tdPriceEach" required="required" name="price" oninput="calculate('row_0')">
                        </td>
                        <td>
                            <input type="text" class="tdPriceTotal" required="required" name="total" onkeyup="calcGrandTot()">
                        </td>
                        </p>
                    </tr>
                    </tbody>
                </table>
                    <table align="right" border="1" style="margin: 0px 21px 0px 0px;">
<!--                    <table align="right" style="padding-right: 20px" border="1">-->
                    <tbody>
                    <tr id="grandTotalRow" >
<!--                    <tr id="grandTotalRow" style="padding-right: 20px">-->
                        <td >
<!--                        <td style="padding-right: 20px">-->
                            <label for="grandTotal">Grand Total: $</label>
                            <input type="text" id="grandTotal">
                        </td>
                    </tr>
                    </tbody>
                </table>

            </fieldset>


            <fieldset>
                <legend>Order Notes:</legend>


                <textarea name="orderSumOfInfo" rows="13" cols="94" >Enter notes here</textarea>

            </fieldset>

            <input type="submit" name='submitOrder' value='Submit Order'/>
            <!--            <input type="submit" name='submitViolentCrimeData' value='Submit report'/>-->


        </form>
    </div>

 <script type="text/javascript">
//            var initialLineNum = 2;
//            var runningTotal = 0;
        function addRow(tableID, grandTot) {
//            alert("in addRow");

//           get a reference to the table
            var table = document.getElementById(tableID);

//              HTMLTableElement.rows read-only property returns a live HTMLCollection of all the rows in the table.
// The rows included in the associated <thead>, ,<tfoot> and <tbody> elements. Although the property is read-only,
// the retuned object is live and allows th modification of its content.
            var rowCount = table.rows.length;
            if (rowCount < 100) { // limit the user from creating fields more than your limits

//                Insert a row in the table at row index(rowCount) and return a reference to the row
                var row = table.insertRow(rowCount);

//                alert(rowCount);
                var colCount = table.rows[0].cells.length;
                row.id = 'row_' + rowCount;


                for (var i = 0; i < colCount; i++) {

//                    Insert a cell in the row
                    var newcell = row.insertCell(i);
                    newcell.outerHTML = table.rows[0].cells[i].outerHTML;
                }
                var listitems = row.getElementsByTagName("input");

//              set various attribute values
                for (i = 0; i < listitems.length; i++) {
//                alert("hello");


                        listitems[i].setAttribute("oninput", "calculate('" + row.id + "')");

                }

            } else {
                alert("Maximum 100 items per purchase order.");

            }
//            initialLineNum++;
        }


        function deleteRow(tableID) {
//            alert("in deleteRow");
            var table = document.getElementById(tableID);
            var rowCount = table.rows.length;
            for (var i = 0; i < rowCount; i++) {
//            alert(rowCount);
                var row = table.rows[i];
//            alert(document.getElementById("dataTable").rows[0].innerHTML);
                var chkbox = row.cells[1].childNodes[1];
                if (chkbox !== null && chkbox.checked === true) {
//            alert(chkbox);
                    if (rowCount <= 1) { // limit the user from removing all the fields
                        alert("Cannot remove all the items in the list.");
                        break;
                    }
                    table.deleteRow(i);
                    rowCount--;
                    i--;
                }
            }
            // need to re initialize the row values here
            var tableRows = table.getElementsByTagName("tr");
            console.log(row);
// for echoing to the console (HTML page > right click > Inspect Element (or type Q) > Console tab
//console.log(tableRows);
            for (var j = 0; j < rowCount; j++){
            row.id = 'row_' + j;

                tableRows[j].setAttribute("id", "row_" + j);



            // need to re-initialize the calculated values here
            var calcFields = row.getElementsByTagName("input");
                for (k = 0; k < calcFields.length; k++) {
//                alert("hello");

                calcFields[k].setAttribute("oninput", "calculate('" + row.id + "')");
                }

            }

//console.log(tableRows);
            calcGrandTot();
        }

            function calculate(elementID) {
                var mainRow = document.getElementById(elementID);
                var myBox1 = mainRow.querySelectorAll('[name=qty]')[0].value;
                var myBox2 = mainRow.querySelectorAll('[name=price]')[0].value;
                var total = mainRow.querySelectorAll('[name=total]')[0];
                var myResult1 = myBox1 * myBox2;
                total.value = myResult1.toFixed(2);

            }


    </script>

#4

@droopsnoot Also, here is the "non-pdo" code I'm using:

<div align="center">

        <h1>Purchase Order #: BCIT-<?php echo $row['OrderID'] + 1 ?></h1>







        <form class="formLayout" action="index.php" method="POST">
            <fieldset class="table">
                <legend>Supplier</legend>


                <div class="tr">

                    <div class="td right">Supplier:</div>
                    <div class="td">


                        <select name="supplier" id="suppSelID" onchange="showVendor(this.value)">
                            <?php
                            $queryForSuppliers = "SELECT SupplierID, SupName FROM Supplier";
                            $sql = sqlsrv_query($db, $queryForSuppliers);
                        echo "hello";
                            while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                                echo '<option value="' . $row['SupplierID'] . '">' . $row['SupName'] . '</option>';
                                var_dump($row);
                            }

                            ?>
                        </select>
<!--                    </div>-->
<!--                </div>-->
<!--            </fieldset>-->
<!--        </form>-->
        <br/>
        <div id="txtHint"></div>
                        <?php
                        echo $row['SupplierID'];
                        //                        var_dump($row['SupplierID'])

                        ?>


                    </div>

                </div>


            </fieldset>
            <fieldset class="table">
                <legend>Order</legend>
                <div class="tr">
                    <div class="td right">Ordered for Office:</div>
                    <select name="office" id="officeSelID">
                        <?php
                        $queryForOffices = "SELECT OfficeID,OffDescription FROM Office";
                        $sql = sqlsrv_query($db, $queryForOffices);
                        echo "hello";
                        while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                            echo '<option value="' . $row['OfficeID'] . '">' . $row['OffDescription'] . '</option>';
                            var_dump($row);
                        }

                        ?>
                    </select>
                </div>

                <div class="tr">
                    <div class="td right">Ordered by:</div>
                    <select name="office" id="officeSelID">
                        <?php
                        $queryForPuchaser= "SELECT PurchaserID,PurName FROM Purchaser";
                        $sql = sqlsrv_query($db, $queryForPuchaser);
                        echo "hello";
                        while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                            echo '<option value="' . $row['PurchaserID'] . '">' . $row['PurName'] . '</option>';
                            var_dump($row);
                        }

                        ?>
                    </select>
                </div>

                <div class="tr">
                    <div class="td right">Date Ordered:</div>
                    <div class="td"><input type="text" class="autoFillDate" name="callDate"></div>

                    <div class="td right">Date Received:</div>
                    <div class="td"><input type="text" class="datepicker2" name="crimeDate"></div>
                </div>


                <div class="tr">
                    <div class="td right">Order complete?</div>
                    <div class="td"><input type="checkbox" id="subscribeNews" name="subscribe" value="newsletter"></div>
                </div>


            </fieldset>

            <fieldset>
                <legend>Items:</legend>

                <input type="button" value="Add" onClick="addRow('dataTable', 'grandTotal')" />
                <input type="button" value="Remove" onClick="deleteRow('dataTable')" />
                <table border="1">
                <tr>
                    <th id="lineNum">Line#</th>
                    <th id="chkBxHdr">&#x2713;</th>
                    <th id="contractNum">Contract#</th>
                    <th id="descr">Description</th>
                    <th id="qty">Qty</th>
                    <th id="priceEach">Each</th>
                    <th id="priceTotal">Line item total:</th>
                </tr>
                </table>
                <table id="dataTable" border="1">
                    <tbody>
                    <tr id='row_0'>
                        <p>
                        <td id="tdLineNum">
<!--                            <input type="text" id="tdLineNum"  required="required" name="lineNum" readonly value="1">-->
                        </td>
                        <td>
                            <input type="checkbox" id="chkBx">
                        </td>
                        <td>
                            <input type="text" id="tdContractNum" required="required" name="contractNum">
                        </td>
                        <td>
                            <input type="text" id="tdDesc" required="required" name="desc">
                        </td>
                        <td>
                            <input type="text" id="tdQty" required="required" name="qty" oninput="calculate('row_0')">
                        </td>
                        <td>
                            <input type="text" id="tdPriceEach" required="required" name="price" oninput="calculate('row_0')">
                        </td>
                        <td>
                            <input type="text" class="tdPriceTotal" required="required" name="total" onkeyup="calcGrandTot()">
                        </td>
                        </p>
                    </tr>
                    </tbody>
                </table>
                    <table align="right" border="1" style="margin: 0px 21px 0px 0px;">
<!--                    <table align="right" style="padding-right: 20px" border="1">-->
                    <tbody>
                    <tr id="grandTotalRow" >
<!--                    <tr id="grandTotalRow" style="padding-right: 20px">-->
                        <td >
<!--                        <td style="padding-right: 20px">-->
                            <label for="grandTotal">Grand Total: $</label>
                            <input type="text" id="grandTotal">
                        </td>
                    </tr>
                    </tbody>
                </table>

            </fieldset>


            <fieldset>
                <legend>Order Notes:</legend>


                <textarea name="orderSumOfInfo" rows="13" cols="94" >Enter notes here</textarea>

            </fieldset>

            <input type="submit" name='submitOrder' value='Submit Order'/>
            <!--            <input type="submit" name='submitViolentCrimeData' value='Submit report'/>-->


        </form>
    </div>

<script type="text/javascript">
//            var initialLineNum = 2;
//            var runningTotal = 0;
        function addRow(tableID, grandTot) {
//            alert("in addRow");

//           get a reference to the table
            var table = document.getElementById(tableID);

//              HTMLTableElement.rows read-only property returns a live HTMLCollection of all the rows in the table.
// The rows included in the associated <thead>, ,<tfoot> and <tbody> elements. Although the property is read-only,
// the retuned object is live and allows th modification of its content.
            var rowCount = table.rows.length;
            if (rowCount < 100) { // limit the user from creating fields more than your limits

//                Insert a row in the table at row index(rowCount) and return a reference to the row
                var row = table.insertRow(rowCount);

//                alert(rowCount);
                var colCount = table.rows[0].cells.length;
                row.id = 'row_' + rowCount;


                for (var i = 0; i < colCount; i++) {

//                    Insert a cell in the row
                    var newcell = row.insertCell(i);
                    newcell.outerHTML = table.rows[0].cells[i].outerHTML;
                }
                var listitems = row.getElementsByTagName("input");

//              set various attribute values
                for (i = 0; i < listitems.length; i++) {
//                alert("hello");


                        listitems[i].setAttribute("oninput", "calculate('" + row.id + "')");

                }

            } else {
                alert("Maximum 100 items per purchase order.");

            }
//            initialLineNum++;
        }


        function deleteRow(tableID) {
//            alert("in deleteRow");
            var table = document.getElementById(tableID);
            var rowCount = table.rows.length;
            for (var i = 0; i < rowCount; i++) {
//            alert(rowCount);
                var row = table.rows[i];
//            alert(document.getElementById("dataTable").rows[0].innerHTML);
                var chkbox = row.cells[1].childNodes[1];
                if (chkbox !== null && chkbox.checked === true) {
//            alert(chkbox);
                    if (rowCount <= 1) { // limit the user from removing all the fields
                        alert("Cannot remove all the items in the list.");
                        break;
                    }
                    table.deleteRow(i);
                    rowCount--;
                    i--;
                }
            }
            // need to re initialize the row values here
            var tableRows = table.getElementsByTagName("tr");
            console.log(row);
// for echoing to the console (HTML page > right click > Inspect Element (or type Q) > Console tab
//console.log(tableRows);
            for (var j = 0; j < rowCount; j++){
            row.id = 'row_' + j;

                tableRows[j].setAttribute("id", "row_" + j);



            // need to re-initialize the calculated values here
            var calcFields = row.getElementsByTagName("input");
                for (k = 0; k < calcFields.length; k++) {
//                alert("hello");

                calcFields[k].setAttribute("oninput", "calculate('" + row.id + "')");
                }

            }

//console.log(tableRows);
            calcGrandTot();
        }

            function calculate(elementID) {
                var mainRow = document.getElementById(elementID);
                var myBox1 = mainRow.querySelectorAll('[name=qty]')[0].value;
                var myBox2 = mainRow.querySelectorAll('[name=price]')[0].value;
                var total = mainRow.querySelectorAll('[name=total]')[0];
                var myResult1 = myBox1 * myBox2;
                total.value = myResult1.toFixed(2);

            }


    </script>

#5

Did you post the correct code? That seems to still use sqlsrv_ functions.


#6

@droopsnoot Whoops! My mistake. I actually only STARTED making the changes to PDO (just now added the pdo components to the beginning of the code). I have not changed all the queries, etc. simply because I'm still not sure PDO is the way to go. :-/


#7

Well, the reason you get a two-dimensional array is that in your PDO code, you're calling fetchAll(). If you just use fetch() instead, you can handle the results of the query in the same way that you do with the sqlserver code.

$queryForSuppliers = "SELECT SupplierID, SupName FROM Supplier";
$sql = sqlsrv_query($db, $queryForSuppliers);
echo "hello";
while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {
  echo '<option value="' . $row['SupplierID'] . '">' . $row['SupName'] . '</option>';
  var_dump($row);
  }

becomes

$queryForSuppliers = "SELECT SupplierID, SupName FROM Supplier";
$sql = $db->query($queryForSuppliers);
echo "hello";
while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
  echo '<option value="' . $row['SupplierID'] . '">' . $row['SupName'] . '</option>';
  var_dump($row);
  }

#8

@droopsnoot Darn it. I thought I had changed it. I will try the simple fetch() . Thank you!

@droopsnoot Edit: so by using PDO, will I be able to avoid having to reset each "name"="x" pair to a unique value (e.g. "x_0", "x_1", etc.) or will I still have to use something like setAttribute to assign "x" a unique value?

I'm guessing I will need to use something like: https://stackoverflow.com/questions/8426461/javascript-setattribute-v-s-element-attribute-value-to-set-name-attribut (getElementsByName) and set each uniquely. Or perhaps use jQuery: https://stackoverflow.com/questions/2617480/how-to-get-all-elements-which-name-starts-with-some-string :thinking:


#9

@droopsnoot Okay, I was able to get PDO implemented successfully. Now I'm not exactly sure how to use PDO to insert the name/value pairs from my table. Would you advise I start a new thread?

<div align="center">
<!--        --><?php
//        echo "<pre>";
//        var_dump($row);
//        echo "</pre>";
//        ?>


        <h1>Purchase Order #: BCIT-<?php echo $row['OrderID'] ?></h1>
<!--        <h1>Purchase Order #: BCIT---><?php //echo $row['0']['OrderID'] ?><!--</h1>-->


        <form class="formLayout" action="index4.php" method="POST">
            <fieldset class="table">
                <legend>Supplier</legend>


                <div class="tr">

                    <div class="td right">Supplier:</div>
                    <div class="td">


                        <select name="supplier" id="suppSelID" onchange="showVendor(this.value)">
                            <script>
                                console.log(this.value);
                            </script>
                            <?php
                            $queryForSuppliers = "SELECT SupplierID, SupName FROM Supplier";

                            $resultsOfSupplierQuery = $db->query($queryForSuppliers);
                            //                            works:
                            //                            $sql = sqlsrv_query($db, $queryForSuppliers);
                            echo "hello";
                            while ($row = $resultsOfSupplierQuery->fetch(PDO::FETCH_ASSOC)) {

                                echo '<option value="' . $row['SupplierID'] . '">' . $row['SupName'] . '</option>';
//                                var_dump($row);
                            }

                            ?>
                        </select>
                        <!--                    </div>-->
                        <!--                </div>-->
                        <!--            </fieldset>-->
                        <!--        </form>-->
                        <br/>
                        <div id="txtHint"></div>
                        <?php
                        echo $row['SupplierID'];
                        //                        var_dump($row['SupplierID'])

                        ?>


                    </div>

                </div>


                <!---->
                <!--                <div class="tr">-->
                <!--                    <div class="td right">Contract Number</div>-->
                <!--                    <div class="td"><input type="text" name="callDate" value="User-entered Contract#"></div>-->
                <!---->
                <!--                </div>-->
            </fieldset>
            <fieldset class="table">
                <legend>Order</legend>
                <div class="tr">
                    <div class="td right">Ordered for Office:</div>
                    <select name="office" id="officeSelID">
                        <?php
                        $queryForOffices = "SELECT OfficeID,OffDescription FROM Office";

                        $sql = $db->query($queryForOffices);

//                        $sql = sqlsrv_query($db, $queryForOffices);
                        echo "hello";
                        while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
//                        while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                            echo '<option value="' . $row['OfficeID'] . '">' . $row['OffDescription'] . '</option>';
                            var_dump($row);
                        }

                        ?>
                    </select>
                </div>

                <div class="tr">
                    <div class="td right">Ordered by:</div>
                    <select name="office" id="officeSelID">
                        <?php
                        $queryForPuchaser= "SELECT PurchaserID,PurName FROM Purchaser";

                        $resultsOfQueryforPurchaser = $db->query($queryForPuchaser);

//                        $sql = sqlsrv_query($db, $queryForPuchaser);

                        echo "hello";
                        while ($row = $resultsOfQueryforPurchaser->fetch(PDO::FETCH_ASSOC)) {
//                        while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {

                            echo '<option value="' . $row['PurchaserID'] . '">' . $row['PurName'] . '</option>';
                            var_dump($row);
                        }

                        ?>
                    </select>
                </div>

                <div class="tr">
                    <div class="td right">Date Ordered:</div>
                    <div class="td"><input type="text" class="autoFillDate" name="callDate"></div>

                    <div class="td right">Date Received:</div>
                    <div class="td"><input type="text" class="datepicker2" name="crimeDate"></div>
                </div>


                <div class="tr">
                    <div class="td right">Order complete?</div>
                    <div class="td"><input type="checkbox" id="subscribeNews" name="subscribe" value="newsletter"></div>
                </div>


            </fieldset>

            <fieldset>
                <legend>Items:</legend>

                <input type="button" value="Add" onClick="addRow('dataTable', 'grandTotal')" />
                <input type="button" value="Remove" onClick="deleteRow('dataTable')" />
                <table border="1">
                    <tr>
                        <th id="lineNum">Line#</th>
                        <th id="chkBxHdr">&#x2713;</th>
                        <th id="contractNum">Contract#</th>
                        <th id="descr">Description</th>
                        <th id="qty">Qty</th>
                        <th id="priceEach">Each</th>
                        <th id="priceTotal">Line item total:</th>
                    </tr>
                </table>
                <table id="dataTable" border="1">
                    <tbody>
                    <tr id='row_0'>
                        <p>
                        <td id="tdLineNum">
                            <!--                            <input type="text" id="tdLineNum"  required="required" name="lineNum" readonly value="1">-->
                        </td>
                        <td>
                            <input type="checkbox" id="chkBx">
                        </td>
                        <td>
                            <input type="text" id="tdContractNum" required="required" name="contractNum">
                        </td>
                        <td>
                            <input type="text" id="tdDesc" required="required" name="desc">
                        </td>
                        <td>
                            <input type="text" id="tdQty" required="required" name="qty" oninput="calculate('row_0')">
                        </td>
                        <td>
                            <input type="text" id="tdPriceEach" required="required" name="price" oninput="calculate('row_0')">
                        </td>
                        <td>
                            <input type="text" class="tdPriceTotal" required="required" name="total" onkeyup="calcGrandTot()">
                        </td>
                        </p>
                    </tr>
                    </tbody>
                </table>
                <table align="right" border="1" style="margin: 0px 21px 0px 0px;">
                    <!--                    <table align="right" style="padding-right: 20px" border="1">-->
                    <tbody>
                    <tr id="grandTotalRow" >
                        <!--                    <tr id="grandTotalRow" style="padding-right: 20px">-->
                        <td >
                            <!--                        <td style="padding-right: 20px">-->
                            <label for="grandTotal">Grand Total: $</label>
                            <input type="text" id="grandTotal">
                        </td>
                    </tr>
                    </tbody>
                </table>

            </fieldset>


            <fieldset>
                <legend>Order Notes:</legend>


                <textarea name="orderSumOfInfo" rows="13" cols="94" >Enter notes here</textarea>

            </fieldset>

            <input type="submit" name='submitOrder' value='Submit Order'/>
            <!--            <input type="submit" name='submitViolentCrimeData' value='Submit report'/>-->


        </form>
    </div>

<script type="text/javascript">
//            var initialLineNum = 2;
//            var runningTotal = 0;
        function addRow(tableID) {
//            alert("in addRow");

//           get a reference to the table
//            var name1 = document.getElementsByName("desc")[0];
//console.log(name1);
//name1.setAttribute("name", "desc_1");
//console.log(name1);
            var table = document.getElementById(tableID);

//              HTMLTableElement.rows read-only property returns a live HTMLCollection of all the rows in the table.
// The rows included in the associated <thead>, ,<tfoot> and <tbody> elements. Although the property is read-only,
// the retuned object is live and allows th modification of its content.
            var rowCount = table.rows.length;
            if (rowCount < 100) { // limit the user from creating fields more than your limits

//                Insert a row in the table at row index(rowCount) and return a reference to the row
                var row = table.insertRow(rowCount);

//                alert(rowCount);
                var colCount = table.rows[0].cells.length;
                row.id = 'row_' + rowCount;


                for (var i = 0; i < colCount; i++) {

//                    Insert a cell in the row
                    var newcell = row.insertCell(i);
                    newcell.outerHTML = table.rows[0].cells[i].outerHTML;
                }
                var listitems = row.getElementsByTagName("input");

//              set various attribute values
                for (i = 0; i < listitems.length; i++) {
//                alert("hello");


                        listitems[i].setAttribute("oninput", "calculate('" + row.id + "')");

                }

            } else {
                alert("Maximum 100 items per purchase order.");

            }
//            initialLineNum++;
        }


        function deleteRow(tableID) {
//            alert("in deleteRow");
            var table = document.getElementById(tableID);
            var rowCount = table.rows.length;
            for (var i = 0; i < rowCount; i++) {
//            alert(rowCount);
                var row = table.rows[i];
//            alert(document.getElementById("dataTable").rows[0].innerHTML);
                var chkbox = row.cells[1].childNodes[1];
                if (chkbox !== null && chkbox.checked === true) {
//            alert(chkbox);
                    if (rowCount <= 1) { // limit the user from removing all the fields
                        alert("Cannot remove all the items in the list.");
                        break;
                    }
                    table.deleteRow(i);
                    rowCount--;
                    i--;
                }
            }
            // need to re initialize the row values here
            var tableRows = table.getElementsByTagName("tr");
            console.log(row);
// for echoing to the console (HTML page > right click > Inspect Element (or type Q) > Console tab
//console.log(tableRows);
            for (var j = 0; j < rowCount; j++){
            row.id = 'row_' + j;

                tableRows[j].setAttribute("id", "row_" + j);



            // need to re-initialize the calculated values here
            var calcFields = row.getElementsByTagName("input");
                for (k = 0; k < calcFields.length; k++) {
//                alert("hello");

                calcFields[k].setAttribute("oninput", "calculate('" + row.id + "')");
                }

            }

//console.log(tableRows);
            calcGrandTot();
        }

            function calculate(elementID) {
                var mainRow = document.getElementById(elementID);
                var myBox1 = mainRow.querySelectorAll('[name=qty]')[0].value;
                var myBox2 = mainRow.querySelectorAll('[name=price]')[0].value;
                var total = mainRow.querySelectorAll('[name=total]')[0];
                var myResult1 = myBox1 * myBox2;
                total.value = myResult1.toFixed(2);

            }


    </script>

#10

@droopsnoot Making progress. I think next time I'm in the office (Wednesday) I will rename my input "name="names"" to arrays (i.e. name="contract[]", name="desc[]", etc.). It seems that is probably the way to go to insert the data into the table per https://stackoverflow.com/questions/18156505/insert-multiple-fields-using-foreach-loop Unless there is a better way you see. Obviously I will need to deal with the other input fields that are not part of the list of items to be purchased, but I'll cross that bridge when I get to it. Many thanks for your help thus far. Kind regards, Jonathan


#11

That's a reasonable approach in terms of parsing the arrays that come in from your form, but obviously don't take a step back and use the obsolete mysql_ functions that abound in that thread. Indeed, it's an ideal use for prepared statements:

$query = "insert into tablename (contract, desc, qty, price) values (:contract, :desc, :qty, :price);
$p = $db->prepare($query);
for ($i=0; $i< count($contractarray); $i++) { 
  // obviously do some validation first
  $p->bindParam(":contract", $contractarray[$i]);
  $p->bindParam(":desc", $descarray[$i]);
  $p->bindParam(":qty", $qtyarray[$i]);
  $p->bindParam(":price", $pricearray[$i]);
  $p->execute();
  }

so you prepare the statement once, then just bind the values to it and execute, for each iteration of the loop. Obviously first of all you need to extract the $_POST values into individual arrays, do some validation and bounds checking, and I've guessed on one array to count, you'd of course need to check that all arrays have that number of elements, as part of the validation.


#12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.