how do i use multiple selection box in mysql update form?

mmoalem

hi there
have the following scripts for updating mysql data
amend1.php:

<?php
$record = $_POST['searchdata'];
echo "Reference: $record<br><BR>";


//Connecting to MYSQL
MySQL_connect("localhost","USER","PASS");

//Select the database we want to use
mysql_select_db("TABLE") or die("Could not find database");

$result=mysql_query(" SELECT * FROM ppl_tracks WHERE Track='$record'");
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {

// collect all details for our one reference
$Description=mysql_result($result,$i,"Description");
$Artist=mysql_result($result,$i,"Artist");
$reference=mysql_result($result,$i,"Track");
$FileName=mysql_result($result,$i,"FileName");
$Keywords=mysql_result($result,$i,"Keywords");
$Mood=mysql_result($result,$i,"Mood");
$Composed=mysql_result($result,$i,"Composed");
$ID=mysql_result($result,$i,"RECNO");


?>

<TABLE WIDTH="100%" CELLPADDING="10" CELLSPACING="0" BORDER="2"> <TR ALIGN="center" VALIGN="top"> 
<TD ALIGN="center" COLSPAN="1" ROWSPAN="1" BGCOLOR="#F2F2F2">

<FORM ACTION="amend2.php" METHOD="post"> 
<P ALIGN="LEFT">
<INPUT TYPE="hidden" NAME="ud_id" VALUE="<? echo "$ID" ?>"> 
<BR>Description1:<BR><TEXTAREA NAME="ud_Description" COLS="50" ROWS="4">
<? echo "$Description"?></TEXTAREA></P><P ALIGN="LEFT">Artist:<BR>
<TEXTAREA NAME="ud_Artist" COLS="50" ROWS="4"><? echo "$Artist"?></TEXTAREA></P><HR><B> 
</B><P ALIGN="LEFT">Thumbnail:<BR> <INPUT TYPE="text" NAME="ud_FileName" VALUE="<? echo "$FileName"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image 
1 :<BR> <INPUT TYPE="text" NAME="ud_Keywords" VALUE="<? echo "$Keywords"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image 
2 :<BR> <INPUT TYPE="text" NAME="ud_Mood" VALUE="<? echo "$Mood"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image 
3 :<BR><INPUT TYPE="text" NAME="ud_Composed" VALUE="<? echo "$Composed"?>" SIZE="30" MAXLENGTH="50"><BR><BR> 
</P><P><INPUT TYPE="Submit" VALUE="Update the Record"> </P></FORM></TD></TR></TABLE>

<?
++$i;
}
?>

amend2.php

<?php

$ud_id=$_POST['ud_id'];
$ud_Description=$_POST['ud_Description'];
$ud_Artist=$_POST['ud_Artist'];
$ud_FileName=$_POST['ud_FileName'];
$ud_Keywords=$_POST['ud_Keywords'];
$ud_Mood=$_POST['ud_Mood'];
$ud_Composed=$_POST['ud_Composed'];

$con = mysql_connect("localhost","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("Table", $con);
echo "<BR>Record $ud_Description <-- Updated<BR><BR>";

mysql_query(" UPDATE ppl_tracks
SET Description='$ud_Description' WHERE RECNO = '$ud_id'");

echo "<BR>Record $ud_id <-- Updated<BR><BR>";
mysql_close($con);
?>

it works fine as is

now for the form i want to replace the text input area for ud_Mood with a Multiple Selection Box... The Mood field has a data type SET with predifined values - the Lutiple selection box needs to read the value of the field and highlight the values already in the field and than post update

johanafm

# values from DB for this user
$mood = array(1, 4, 8);

# all moods
$moods = array(1 => 'happy', 2 => 'ecstatic', 4 => 'confused', 8 => 'dazed', 16 => 'sad');

echo '<select multiple="multiple" name="ud_mood">'.PHP_EOL;
foreach ($moods as $k => $v) {
	echo '<option value="'.$k.'"'. (in_array($k, $mood) ? ' selected="selected">' : '>').$v.'</option>'.PHP_EOL;
}
echo '</select>'.PHP_EOL;

mmoalem

hi there and thanks for the reply - as it is it does not work for me.
in the line

$mood = array(1, 4, 8);

i need to read the values from the database rather than specify 1,4,8 but i am not sure how?
the line before

$Mood=mysql_result($result,$i,"Mood");

will return a sting i will need to convert it into an array of words with unspecified length and insert it into your first line
also when submiting 'ud_mood' returns a number and only one number regardless of how many are selected in the box - obviously it needs to post all values and idealy in a string rather than int format

johanafm

I was a little hasty, but my post was merely ment to show the principle. First off the select's name should be ud_mood[] and will make $_POST['ud_mood'] an array of the selected options.
You can easily turn a comma separated string into an array with explode.

Post data is always sent as strings - it doesn't matter if it contains text or numeric values. Also, there is no need for you to handle the actual mood texts for update purposes.

$selected_moods = array_sum($_POST['ud_mood']);  # with 1, 4 and 8 selected => 13

mmoalem;10962321 wrote:
in the line
$mood = array(1, 4, 8);
i need to read the values from the database rather than specify 1,4,8 but i am not sure how?
the line before
$Mood=mysql_result($result,$i,"Mood");
will return a sting i will need to convert it into an array of words with unspecified length and insert it into your first line

Just turn the keys and values around. I.e.

if (!empty($_POST)) {
	echo '<pre>'.print_r($_POST,1).'</pre>';
	$selected_moods = array_sum($_POST['ud_mood']);
	echo $selected_moods . '<br/>';
	# UPDATE table SET mood = $selected_moods WHERE ...
}

# values from DB for this user
$mood = 'happy,confused,dazed';
$mood = explode(',', $mood);
# all moods
$moods = array('happy' => 1, 'ecstatic' => 2, 'confused' => 4, 'dazed' => 8, 'sad' => 16);

echo '<form action="" method="post">';
echo '<select multiple="multiple" name="ud_mood[]">'.PHP_EOL;
foreach ($moods as $k => $v) {
    echo '<option value="'.$v.'"'. (in_array($v, $mood) ? ' selected="selected">' : '>').$k.'</option>'.PHP_EOL;
}
echo '</select>'.PHP_EOL; 

echo '<input type="submit" name="submit" value="submit" />';
echo '</form>';

And if you don't want to keep db design specifics in your code, you'd have to retrieve the info about the mood column from the database as well. Otherwise you should at least encapsulate the php code dealing with mood types (in a function, mood class or user class) so that you only have one single place to update if you ever add or remove moods from the table's column definition.

mmoalem

thank you very much for the help - after some modifications i got it to work i will post the complete script in case anyone strugling with similar thing but first couple of extra questions:
just for general knowledge - the numbers in the defined array 1,2,4,8 etc are they chosen for a reason? i.e. can i use 1,2,3,4,5,6 instead?
secondly if i want to store all possible Mood values in a separate table (for the sake of easily adding more values) how do i get the script to read of that table? do i need to store the above numbers too in that table ?
and thirdly - do i need to set the datatype of Mood to SET with a list of possible values or can i use VARCHAR? (will help by not needing to update it as well as the values table)

anyway this is my working script:

$result=mysql_query(" SELECT * FROM ppl_tracks WHERE Track='$record'");
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {

// collect all details for our one reference
$Description=mysql_result($result,$i,"Description");
$Artist=mysql_result($result,$i,"Artist");
$reference=mysql_result($result,$i,"Track");
$FileName=mysql_result($result,$i,"FileName");
$Keywords=mysql_result($result,$i,"Keywords");
$Mood=mysql_result($result,$i,"Mood");
$Composed=mysql_result($result,$i,"Composed");
$ID=mysql_result($result,$i,"RECNO");

# values from DB for this user

$Mood = explode(',', $Mood);
# all moods 'dreamy','calm','chilled','energetic','tense'
$Moods = array('dreamy' => 1, 'calm' => 2, 'chilled' => 4, 'energetic' => 8, 'tense' => 16);



?>

<TABLE WIDTH="100%" CELLPADDING="10" CELLSPACING="0" BORDER="2"> <TR ALIGN="center" VALIGN="top"> 
<TD ALIGN="center" COLSPAN="1" ROWSPAN="1" BGCOLOR="#F2F2F2">

<FORM ACTION="amend2.php" METHOD="post"> 
<P ALIGN="LEFT">
<INPUT TYPE="hidden" NAME="ud_id" VALUE="<? echo "$ID" ?>"> 
<BR>Description1:<BR>
<TEXTAREA NAME="ud_Description" COLS="50" ROWS="4">
<? echo "$Description"?>
</TEXTAREA></P>
<P ALIGN="LEFT">Artist:<BR>
<TEXTAREA NAME="ud_Artist" COLS="50" ROWS="4"><? echo "$Artist"?>
</TEXTAREA></P><HR>
<P ALIGN="LEFT">Thumbnail:<BR>
<INPUT TYPE="text" NAME="ud_FileName" VALUE="<? echo "$FileName"?>" SIZE="30" MAXLENGTH="50"></P>
<P ALIGN="LEFT">Image 1 :<BR>
<INPUT TYPE="text" NAME= VALUE="<? echo "$Keywords"?>" SIZE="30" MAXLENGTH="50"></P>
<P ALIGN="LEFT">Image 2 :<BR>
<?
echo '<select multiple="multiple" name="ud_Mood[]">';
foreach ($Moods as $k => $v) {
    echo '<option value="'.$v.'"'. (in_array($k, $Mood) ? ' selected="selected">' : '>').$k.'</option>';
}
echo '</select>';?></P>
<P ALIGN="LEFT">Image 3 :<BR>
<INPUT TYPE="text" NAME="ud_Composed" VALUE="<? echo "$Composed"?>" SIZE="30" MAXLENGTH="50"><BR><BR></P>
<P>
<INPUT TYPE="Submit" VALUE="Update the Record"> </P></FORM></TD></TR></TABLE>

<?
++$i;
}
?>

and amend2.php

<?php

$ud_id=$_POST['ud_id'];
$ud_Description=$_POST['ud_Description'];
$ud_Artist=$_POST['ud_Artist'];
$ud_FileName=$_POST['ud_FileName'];
$ud_Keywords=$_POST['ud_Keywords'];
$ud_Mood = array_sum($_POST['ud_Mood']);
$ud_Composed=$_POST['ud_Composed'];





$con = mysql_connect("localhost","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("memimom1_test", $con);
echo "<BR>Record $ud_Description <-- Updated<BR><BR>";

mysql_query(" UPDATE ppl_tracks
SET Description='$ud_Description', Mood='$ud_Mood' WHERE RECNO = '$ud_id'");

echo "<BR>Record $ud_id <-- Updated<BR><BR>";
mysql_close($con);
?>

johanafm

mmoalem;10962503 wrote:
just for general knowledge - the numbers in the defined array 1,2,4,8 etc are they chosen for a reason? i.e. can i use 1,2,3,4,5,6 instead?

I chose them for ease of use, since you said that the data type in the database was "set". The set datatype is a bitfield; i.e. each bit represents a distinct mood. I don't remember what the bitsize of the field is, but probably 64. I'll show a size 4 for simplicity though

Assuming, from low to high: 'dreamy','calm','chilled','energetic'
no mood (integer value 0):
0000

dreamy (int value 1):
0001

chilled (int value 4):
0100

dreamy and chilled (int value 5):
0101

mmoalem;10962503 wrote:
secondly if i want to store all possible Mood values in a separate table (for the sake of easily adding more values) how do i get the script to read of that table? do i need to store the above numbers too in that table ?

This is the normalized approach, and I recommend you take it. Also, in this case it would make more sense to use an ordinary sequence of integers: 1, 2, 3, 4... You will also need a link table to create a many-to-many, M:N, relationship between user and mood

table user:
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
... other stuff

table user_mood:
user_id INT UNSIGNED,
mood_id INT UNSIGNED,
PRIMARY KEY (user_id, mood_id)

table mood:
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
mood VARCHAR(20)

Combining moods with users
SELECT u.id AS u_id, u.name, m.id AS m_id, m.mood
FROM user AS u
LEFT JOIN user_mood AS um ON u.id = um.user_id
LEFT JOIN mood AS m ON um.mood_id = m.id
-- if you just want one or some users, e.g those named "roger something"...
WHERE u.name like 'roger%'

mmoalem;10962503 wrote:
and thirdly - do i need to set the datatype of Mood to SET with a list of possible values or can i use VARCHAR? (will help by not needing to update it as well as the values table)

Definitely don't use that approach. The set datatype has some advantages in how you can manipulate it over a comma separated VARCHAR. Use normalized approach as shown above, or use the SET datatype. Using VARCHAR is inefficient and will lead you into lots of trouble. Usually the SET datatype will also be less efficient than the table join approach, but is still better than VARCHAR.

And, as you poiinted out yourself, if you use a separate table for moods, you can easily add more moods later on without having to do extra work to make use of the new moods in your php code.

mmoalem

again, thank you very much for the continuous patience and advice
at the moment i am reading and am trying to get my head around the many to many format.
but just a quick question - will it be simpler if instead of linking the tables - the moods table will just hold possible mood values and the above form will display this values in the selection box and on update will insert them into the mood field... i.e. the main table stays self contained and the mood table is for supplying all possible values to the update form...at least that waht i thought was the main option

johanafm

No. If you do that, your only option is to use a comma separated varchar (since it can take any values), but like I said before, you really do not want to get yourself into that.
If you try doing this with the SET datatype, you'd still need the mood values in both tables, and that's worse than having just the SET datatype.

If you want to use relational databases, you really need to learn how to perform joins, and it isn't very complicated to get the gist of how it works.
MySQL documentation for JOIN syntax.
Wikipedia article on joins.

Feel free to ask if anything is unclear or you have further questions. Although pure join quqestions would be better suited in the Database thread on this forum.

In this particular case, your user table keeps informtion about users, the mood table contains information about moods, and the user_mood table keeps track of what (0, 1, multiple) moods a particular user has selected.