SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Populate dropdown menu from MySQL database?

    I would like to create a dropdown menu in PHP that loops through a MySQL database table of names. The dropdown will just display the name and the user can select the name for form submission. Each name in the table also has a unique id. Im totally lost on this one... Any help would be great! Thanks

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you know how to get a result set from a SELECT query from MySQL?
    The markup you're aiming for will look a bit like this:
    Code HTML:
    <select name="cities">
      <option value="12">New York</option>
      <option value="31">Singapore</option>
      <option value="44">Bejing</option>
    </select>

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI you can do this way
    PHP Code:
    //db connection 
    mysql_connect("localhost","user","password");
    mysql_select_db("database");

    //query
    $sql=mysql_query("SELECT id,name FROM table");
    if(
    mysql_num_rows($sql)){
    $select'<select name="select">';  
    while(
    $rs=mysql_fetch_array($sql)){
          
    $select.='<option value="'.$rs['id'].'">'.$rs['name'].'</option>';
      }
    }
    $select.='</select>';
    echo 
    $select

  4. #4
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

  5. #5
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually that did not work

  6. #6
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    elaborate Airludy, did you get errors? No output? what?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  7. #7
    SitePoint Member
    Join Date
    Aug 2010
    Location
    Cincinnati, OH
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post

    Just did this today actually lol. It's in OO if you are familiar

    PHP Code:
    <?php

    $conn 
    = new mysqli('localhost''username''password''database'
    or die (
    'Cannot connect to db');

        
    $result $conn->query("select id, name from table");
        
        echo 
    "<html>";
        echo 
    "<body>";
        echo 
    "<select name='id'>";

        while (
    $row $result->fetch_assoc()) {

                      unset(
    $id$name);
                      
    $id $row['id'];
                      
    $name $row['name']; 
                      echo 
    '<option value="'.$id.'">'.$name.'</option>';
                     
    }

        echo 
    "</select>";
        echo 
    "</body>";
        echo 
    "</html>";
    ?>
    Or you can make it into a function and place it in the middle of html code and get rid of that echo <html>, <body> and all that
    Hope this helps!

  8. #8
    SitePoint Member
    Join Date
    Jun 2010
    Location
    Bucharest environs, Romania
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I managed to make work to me ... Here's the code I found today (and a little modified) - AT THE END of all this I'll put a simple question (I cannot find the answer on PHP Manual or in other places ...)

    ----------------------------------------------------------------------------------------------
    A database with 2 tables (I didn't like at all that the name of the second field is exactly the name of the table ... but that is what I found ...)

    Database
    Code:
    CREATE TABLE category ( cat_id int(2) NOT NULL auto_increment, category varchar(25) NOT NULL default '', PRIMARY KEY (cat_id) ) TYPE=MyISAM;
    
    
    INSERT INTO category VALUES (1, 'Fruits'); INSERT INTO category VALUES (2, 'Colors'); INSERT INTO category VALUES (3, 'Games'); INSERT INTO category VALUES (4, 'Vehicles');
    
    
     # -------------------------------------------------------- # # 
     # -------------------------------------------------------- # # 
     # -------------------------------------------------------- # # 
    
    Table structure for table `subcategory` # 
    
    CREATE TABLE subcategory ( cat_id int(2) NOT NULL default '0', subcategory varchar(25) NOT NULL default '' ) TYPE=MyISAM; 
    # # Dumping data for table `subcategory` # 
    
    INSERT INTO subcategory VALUES (1, 'Mango'); INSERT INTO subcategory VALUES (1, 'Banana'); INSERT INTO subcategory VALUES (1, 'Orange'); INSERT INTO subcategory VALUES (1, 'Apple'); INSERT INTO subcategory VALUES (2, 'Red'); INSERT INTO subcategory VALUES (2, 'Blue'); INSERT INTO subcategory VALUES (2, 'Green'); INSERT INTO subcategory VALUES (2, 'Yellow'); INSERT INTO subcategory VALUES (3, 'Cricket'); INSERT INTO subcategory VALUES (3, 'Football'); INSERT INTO subcategory VALUES (3, 'Baseball'); INSERT INTO subcategory VALUES (3, 'Tennis'); INSERT INTO subcategory VALUES (4, 'Cars'); INSERT INTO subcategory VALUES (4, 'Trucks'); INSERT INTO subcategory VALUES (4, 'Blkes'); INSERT INTO subcategory VALUES (4, 'Train');
    index.php file

    PHP Code:
    <?php

    # Connection 
    // I define constants (but I would prefer to put this start of the page, with 
    //this php code, somewhere else, as I have read, you know, for security
    //... but where to put it on the server and then include it ?...)

    DEFINE ('DB_USER''username');
    DEFINE ('DB_PASSWORD''password');
    DEFINE ('DB_HOST''mysqlhost(server)');
    DEFINE ('DB_NAME''databasename');

    // Connection with MySQL server

    $dbc = @mysql_connect (DB_HOSTDB_USERDB_PASSWORD) OR die ('Cannot connect to MySQL server: '.mysql_error());

    // Select database

    @mysql_select_db (DB_NAME) OR die ('Cannot select database: '.mysql_error());

    echo 
    " YEAP, it did connect !!! ";

    ?>

    <html lang="en-en" >

    <head>
    <title>2 Dropdown lists - populated from MySQL database</title>

    <SCRIPT language=JavaScript>
    function reload(form)
    {
    var val=form.cat.options[form.cat.options.selectedIndex].value;
    self.location='index.php?cat=' + val ;
    }
    </script>

    </head>

    <body>

    <br /> <br /> <br />

    <?php

    @$cat=$_GET['cat']; // Use this line or below line if register_global is off

    if(strlen($cat) > and !is_numeric($cat)){ // to check if $cat is numeric data or not. 
    echo "Data Error";
    exit;
    }


    @
    $cat=$HTTP_GET_VARS['cat']; // Use this line or above line if register_global is off



    ///////// Getting the data from Mysql table for first list box//////////
    $quer2 mysql_query("SELECT DISTINCT category,cat_id FROM category ORDER BY category");
    ///////////// End of query for first list box////////////

    /////// for second drop down list we will check if category is selected else we will display all the subcategory/////
    if(isset($cat) and strlen($cat) > 0){
    $quer mysql_query("SELECT DISTINCT subcategory FROM subcategory WHERE cat_id=$cat ORDER BY subcategory"); 
    }
    else{
    $quer mysql_query("SELECT DISTINCT subcategory FROM subcategory ORDER BY subcategory"); } 
    ////////// end of query for second subcategory drop down list box ///////////////////////////


    echo "<form method=post name=f1 action='index-check.php'>";

    /// Add your form processing page address to action in above line. Example  action=index-check.php////

    ////////////////////////////////////////////////////////

    //////////  START of the FIRST drop downlist  /////////
    echo "<select name='cat' onchange=\"reload(this.form)\"><option value=''>Select Category</option>";

    while(
    $noticia2 mysql_fetch_array($quer2))

      if(
    $noticia2['cat_id']==@$cat
        {echo 
    "<option selected value='$noticia2[cat_id]'>$noticia2[category]</option>"."<BR>";}
      else{echo  
    "<option value='$noticia2[cat_id]'>$noticia2[category]</option>";}
    }

    echo 
    "</select>";

    //////////  END the the FIRST drop down list  ///////////

    ?>

    &nbsp;&nbsp;&nbsp;

    <?php

    ////////////////////////////////////////////////////////

    //////////  START of the SECOND drop downlist  /////////
    echo "<select name='subcat'><option value=''>Then select subCategory</option>";

    while(
    $noticia mysql_fetch_array($quer))

    echo  
    "<option value='$noticia[subcategory]'>$noticia[subcategory]</option>";
    }

    echo 
    "</select>";

    //////////  END the the SECOND drop down list  ///////////

    ?>

    &nbsp;&nbsp;&nbsp;

    <?php

    //// Add your other form fields as needed here/////

    echo "<input type=submit value=Submit>";
    echo 
    "</form>";

    ?>

     
    </body>

    </html>
    And the file
    index-check.php

    PHP Code:

    <html lang="en-en" >
    <head>
    <title>Multiple drop down lists</title>
    </head>

    <body>

    <?php
    $cat
    =$_POST['cat'];
    $subcat=$_POST['subcat'];

    echo 
    "Value of \$cat = $cat <br>Value of \$subcat = $subcat ";
    echo 
    "<p></p>";
    echo 
    "I'm interested in  ***** (variable)";

    ?>

    </body>

    </html>
    Now :

    What on Earth is NOTICIA ???????????!!!!!!!!!!!!
    It works, but what is NOTICIA ??!!

    Thanks !


  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,023
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Notica? Are you getting a Notice error from PHP? If so can you post the text of the error?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  10. #10
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Couple of things
    1/ get rid of this line:
    PHP Code:
    @$cat=$HTTP_GET_VARS['cat']; // Use this line or above line if register_global is off 
    its really not required.

    2/ take the @signs off the mysql lines. It suppresses the errors that you will need in case it all goes wrong.

    3/ noticia is just the simple variable assigned to hold the results of the query which are returned as an array.
    You could call it anything you like just as arron did in post #7, he called it $row.
    PHP Code:
    while ($row $result->fetch_assoc()) { 
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •