SELECT help with PHP and DATA

Posted on 2008-01-29
Medium Priority
Last Modified: 2013-12-12
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?

Question by:UltraFlux
  • 4
LVL 16

Accepted Solution

dr_dedo earned 2000 total points
ID: 20773960
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
Table users ----+userId
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);
$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


Author Comment

ID: 20774162
I think I understand how I messed things up.

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

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?


Author Comment

ID: 20774195
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 20774247
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.

Author Comment

ID: 20774470

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


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question