Multi select list remember value

[karenn1]

Hey everyone,

 

I have a multiple select list pulling it's info from my database:

 

<select name="skill[]"size="5" multiple="multiple" class="formfields" id="skill[]">
<?php		
while ($rs_skill = mysql_fetch_array($result_skill))	{
print "<option value=\"".$rs_skill["id"]."\">".ucwords($rs_skill["skill"])."</option>";
}
?>      
</select>

 

When the page gets submitted and a user left out any other field, it returns to the page with an error. All of this works fine, however, the values selected in the multiple list doesn't stay selected. How can I adapt the coding to remember the selections?

 

 

 

 

Thanks!

 

Karen

 

 

 

 

 

[ignace]

print "<option value=\"".$rs_skill["id"]."\"".isset($_POST['skill']) && in_array($rs_skill['id'], $_POST['skill']) ? ' selected="selected"' : ''.">".ucwords($rs_skill["skill"])."</option>";

[karenn1]

Hi Ignace,

 

Thanks for your reply. I tried your code but now the list is empty. Can you help further?

 

 

 

Thanks!

 

Karen

[karenn1]

Anybody???

[ignace]

Try:

 

print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'], $_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";

[karenn1]

Ignace, you are a star! It works perfect!!

 

My next question. If I have an array of values stored into my database for a user, ie. 4, 10, 43 (the numbers being the skill ID), how can I adapt this code to pull in the value from the database and show the multiple selected skills in the list?

 

I hope this makes sense

 

 

Thanks!

 

Karen

[ignace]

Try:

 

print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'], $_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";

 

Your select has multiple="multiple" and the PHP adds a selected="selected" to any skill that is in the _POST['skill'] array. So aren't all skills selected? Or do you mean upon you retrieve it from the database? You can pre-fill $_POST['skill']:

 

if (empty($_POST)) {//nothing was yet submitted
    while ($row = mysql_fetch_assoc($result)) {
        $_POST['skill'][] = $row['skill_id'];
    }
} else {
    //form was submitted
}

//form is underneath this code

[karenn1]

Hi ignace,

 

Yes, I want to retrieve the array from the database and have those items selected in the multi list. I've used your coding and it looks like this now:

 

$sql_skillarray = "SELECT * FROM skill_sellers WHERE id = '".$_REQUEST['id']."'";
$result_skillarray = mysql_query($sql_skillarray);

if (empty($_POST)) {//nothing was yet submitted

	while ($row = mysql_fetch_assoc($result_skillarray)) {
        		     $_POST['skill'][] = $row['skills'];
    		}
} else {
    	//form was submitted
}


while ($rs_skill = mysql_fetch_array($result_skill))	{
  print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'], 
  $_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";
}

 

Nothing is selected. What am I doing wrong?

 

 

 

Thanks!

 

Karen

[karenn1]

I just noticed now, the above coding works when there is only one skill_id in the field, ie. 4 instead of 4, 12, 35, etc.

 

It's obviously not reading the array properly.

 

Can anyone please help?

[ignace]

while ($rs_skill = mysql_fetch_array($result_skill))   {
    echo $rs_skill['id'], ' in ', print_r($_POST['skill'], true), '<br>';
    print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'],
    $_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";
}

 

What does this give you?

[karenn1]

Hi Ignace,

 

Thanks again for all your help. The code above displays the list but nothing is selected. Herewith my complete code for the list:

 

<select name="skill[]" size="5" multiple="multiple" class="formfields" id="skill[]">
           <?php
   						
$sql_skill = "SELECT * FROM skills ORDER BY skill ASC";
$result_skill = mysql_query($sql_skill);


while ($rs_skill = mysql_fetch_array($result_skill))   {
    	echo $rs_skill['id'], ' in ', print_r($_POST['skill'], true), '<br>';
   	print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'],
    	$_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";
}

?>
</select>

 

Any ideas?

 

 

 

Karen

[ignace]

Paste your output here I'll check it with the code you provided.

[karenn1]

I hope this is what you are looking for. It's a very long list so I just copied the first few:

 

<select name="skill[]" size="5" multiple="multiple" class="formfields" id="skill[]">
                      1 in <br><option value="1">Account Handling: Account Director</option>2 in <br>
<option value="2">Account Handling: Account Executive</option>3 in <br>
<option value="3">Account Handling: Account Manager</option>4 in <br>
<option value="4">Account Handling: Administration</option>5 in <br>
<option value="5">Account Handling: Group Account / Client Services</option>6 in <br>
<option value="6">Account Handling: Team Assistant</option>7 in <br>
<option value="7">Consulting: Advertising</option>8 in <br>
<option value="8">Consulting: Branding</option>  </select>

[ignace]

Like I thought $_POST['skill'] does not contain anything

 

1 in <br>

 

Post your entire script again.

[ignace]

I'm a bit confused by your code, check if this is correct:

 

$sql_skillarray = "SELECT * FROM skill_sellers WHERE id = '".$_REQUEST['id']."'";
$result_skillarray = mysql_query($sql_skillarray);
while ($rs_skill = mysql_fetch_array($result_skillarray))   {
    print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'], $_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";
}

[karenn1]

$sql_skillarray = "SELECT * FROM skill_sellers WHERE id = '".$_REQUEST['id']."'";
$result_skillarray = mysql_query($sql_skillarray);
while ($rs_skill = mysql_fetch_array($result_skillarray))   {
    print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'], $_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";
}

 

The list is now blank again. Just a white block

 

 

[thebadbad]

Are you storing the IDs in a single field in the database? If so, you have to explode() the string of IDs; i.e.:

 

<?php
$database_value = '4, 12, 35';
$_POST['skill'] = explode(', ', $database_value);
?>

 

And then run the working code.

[ignace]

Change it back to:

 

$sql_skill = "SELECT * FROM skills ORDER BY skill ASC";
$result_skill = mysql_query($sql_skill);                                    
while ($rs_skill = mysql_fetch_array($result_skill))   {
    print "<option value=\"".$rs_skill["id"]."\"".(isset($_POST['skill']) && in_array($rs_skill['id'], $_POST['skill']) ? ' selected="selected"' : '').">".ucwords($rs_skill["skill"])."</option>";
}

 

What does the table skill_sellers then contain?

[karenn1]

skill_sellers contains a listing of all clients, ie:

 

1 John Smith john@smith.com 1,3,5

 

In this example, 1,3,5 is the id numbers of all his skills. This is what I want to match up with the multi select list. When updating John's profile, I want to see what skills he has, thus those must be selected on the multi select list. All skills are in the 'skills' table.

 

Does that makes sense?

[ignace]

Ah k I get it. But that 1,3,5 shouldn't be in your table as now you can't work with it to gather the skills the user has use it like:

 

CREATE TABLE users_skills (
    skills_sellers_id INTEGER NOT NULL,
    skills_id INTEGER NOT NULL,
    PRIMARY KEY (skills_sellers_id, skills_id)
);

INSERT INTO users_skills VALUES (1, 1), (1, 3), (1, 5);

 

I created 3 rows and they all have 1 for skills_sellers_id (your user's id) and that matches up with 1, 3 and 5 (his skills) If you now would select all skills (ids) for that user:

 

SELECT skills_id FROM users_skills WHERE skills_sellers_id = 1

 

Returns 1, 3 and 5 Now to get the name for that skill id:

 

SELECT * FROM skills LEFT JOIN users_skills ON skills.id = users_skills.skills_id WHERE skills_sellers_id = 1

 

Now to PHP:

 

$id = /*retrieve user id from session or wherever it is stored*/;
$query = "SELECT skills.skill, skills_sellers_id, skills_id  FROM skills LEFT JOIN users_skills ON skills.id = users_skills.skills_id WHERE skills_sellers_id = $id";// This will give us all skills
$result = mysql_query($query);
print '<select name="skills">';

while ($row = mysql_fetch_assoc($result)) {
    echo '<option value="', $row['skills_id'], '"', ((null != $row['skills_sellers_id']) ? ' selected="selected"' : ''), '>', $row['skill'], '</option>';
}
print '</select>';

 

I have pulled a little trick here I used LEFT JOIN which will get all rows from skills even if their is no record for in users_skills (whereby skills_sellers_id = null) which will cause to list all skills (even those the player doesn't have if you don't want that, remove LEFT + 1) and will 'highlight' (selected="selected") all skills the player has.

 

Take a look in phpMyAdmin and provide this query:

 

SELECT skills.skill, skills_sellers_id, skills_id  FROM skills LEFT JOIN users_skills ON skills.id = users_skills.skills_id WHERE skills_sellers_id = 1

 

(1): echo '<option value="', $row['skills_id'], '" selected="selected">', $row['skill'], '</option>';