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
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Select Box default selection based on mysql query

    Hi again,

    Its that time for me to admit that I have no idea what I am doing and kneel before the almighty coding gods and ask for their forgiveness as I have failed them in some way......


    Based on a MySQL query I have a select list that is dynamical created. I need it to build itself and then make one of the items selected based on another variable.

    For example:

    Table 1 (Users) contains:
    User
    Email
    TeamID

    Table 2 (Teams) contains:
    Team_name
    Team_ID


    Basically what I am looking for is, once the select box is created and populated from table 2 the team name is selected when the team_id matches the user’s team_id in table 1.


    Here is what I have (doesn’t work):

    PHP Code:
    <?php

    $query
    ="SELECT team_id,team_name FROM teams";
    $query1="SELECT team_id,team_name FROM teams WHERE team_id = '".$row['teamid']."'";

    /* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order */

    $result mysql_query ($query);
    $result1 mysql_query ($query1);

    $tid mysql_fetch_array($result1);

    echo 
    "<select class=\"inputbox\" name=\"team\" value='' tabindex=\"2\"></option>";
    // printing the list box select command

    while($nt=mysql_fetch_array($result)){//Array or records stored in $nt
    if($row['teamid'] == $tid['team_id']) {
    $selected 'selected="selected"';
    }
    echo 
    '<option value="'.$nt['team_id'].'" '$selected'>'.$nt['team_name'].'</option>';
    /* Option values are added by looping through the array */
    }
    echo 
    "</select>";// Closing of list box 
    ?>
    Feel free to call me an ignoramus I probably deserve it…..
    Last edited by Goku85; 03-01-2009 at 07:19 PM.

  • #2
    Regular Coder Deacon Frost's Avatar
    Join Date
    Feb 2008
    Location
    Between the Lines
    Posts
    279
    Thanks
    31
    Thanked 4 Times in 4 Posts
    Can you wrap your code in php please, so we can better read it .
    [ php] [/ php]

  • #3
    Regular Coder
    Join Date
    Mar 2009
    Location
    United Kingdom
    Posts
    161
    Thanks
    6
    Thanked 28 Times in 28 Posts
    Im guessing you want a dropdown box populated with teamnames from your SQL database?

    If so, then this is wrong in quite afew places, firstly you need a while statement, for it will not populate all team names in the drop down box.

    so here is an example

    PHP Code:
    $result mysql_query("SELECT * FROM phpbb3_users");

    $options "";

    while (
    $row=mysql_fetch_array($result)){
        
    $id=$row['user_id'];
        
    $username=$row['username'];
        
    $options.="<option value=\"$id\">".$username.'</option>';

    Ok so what is happening here is you are doing your SQL query then with the while statement is saying while there are data from database relavant to the query then it will keep doing the commands inside the {} brackets.

    So inside the brackets we are defining the variables as the variables will keep changing. Then we insert the data into the the <option> tag.

    Then we must put it into a form, so what i would do is close the the php tag by using '?>', and then simply puting the html below, for this example it would be

    Code:
    	<html>
    <body>
    
    <form method="post">
    <SELECT NAME=thing>
    <OPTION VALUE=0>Choose
    <?=$options?>
    </SELECT>
    
    </form>
    
    </body>
    </html>
    I hope this helps...

  • #4
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Killermud,
    My querey and select menu is working, it does contain a while comand that allows the loop, how ever it will not select the correct item in the list based on the results of another query. So for example:

    Team1
    Team2
    Team3

    I have a user "Bob", and he is on team2. When I pull up the user information in a admin interface, he team is set to team2. But the list also shows all the teams as well as the one that Bob is on.

    Currently my script will build the entire list, all teams but will not auto select the correct team.

    This is what I am trying to achive.

    Deacon Frost - I will remember to do so in the future and attempt to rectify sorry for the inconvenience.


    Quote Originally Posted by Killermud View Post
    Im guessing you want a dropdown box populated with teamnames from your SQL database?

    If so, then this is wrong in quite afew places, firstly you need a while statement, for it will not populate all team names in the drop down box.

    so here is an example

    PHP Code:
    $result mysql_query("SELECT * FROM phpbb3_users");

    $options "";

    while (
    $row=mysql_fetch_array($result)){
        
    $id=$row['user_id'];
        
    $username=$row['username'];
        
    $options.="<option value=\"$id\">".$username.'</option>';

    Ok so what is happening here is you are doing your SQL query then with the while statement is saying while there are data from database relavant to the query then it will keep doing the commands inside the {} brackets.

    So inside the brackets we are defining the variables as the variables will keep changing. Then we insert the data into the the <option> tag.

    Then we must put it into a form, so what i would do is close the the php tag by using '?>', and then simply puting the html below, for this example it would be

    Code:
    	<html>
    <body>
    
    <form method="post">
    <SELECT NAME=thing>
    <OPTION VALUE=0>Choose
    <?=$options?>
    </SELECT>
    
    </form>
    
    </body>
    </html>
    I hope this helps...

  • #5
    Regular Coder
    Join Date
    Mar 2009
    Location
    United Kingdom
    Posts
    161
    Thanks
    6
    Thanked 28 Times in 28 Posts
    From what i can see in your MYSQL query you have not defined the $row[''] Array.

    But i dont really understand what you wish to achieve.

  • #6
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Just wanted to thanks those who assisted me, I found and hammered out a solution. I have included it just incase anyone wanted to know.

    PHP Code:
    <?php
    $query1 
    "SELECT * FROM user_info WHERE id = '" $_GET['id'] . "'";
    $result1 mysql_query($query1
        or die(
    mysql_error());

    $row1 =
     
    mysql_fetch_array($result1);

    $teamid $row1['teamid'];

    $query="SELECT * FROM teams";

    /* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order */

    $result mysql_query ($query);

    echo 
    "<select class=\"inputbox\" name=\"team\" value='' tabindex=\"2\"></option>";
    // printing the list box select command

    while($nt=mysql_fetch_array($result)){//Array or records stored in $nt
    $selected = ( $teamid == $nt['team_id'] ) ? ' selected="selected"' ''
    $section_select .= '<option value="' $nt['team_id'] . '"' $selected '>' $nt['team_name'] . '</option>';  
    }
    echo 
    $section_select;
    echo 
    "</select>";// Closing of list box 
    ?>

    If anyone has any other solutionI would be greatful! But thanks again to those who helped!

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Join it:
    PHP Code:
    <?php

    if (@get_magic_quotes_gpc())
    {
        
    $_GET['id'] = stripslashes($_GET['id']);
    }

    $sQry "SELECT t.*, u.teamid AS userTeam
            FROM teams t
            LEFT JOIN user_info u ON (u.teamid = t.team_id)
            WHERE u.id = '" 
    mysql_real_escape_string($id) . "'";

    $obQry mysql_query($obQry
        or die(
    mysql_error());

    echo 
    "<select class=\"inputbox\" name=\"team\" value='' tabindex=\"2\"></option>";
    // printing the list box select command

    while($nt=mysql_fetch_array($obQry)){//Array or records stored in $nt
        
    $selected = ($nt['team_id'] == $nt['userTeam']) ? ' selected = "selected" : '';
        $section_select .= '
    <option value="' . $nt['team_id'] . '"' . $selected . '>' . $nt['team_name'] . '</option>;  
    }
    echo 
    $section_select;
    echo 
    "</select>";// Closing of list box 
    ?>
    I assumed the userid is a string from you're original query. If its an int, I'd drop the surrounding quotations from it (remove the magic_quotes_gpc check too), and cast it to an int.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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