MySQL/PHP - displays only the first row twice

Question

I'm trying to get my PHP to display all the product names in a dropdown menu. However it only displays the first row twice.

$prodNameSQL="select prodName from product";

//execute SQL query or return an error
$exeProdNameSQL=mysql_query($prodNameSQL) or die(mysql_error());

//create array of records & populate it with result of the execution of the SQL query
$array=mysql_fetch_array($exeProdNameSQL);

echo "<select name='productCombo'>";
foreach($array as $value) {

echo "<option value=".$value.">".$value;
echo "</option>";

}
echo "</select>";

I know that I should be using mysqli and mysql is depreciated but its beyond my control.

Thanks.

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Feb 21 '17 at 19:28

score 1 · Accepted Answer · answered Feb 21 '17 at 19:37

When you call mysql_fetch_array the mysql driver returns a single row from your result set. By executing a foreach over that result ($array) you are effectively looping over the columns in a single row.

You need to execute:

while ($array = mysql_fetch_array($exeProdNameSQL)) {
    // output your dropdown options here
}

This will allow you to iterate over every single result that your query returns.

Also, the mysql extension was deprecated a very long time ago (php5.5), and php5.6 has reach end of life and is basically deprecated as well. The mysql extension is not available php7.0+, so if you ever try to run this code on an up-to-date system, it will fail.

You should strongly consider upgrading to the MySQL PDO driver (http://php.net/manual/en/ref.pdo-mysql.php).

score 0 · Answer 2 · answered Feb 21 '17 at 19:30

Try using result set instead using a fetch_assoc() and then using a while loop like this

while($row = mysql_fetch_assoc($result)){
    echo "<option value=".$row['prodName '].">".$row['prodName ']."</option";

}

http://php.net/manual/en/function.mysql-fetch-assoc.php

score 0 · Answer 3 · answered Feb 21 '17 at 19:36

Check first the $array if it does contain results

<?php 

if ($array) {

while ($row = mysqli_fetch_array($array, MYSQLI_ASSOC)) {
    echo "<option value=".$row['name'].">".$row['value']."</option>";
}

} else {
  echo "<p>". mysqli_error($connection) ."</p>";
}

MySQL/PHP - displays only the first row twice

3 Answers3

You should strongly consider upgrading to the MySQL PDO driver (http://php.net/manual/en/ref.pdo-mysql.php).