selectbox with multiple columns from mysql database

Question

I have the following php code and it's working great for showing 1 column, but I need it to show the values of 10 columns.

         <select size="1" name="domeinnaam"> 
      <?php 
      include '../config.php';
      $sql = "SELECT * FROM megabase";  
      $resultaat = mysql_query($sql) or die (mysql_error());  
      while ($row = mysql_fetch_array($resultaat))       
      { 
      echo '<option>' . $row['domeinnaam1'] . '</option>';
      }
      ?>
     </select>

I have tried to add a 2nd echo, but that corrupted the code. I also tried to

      echo '<option>' . $row['domeinnaam1'] . $row['domeinnaam2'] . '</option>';

but that didnt work. because the result will then display as follows: domain1
domain1
domain1domain2

and it should be

domain1
domain1
domain1
domain2

What will work?

First, mysql_ are not safe anymore. Second, I suspect you have 10 columns with differnt values right? Try doing something with a index-number. Before the while use $i = 1; And then add $i = $i+1; in the loop and name your var something like $row['domeinnaam'+$i], it's ugly but will work. — Tim, Mar 04 '13 at 15:11
Basically what you want to have is a table inside a select element? — Till Helge, Mar 04 '13 at 15:11
Tim, I have a database with the following columns: id, customer, domain1,domain2,domain3,domain4 etc to 10. So i want to show all domainnames in a dropdown box — , Mar 04 '13 at 15:17
Till Helge Helwig, No i want to have all the values which are domainnames to be displayed like all dropdowns are displayed I dont want table view. — , Mar 04 '13 at 15:19
Are you trying to have each value in a separate option in the select? Like etc... If so are the 10 fields named domeinnaam1, domeinnaam2, domeinnaam3, etc. ? — zgr024, Mar 04 '13 at 15:25

zgr024 · Accepted Answer · 2013-03-04T15:50:16.963

0

Assuming you want each domain name to appear as an option in the select and the domain name fields in your db are domeinnaam1, domeinnaam2, domeinnaam3, etc., you would do the following...

<?php 
        include '../config.php';
        $sql = "SELECT * FROM megabase";  
        $resultaat = mysql_query($sql) or die (mysql_error());  
        $domains = array();
        while ($row = mysql_fetch_array($resultaat))       
        { 
           if (!empty($row['domeinnaam1'])) $domains[] = $row['domeinnaam1'];
           if (!empty($row['domeinnaam2'])) $domains[] = $row['domeinnaam2'];
        }
    ?>

<select size="1" name="domeinnaam">
    <?php
        foreach ($domains as $domain)
        {
            echo "<option>$domain</option>";
        } 
    ?>
</select>

You should use PDO instead of mysql_ functions or the ADODB library works well. mysql_ functions are deprecated as of PHP 5.5

refer to http://www.php.net/manual/en/pdo.construct.php for PDO reference

edited Mar 04 '13 at 15:50

answered Mar 04 '13 at 15:32

zgr024

1,175
1
12
26

Thanks zgr024, It shows the first and 2nd domainname but it should show 10. In the column domeinnaam1 are 9 records and in column domeinnaam2 is 1 record. – Mar 04 '13 at 15:37
You should then just pull all the records and place the domain names in array and echo the array in the select. I've updated the code – zgr024 Mar 04 '13 at 15:38
Oke with the updated code i get all the results but it also shows a lot of white spaces because there are more customers with 1 domain than with 2. is there a way to ignore empty records. zrg024 Thanks for getting me so far – Mar 04 '13 at 15:44
I've updated the code to ignore values of `''` which means empty strings – zgr024 Mar 04 '13 at 15:49
beautiful peace of code it works like a charm would you mind helping me with an error that i receive on the next page of the form i open a new question for this i will put a link here – Mar 04 '13 at 15:55
the link to the next page problem is here: http://stackoverflow.com/questions/15205771/multiple-columns-in-where-clause-query-mysql – Mar 04 '13 at 16:05
I've posted my answer to that as well, glad I could help – zgr024 Mar 04 '13 at 16:24

score 0 · Answer 2 · answered Mar 04 '13 at 16:02

Your code should not have major PHP configurations or initializations like this. I does not look smart. Tidy it up.

<select size="1" name="domeinnaam"> 
  <?php 
  include '../config.php';
  $sql = "SELECT * FROM megabase";  
  $resultaat = mysql_query($sql) or die (mysql_error());  
  while ($row = mysql_fetch_array($resultaat))       
  { 
  echo '<option>' . $row['domeinnaam1'] . '</option>';
  }
  ?>
 </select>


  <?php 
  include '../config.php';
  $sql = "SELECT * FROM megabase";  
  $resultaat = mysqli_query($conn, $sql) or die ($conn->mysqli_error());  

  ?>
<select size="1" name="domeinnaam">
<?php
while ($row = mysqli_fetch_array($conn, $resultaat))       
      { 
      echo '<option>' . $row['domeinnaam1'] . '</option>';
      }
?>


 </select>

Next don't really try to break options, you could use javascript frameworks like JQuery to achieve a more stylish select. I don't really think you can do this:

while ($row = mysqli_fetch_array($conn, $resultaat))       
  { 
  echo '<option>' . $row['domeinnaam1'] . '<br />' .$row['domeinnaam2']. '</option>';
  }

Using JQuery, you could a list item in a div:

<ul>
while ($row = mysqli_fetch_array($conn, $resultaat))       
      { 
      echo '<li>' . $row['domeinnaam1'] . '<br />' .$row['domeinnaam2']. '</li>';
      }
</ul>

Then pass the selected item to a hidden field for later use using the onclick event.

If you want to display the 10 columns in one loop as separate options, this could do it.

while ($row = mysqli_fetch_array($conn, $resultaat))       
{ 
echo '<option>' . $row['domeinnaam1'] .'</option>';
echo '<option>' . $row['domeinnaam2'] .'</option>';
echo '<option>' . $row['domeinnaam3'] .'</option>';
echo '<option>' . $row['domeinnaam4'] .'</option>';
echo '<option>' . $row['domeinnaam5'] .'</option>';
echo '<option>' . $row['domeinnaam6'] .'</option>';
echo '<option>' . $row['domeinnaam7'] .'</option>';
echo '<option>' . $row['domeinnaam8'] .'</option>';
echo '<option>' . $row['domeinnaam9'] .'</option>';
echo '<option>' . $row['domeinnaam10'] .'</option>';
}

I wonder where this would be useful and how. Anything can happen in programming. :)

Thanks for your reply Steward. I look into your answer. Thanks once again — , Mar 04 '13 at 16:20
Another good way to say thanks is upvote or accept answer. Thank, :) — Steward Godwin Jornsen, Mar 04 '13 at 16:24
Not to worry, you get on. I just start answer questions 8 days ago. Pretty nice place to be. Someone else would do it for you. — Steward Godwin Jornsen, Mar 04 '13 at 16:48

selectbox with multiple columns from mysql database

2 Answers2

Linked