SELECT help with PHP and DATA

What is the correct way to do this?

SELECT : I see this type of select everywhere.
<option value="1" <?php echo (($row['position']==1? ' selected ': '')) ?>>CEO</option>
<option value="2" <?php echo (($row['position']==1? ' selected ': '')) ?>>VP</option>
<option value="3" <?php echo (($row['position']==1? ' selected ': '')) ?>>HR</option>

SELECT : I use this at the moment
<option value="CEO" <?php echo (($row['position']==1? ' selected ': '')) ?>>CEO</option>
<option value="VP" <?php echo (($row['position']==1? ' selected ': '')) ?>>VP</option>
<option value="HR" <?php echo (($row['position']==1? ' selected ': '')) ?>>HR</option>

I do this so the data that is selected by the user is stored in the database, therefore when I retrieve the data it is correct.  Otherwise I end up retrieving 0,1,2,3,4 which means nothing.  How do I keep track that value = x means Marketing Manager?





UltraFluxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dr_dedoCommented:
I assume the following, you have a couple of tables, one contains a list of positions and the other contains some users
Table positions-+positionID
                +position
Table users ----+userId
                +username
                +positionID
 
After you saved your data to db, you need to fill back a form with stored data to edit them, and so you need to retrieve that user using his id. So, a query like this is used
Select * from users where id = xxx
Also, you should use another query to show your select list (options/select). Now say that user has position value of 2 we want to tell the dynamically generated list to add the word selected when the value is
An example
mysql_connect ($server,$user,$pass);
mysql_select_db($dbname);
$r = mysql_query ("select * from users where userId = xxx");
$row = mysql_fetch_assoc($r);
$user_position  = $row[positionID];
Now, the position of that user is $row[positionID]
 
Now, a second query to list all positions and draw the select drop list, during its creation, we will check, when current users position match an option in the select list is will write the word selected next to it
 
$rPosition = mysql_query ("select positionID, position from positions"); 
 
// as this will result more than one row, a while loop is used
$s = ;
echo <option name =positions>;
while {$rP = mysql_fetch_assoc($rPosition)){
if ($rP[positionID]== $user_position ) $s= selected ; 
echo <option value=.$rP[positionID]. .$s.>.$rP[position].</option>;
}
echo </select>;
 
here, the script will draw different elements of the select list, and when the current element equals the position of the selected user, $s will have a value of selected and the current element will be selected

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UltraFluxAuthor Commented:
I think I understand how I messed things up.

Table Position ( I just used this to generate the drop down box )
+positionID
+position

Table User
+position ( I stored the actual value in the user table ) ex: Mechanic not positionID

I thought this was the best way when I started because it allows me to retrieve all of my data directly from my User table.  SELECT * FROM User....

Is this considered wrong and should I change this?

0
UltraFluxAuthor Commented:
So I should use your method in all of my code then.

Country ( yours )
<option value="1">Canada</option>
<option value="2">Mexico</option>
<option value="3">Japan</option>

Country ( mine )
<option value="Canada">Canada</option>
<option value="Mexico">Mexico</option>
<option value="Japan">Japan</option>

Then update User to reflect countryID
Modify my current mysql queries and selects
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

UltraFluxAuthor Commented:
Is there somewhere on the internet I can get a list of countries and possibly even cities which I can use in my database?  I know there are over 200+ countries and based on our talks I will have to rebuild my tables.
0
UltraFluxAuthor Commented:
:/

Hielo, thanks for the Wiki and Yahoo links, I'm far beyond finding the information on google.  I'm sure I could manually enter the Countries if I had to however I don't think I have the time to manually enter the cities.

I will post this as another question

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.