Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts

    Select Field Populated By MYSQL DB

    I'm writing a script, and part of that script has to pull data out of a database and display it in an html select field.

    Below is my code with. It all works fine. My issue is that instead of displaying all of the database table rows inside of ONE select field, a new select field is generated for every table row.

    How can I alter the code to display all of the rows in ONE SINGLE select field? Any help is much appreciated.

    PHP Code:
    <?php
    $username
    ="...";
    $password="...";
    $database="...";
    $host=".....";
    mysql_connect($host,$username,$password);
    @
    mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM news_cats";
    $result=mysql_query($query);
    $num=mysql_numrows($result);
    mysql_close();
    $i=0;
    while (
    $i $num) {
    $f1=mysql_result($result$i"cat");
    echo 
    "<select>";
    echo 
    "<option name=\"cat\">$f1</option>";
    echo 
    "</select>";
    $i++;
    }
    ?>
    Last edited by stevenmw; 01-22-2012 at 05:16 AM.

  • #2
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Quote Originally Posted by stevenmw View Post
    I'm writing a script, and part of that script has to pull data out of a database and display it in an html select field.

    Below is my code with. It all works fine. My issue is that instead of displaying all of the database table rows inside of ONE select field, a new select field is generated for every table row.

    How can I alter the code to display all of the rows in ONE SINGLE select field? Any help is much appreciated.

    PHP Code:
    <?php
    $username
    ="...";
    $password="...";
    $database="...";
    $host=".....";
    mysql_connect($host,$username,$password);
    @
    mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM news_cats";
    $result=mysql_query($query);
    $num=mysql_numrows($result);
    mysql_close();
    $i=0;
    while (
    $i $num) {
    $f1=mysql_result($result$i"cat");
    echo 
    "<select>";
    echo 
    "<option name=\"cat\">$f1</option>";
    echo 
    "</select>";
    $i++;
    }
    ?>
    You've approached this...strangely. Why are you using mysql_result, instead of mysql_fetch_assoc with a while loop for example? Kind of like this:
    PHP Code:
    while($info mysql_fetch_assoc($result)){
        
    // Do something for this row

    Means you don't have to keep a counter or anything. Also, it makes much more sense to just select cat in your query instead of retrieving all the fields for that table.

    Regardless, your problem is your select tag echo is inside your while statement - so it gets echo'd every time you loop.
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • Users who have thanked BluePanther for this post:

    stevenmw (01-22-2012)

  • #3
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    Thanks. I'll work on some revisions.

  • #4
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    I came up with a very basic revision for test purposes.

    PHP Code:
    <?php
    $username
    ="...";
    $password="...";
    $database="...";
    $host="....";
    mysql_connect($host,$username,$password);
    @
    mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM news_cats";
    $result=mysql_query($query);
    echo 
    "<select>";
    while (
    $row mysql_fetch_assoc($result)) {
    echo 
    "<option>";
    echo 
    $row["cat"];
    echo 
    "</option>";
    }
    echo 
    "</select>";
    ?>
    Works great, thanks.

  • #5
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Quote Originally Posted by stevenmw View Post
    I came up with a very basic revision for test purposes.

    PHP Code:
    <?php
    $username
    ="...";
    $password="...";
    $database="...";
    $host="....";
    mysql_connect($host,$username,$password);
    @
    mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM news_cats";
    $result=mysql_query($query);
    echo 
    "<select>";
    while (
    $row mysql_fetch_assoc($result)) {
    echo 
    "<option>";
    echo 
    $row["cat"];
    echo 
    "</option>";
    }
    echo 
    "</select>";
    ?>
    Works great, thanks.
    I would suggest changing your query to SELECT cat FROM news_cats - no need to retrieve all the extra information if all you're using is the one field. Always select the fields you're going to use, and only use the * if you're going to use every single one
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • #6
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    Thanks for the tip. I'm going to be using the id field soon so I just called it all at once.

    Thanks again.

  • #7
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    Quote Originally Posted by stevenmw View Post
    Thanks for the tip. I'm going to be using the id field soon so I just called it all at once.

    Thanks again.
    SELECT id,cat FROM news_cats

    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.


  •  

    Posting Permissions

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