doujiang1993 2015-10-20 17:59
浏览 21
已采纳

用于一次填充多个列表框的SQL语句

I'm trying to allow a user to specify how many rows they would like to add to the order form for the customer's purchase. This allows the user to have as many rows as needed for purchasing products rather than having a set list. I have the functionality working properly, where if you type in 3 and submit, it will give you three rows to enter in product order information. The problem I am running into is where I am populating a listbox with the product id and name for the user to select. It populates the first row's list box, but the following list boxes only get the " - " and not the $row[] values. It seems like it's not passing in the sql statement anymore, why is this?

This is the area in my code where I'm running into a problem with the functionality:

<?
            if (isset($_POST['update']))
            {
                //Execute this code if the update button is clicked.
                $num = $_POST['rows'];

                for ($i=0; $i<$num; $i++) { ?>

                    <tr>
                        <td class="inputCol2">
                            <select name="'product<?= $i ?>">
                                <option value="selectProduct">Select Product</option>
                                <!-- Populate listbox with Product ID and Product Name -->
                                <?
                                do { ?>

                                    <option value="<?= $row[0]; ?>"><?= $row[0] . " - " . $row[2]; ?></option>

                                <? } while($row = mysqli_fetch_array($result)) ?>

                            </select>
                        </td>
                        <td class="inputCol2"><input type="text" name="'quantity<?= $i ?>" ></td>
                        <td class="inputCol2">$<input type="text" name="'unit<?= $i ?>" value=""></td>
                        <td class="inputCol2">$<input type="text" name="'total<?= $i ?>" value="" ></td>
                    </tr>
                <? } ?>

And this is my entire code:

<? 
        connectDB();

        $sql = "SELECT * FROM product";
        $sql2 = "SELECT DISTINCT emp_id, emp_fname, emp_lname FROM employee";
        $sql3 = "SELECT DISTINCT status_id FROM salesorder ORDER BY status_id asc";
        $sql4 = "SELECT * FROM salesorder ORDER BY order_id desc";

        $result = mysqli_query($db, $sql) or die("SQL error: " . mysqli_error());
        $result2 = mysqli_query($db, $sql2) or die("SQL error: " . mysqli_error());
        $result3 = mysqli_query($db, $sql3) or die("SQL error: " . mysqli_error());
        $result4 = mysqli_query($db, $sql4) or die("SQL error: " . mysqli_error());

        $row = mysqli_fetch_array($result);
        $row2 = mysqli_fetch_array($result2);
        $row3 = mysqli_fetch_array($result3);
        $row4 = mysqli_fetch_array($result4);

        ?>

    <div id="order-wrap">
        <form method="post" action="order.php">
            <table class="orderInfo"><br>
                <tr>
                    <th class="textCol">Product Rows:</th>
                    <td class="inputCol"><input type="text" name="rows"></td>
                    <td><input class="update" type="submit" name="update" value="Update"></td>
                    <td class="inputCol"></td>
                </tr>
            </table>
        </form><!-- Order Rows -->
        <form class="orderform" action ="order-report.php"  METHOD = "post">
            <h2>Order Form</h2>
            <h3>Piedmont Furnishings</h3>
            <img id="couch-img" src="couch.jpg" alt="couch">
            <table class="orderInfo">
                <tr>
                    <th class="textCol">Order Number:</th>
                    <td class="inputCol"><input type="text" name="orderNumber" value="<?= $row4[0] + 1; ?>" disabled></td>
                    <th class="textCol">Order Date:</th>
                    <td class="inputCol"><input type="text" name="orderDate" value="<?= date("Y-m-d") ?>"></td>
                </tr>
                <tr>
                    <th class="textCol">Customer:</th>
                    <td class="inputCol"><input type="text" name="customer"></td>
                    <td class="textCol"></td>
                    <td class="inputCol"></td>
                </tr>
                <tr>
                    <th class="textCol">Sales Agent:</th>
                    <td class="inputCol">
                        <select name="salesAgent">
                            <option value="selectAgent">Select One</option>
                            <!-- Populate listbox with Sales Agents ID -->
                            <?
                            do { ?>

                                <option value="<?= $row2[0]; ?>"><?= $row2[1] . " " . $row2[2]; ?></option>

                            <? } while($row2 = mysqli_fetch_array($result2)) ?>

                        </select>
                    </td>
                    <th class="textCol">Order Status:</th>
                    <td class="inputCol">
                        <select name="orderStatus">
                            <option value="selectStatus">Select One</option>
                            <!-- Populate listbox with Status ID -->
                            <?
                            do { ?>

                                <option value="<?= $row3[0]; ?>"><?= $row3[0] ?></option>

                            <? } while($row3 = mysqli_fetch_array($result3)) ?>

                        </select>
                    </td>
                </tr>
            </table>

        <!-- Where the product rows input show go ??? -->
        <table class="bottomTable">
            <tr>
                <th class="textCol">Product</th>
                <th class="textCol">Quantity</th>
                <th class="textCol">Unit Price</th>
                <th class="textCol">Total Price</th>
            </tr>
        <?
            if (isset($_POST['update']))
            {
                //Execute this code if the update button is clicked.
                $num = $_POST['rows'];

                for ($i=0; $i<$num; $i++) { ?>

                    <tr>
                        <td class="inputCol2">
                            <select name="'product<?= $i ?>">
                                <option value="selectProduct">Select Product</option>
                                <!-- Populate listbox with Product ID and Product Name -->
                                <?
                                do { ?>

                                    <option value="<?= $row[0]; ?>"><?= $row[0] . " - " . $row[2]; ?></option>

                                <? } while($row = mysqli_fetch_array($result)) ?>

                            </select>
                        </td>
                        <td class="inputCol2"><input type="text" name="'quantity<?= $i ?>" ></td>
                        <td class="inputCol2">$<input type="text" name="'unit<?= $i ?>" value=""></td>
                        <td class="inputCol2">$<input type="text" name="'total<?= $i ?>" value="" ></td>
                    </tr>
                <? } ?>
                    <tr>
                        <td class="textCol"></td>
                        <td class="textCol"></td>
                        <td class="textCol">Total Order:</td>
                        <td class="inputCol2">$<input type="text" name="totalfinal"></td>
                    </tr>
                    <input class="submit" type="submit" value="Submit" name="orderSubmit"/> 
                </table>
            </form>
            <? } else {?>
                    <tr>
                        <td class="textCol"></td>
                        <td class="textCol"></td>
                        <td class="textCol">Total Order:</td>
                        <td class="inputCol2">$<input type="text" name="totalfinal"></td>
                    </tr>
                    <input class="submit" type="submit" value="Submit" name="orderSubmit"/> 
                </table>
            </form>
            <? } ?>
       <?
            mysqli_free_result($result);
            mysqli_close($db);
        ?>
    </div>

展开全部

  • 写回答

1条回答 默认 最新

  • douzi1991 2015-10-20 21:51
    关注

    the problem with your code is for first iteration while($row = mysqli_fetch_array($result)) the internal pointer of $result reached at the end... so for next iteration $i=1 there is nothing in the $result but As you use do-while loop the loop must run at least one time and $row[0] & $row[2] is null so you get only "-" . to fix the problem you need to change code slightly.

    remove this line $row = mysqli_fetch_array($result);

    and add

    $options = '<option value="selectProduct">Select Product</option>';
    
    while($row = mysqli_fetch_array($result,MYSQLI_NUM)){
    
        $options .= '<option value="'.$row[0].'">'.$row[0].' - '.$row[1].'</option>';
    }
    

    then change like this inside for loop :

    <td class="inputCol2">
      <select name="'product<?= $i ?>">
         <?php
           echo $options;
         ?>
       </select>
     </td>
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 PADS Logic 原理图
  • ¥15 PADS Logic 图标
  • ¥15 电脑和power bi环境都是英文如何将日期层次结构转换成英文
  • ¥20 气象站点数据求取中~
  • ¥15 如何获取APP内弹出的网址链接
  • ¥15 wifi 图标不见了 不知道怎么办 上不了网 变成小地球了
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部