...

View Full Version : Select Box default selection based on mysql query



Goku85
03-01-2009, 04:55 PM
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

$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…..

Deacon Frost
03-01-2009, 05:43 PM
Can you wrap your code in php please, so we can better read it :).
[ php] [/ php]

Killermud
03-01-2009, 05:46 PM
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


$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


<html>
<body>

<form method="post">
<SELECT NAME=thing>
<OPTION VALUE=0>Choose
<?=$options?>
</SELECT>

</form>

</body>
</html>

I hope this helps...

Goku85
03-01-2009, 06:19 PM
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.



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


$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


<html>
<body>

<form method="post">
<SELECT NAME=thing>
<OPTION VALUE=0>Choose
<?=$options?>
</SELECT>

</form>

</body>
</html>

I hope this helps...

Killermud
03-01-2009, 06:32 PM
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.

Goku85
03-02-2009, 12:19 PM
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
$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!

Fou-Lu
03-02-2009, 01:00 PM
Join it:


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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum