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_ database extension in new code a Kitten is strangled somewhere in the world 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 — RiggsFolly
– RiggsFolly, Commented Feb 21, 2017 at 19:28

Jim Rubenstein · Accepted Answer · 2017-02-21 19:37:50Z

1

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).

answered Feb 21, 2017 at 19:37

Jim Rubenstein

6,9505 gold badges38 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

clearshot66 · Accepted Answer · 2017-02-21 19:30:01Z

0

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

answered Feb 21, 2017 at 19:30

clearshot66

2,2821 gold badge10 silver badges17 bronze badges

Comments

dashred · Accepted Answer · 2017-02-21 19:36:33Z

0

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>";
}

answered Feb 21, 2017 at 19:36

dashred

1411 silver badge12 bronze badges

Collectives™ on Stack Overflow

MySQL/PHP - displays only the first row twice

3 Answers 3

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

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

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

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related