Populate html dropdown with data from Microsoft SQL server

jcarlisle2 · October 25, 2017, 10:20pm

Okay, so I’ve been reading / experimenting for several hours now (articles I’ve read: https://stackoverflow.com/questions/8022353/how-to-populate-html-dropdown-list-with-values-from-database, https://stackoverflow.com/questions/26619703/populating-dropdown-box-from-database-php, and several others) all to no avail. I’m guessing it is a simple error on my part, but I’m not seeing it. Basically, the results of echoing my array contents are not populating the option list, but rather echoing on the page. Any help would be much appreciated. Kind regards, Jon
Edit: Mittineague kindly formatted my code - I tried to do that when I posted, but I’m not sure how to do it.

 <select name="owner">
    <?php
    $queryForSuppliers = "SELECT SupplierID, SupName FROM Supplier";
    $sql = sqlsrv_query($db, $queryForSuppliers);
    $row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC);

//    foreach ($row as $item) {
//        echo "<option value=\"SupplierID\">" . $item['SupName'] . "</option>";
//    }
    while ($row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {
        echo "<option value=\"SupplierID\">" . $row['SupName'] . "</option>";
    }
//   var_dump($row);
//   die(1);
    ?>
 </select>

Mittineague · October 25, 2017, 11:15pm

Might it be possible that once the results are fetched the cursor is at the end?

What happens if you comment out the first fetch line?

//  $row = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC);

jcarlisle2 · October 25, 2017, 11:25pm

Mittineague, thank you for your prompt response. I tried your suggestion - but it made no change. :-/

Mittineague · October 25, 2017, 11:31pm

Please post the output between <select .... </select> as it is in the pages view-source.

droopsnoot · October 26, 2017, 9:20am

On this line

echo "<option value=\"SupplierID\">" . $row['SupName'] . "</option>";

is it intentional that you set the value of every option to the same quoted string “SupplierID”? I suspect it should read:

echo "<option value=\"" . $row['SupplierID'] . "\">" . $row['SupName'] . "</option>";

However, if you’re going to break in and out of the string to concatenate variables, you can save a bit of time by using single quotes around each section, so you don’t have to escape the double-quotes:

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

jcarlisle2 · October 27, 2017, 3:18pm

@Mittineague I made corrections to my code (per dropsnoots suggestion) so now I’m getting:

                        <select name="owner">
                            <option value="1">INSIGHT</option><option value="2">C&M BUSINESS MACHINES</option><option value="3">A-R COMPUTER SYSTEMS</option><option value="4">ASTROGRAPHICS</option><option value="5">CHOICE SOLUTIONS L.L.C.</option><option value="6">SOUND.NET</option><option value="7">BYRON JOHNS</option><option value="8">TRAVIS FLOWERS</option><option value="9">LINDA EASLEY</option><option value="10">JOHN DOWNING</option><option value="12">ANDERSON BUSINESS EQUIP</option><option value="13">Computers Plus, INC</option><option value="14">DOC.COM</option><option value="15">DataSoft Consultants Inc</option><option value="16">IKON OFFICE SOLUTIONS</option><option value="17">IBM</option><option value="19">KELLY PRODUCTS</option><option value="20">CLAYTON PAPER</option><option value="21">ISLAND ELECTRIC DISTRIBUTING</option><option value="22">NOVELL</option><option value="23">PROGRESSIVE TELECOM INC.</option><option value="24">BLACK BOX</option><option value="25">RAM ELECTRONICS</option><option value="26">Power Quest</option><option value="27">B.J. OFFICE PROCUCTS</option><option value="28">CORPORATE EXPRESS</option><option value="29">CARD CENTER</option><option value="30">Northern Tool & Equip</option><option value="31">XIOTECH</option><option value="32">WESTERN EXTRA LITE</option><option value="33">Custom Sensors Inc</option><option value="34">CDWG</option><option value="35">AppDev</option><option value="36">KeyStone Learning Systems</option><option value="37">American Electric Co</option><option value="38">ORACLE</option><option value="39">CompuMaster Training</option><option value="40">3Com Corp</option><option value="41">Computer Solutions</option><option value="42">Dell Computer Co</option><option value="43">Electronics Supply</option><option value="44">Solutions 4Sure</option><option value="45">Southern Computer Warehouse</option><option value="46">Office Depot</option><option value="47">Micro Center</option><option value="48">Lan Tell</option><option value="49">RSC INC.</option><option value="51">One Stop Shop</option><option value="52">Infinity Micro</option><option value="53">Regional Supply Center</option><option value="54">PCNation</option><option value="55">PCHardWare4U Inc</option><option value="56">Acer Computer</option><option value="57">Global Reference Guides Inc</option><option value="58">Coast2Coast</option><option value="59">Page Computer</option><option value="60">Computers 4 Sure</option><option value="61">InFocus</option><option value="62">Faronics Technologies USA Inc</option><option value="63">Macromedia</option><option value="64">CompuAmerica</option><option value="65">Micro Focus</option><option value="66">Cyberscience Corporation</option><option value="67">SurfControl</option><option value="68">CompuVest</option><option value="69">INTERNATIONAL LASER GROUP</option><option value="70">Net Studio</option><option value="71">IBM MO State Contract Purchase</option><option value="72">World Wide Technology Inc</option><option value="73">AVP (Projector</option><option value="74">Begin Finite</option><option value="75">Mid-America Regional Council</option><option value="76">Office Max</option><option value="77">Adobe</option><option value="78">SoftwareMore USA</option><option value="79">1ShopDirect</option><option value="80">eBargainSoftware</option><option value="81">Stonewall Cable Inc</option><option value="82">Sprint Wireless</option><option value="83">CablesForLess</option><option value="84">TheNerds.net</option><option value="85">Dartek</option><option value="86">APC</option><option value="87">OceanPC</option><option value="88">Len's Computer Supplies</option><option value="89">Coastal Micro Suply</option><option value="90">2BuyStore</option><option value="91">KVMS.COM</option><option value="92">Soft Source INC.</option><option value="93">Ambir</option><option value="94">Provantage Computer Products</option><option value="95">Market Point</option><option value="96">Kanecal</option><option value="97">Application Data Systems, Inc.</option><option value="98">TSA</option><option value="99">PCA</option><option value="100">Amazon.Com</option><option value="101">VioSoftware Corporation</option><option value="102">Star Toners, Inc.</option><option value="103">Sewell</option><option value="104">Securitycameraworld</option><option value="105">ComputerPlug.Com</option><option value="106">Comp-U-Plus</option><option value="107">Global Knowledge</option><option value="108">Fiber Optic Cable Shop</option><option value="109">Digitally Unique</option><option value="110">NewEgg.Com</option><option value="111">TechSmith Corporation</option><option value="112">Midwest Data Systems Inc</option><option value="113">Calendars</option><option value="114">HP</option><option value="115">PlanetBattery.com</option><option value="116">Drexel Technologies Inc.</option><option value="117">CellPhoneShop.Com</option><option value="118">SoftWare Outlet</option><option value="119">ISC information Support Concep</option><option value="120">Alan Computer Tech</option><option value="121">pcRUSH</option><option value="122">Innovation First Inc</option><option value="123">SpectorSoft</option><option value="124">Web Commerce Partners, Inc.</option><option value="125">Atlanta Network Technologies</option><option value="126">Carahsoft Technology Corp</option><option value="127">Lots Of Laptops</option><option value="128">Bob Johnson�s Computer Stuff Inc.</option><option value="129">Buy.Com</option><option value="130">Expedite</option><option value="131">Trident Contract Management</option><option value="132">ESRI, Inc.</option><option value="133">Feedroller.Com</option><option value="134">Sewell Direct</option><option value="135">Caboodle Cartridge</option><option value="136">Network Integration Services</option><option value="137">PC Universe</option><option value="138">Centriq Training</option><option value="139">MonoPrice.Com</option><option value="140">EBE Printer Supplies</option><option value="141">Antenna Plus, LLC</option><option value="142">ID Solutions</option><option value="143">Roxio, A Division of Sonic Solutions</option><option value="144">Graybar</option><option value="145">Kaga Electronics</option><option value="146"></option><option value="147">CommVault</option><option value="148">IKON Office Solutions, Inc.</option><option value="149">Discount Mountain Software</option><option value="150">GFI Software Florida, Inc.</option><option value="151">Xtreme Electronics</option><option value="152">Primera Technology</option><option value="153">Tiger Direct</option><option value="154">USA Printer Supplies</option><option value="155">Cleanitsupply.com</option><option value="156">PI Manufacturing</option><option value="157">Windstream</option><option value="158">Jarco</option><option value="159">Emerson</option><option value="160">SKC</option><option value="161">Bonwell Designs</option><option value="162">Barracuda Networks</option><option value="163">ZONES</option><option value="164">Civic Plus</option><option value="165">IPSwitch</option><option value="166">Wright Line</option><option value="167">Summation Technology LLC</option><option value="168">MonoPrice</option><option value="169">New Horizons</option><option value="170">TGS</option><option value="171">QuestymeUSA</option><option value="172">SHI</option><option value="173">Stormwind Studios</option><option value="174">Sierra Wireless</option><option value="175">ROK Brothers</option><option value="176">Electronic Technology, Inc.</option>                            </select>

Also, how do I format my code (my original post was poorly formatted and you were kind enough to clean it up for me). On stack overflow I select all > CTRL+k…

EDIT: Ugh!! I just realized I had an opening <select name=“warrExists” from previous code I thought I had deleted.

By removing the erroneous opening select, the problem cleared up entirely (should I add the erroneous opening select to the original code example - just to clarify what the problem was? If so, I don’t see how I can edit the original post). Do I need to “mark this topic as answered”? Stackoverflow seems really particular about making sure a ‘topic/question’ is closed as SOON as it is answered. Also, how do I attribute recognition to the two commenters (Mittineague and droopsnoot) for their help in resolving my issue? Thank you so much for your help!!

jcarlisle2 · October 27, 2017, 3:23pm

@droopsnoot as soon as I saw your code I realized the error in mine. You’re exactly right and I’ve implemented the snippet your provided and so now my key/value pairs are rendering correctly.

benanamen · January 9, 2018, 10:30pm

IMO this is much cleaner and less prone to errors.

echo "<option value='{$row['SupplierID']}'>{$row['SupName']}</option>";

system · April 11, 2018, 5:30am

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