Re: storing array in mysql
| From: | Richard Lynch | Date: | Tue, 31 Jul 2001 05:47:48 +0000 |
| Subject: | Re: storing array in mysql | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-60331@lists.php.net to get a copy of this message | ||
> I want to store the results of a multiple select input box in a mysql
> db.
Store each possible selection as a different record in a separate table and
then create a three-table join for which selections match which "main"
records.
You'll be tearing your hair out for the rest of time otherwise.
Here's what I mean:
Assume table "foo" is what you have right now, and you've been trying to
stuff things into "lists_actual" (that is you, isn't it?) for the names
selected. Do this instead:
[UNTESTED CODE!]
create table foo(
foo_id int(11) auto_increment not null primay key,
foo text
);
insert into foo(foo) values('Foo 1');
insert into foo(foo) values('Foo 2');
create table name(
name_id int(11) auto_increment not null primary key,
name text
);
insert into name(name) values('a');
insert into name(name) values('b');
insert into name(name) values('c');
insert into name(name) values('d');
insert into name(name) values('e');
create table foo_name(
foo_id int(11),
name_id int(11)
);
<?php
if (!isset($foo_id)){
$query = "select foo_id, foo from foo";
$foos = mysql_query($query) or die(mysql_error());
while (list($foo_id, $foo) = mysql_fetch_row($foos)){
echo "<A HREF=$PHP_SELF?foo_id=$foo_id>$foo</A><BR>\n";
}
exit;
}
if (isset($names)){
$query = "delete from foo_name where foo_id = $foo_id";
mysql_query($query) or die(mysql_error());
while (list($name_id, $name) = each($names)){
$query = "insert into foo_name (foo_id, name_id) values($foo_id,
$name_id)";
mysql_query($query) or die(mysql_error());
}
}
$query = "select foo_id, foo from foo where foo_id = $foo_id";
$foos = mysql_query($query) or die(mysql_error());
list($foo_id, $foo) = mysql_fetch_row($foos);
echo $foo, "<BR>\n";
?>
<FORM ACTION=<?php echo $PHP_SELF;?> METHOD=POST>
<INPUT TYPE=HIDDEN NAME=foo_id VALUE=<?php echo $foo_id;?>>
<SELECT NAME=names[] SIZE=5 MULTIPLE>
<?php
# It is CRITICAL that the two ORDER BY clauses match!
# So you can run through the records "in parallel"
$query = "select name_id, name from name order by name";
$allnames = mysql_query($query) or die(mysql_error());
$query = "select name.name_id from foo_name, name ";
$query .= " where foo_id = $foo_id ";
$query .= " and foo_name.name_id = name.name_id ";
$query .= " order by name ";
$selected_names = mysql_query($query) or die(mysql_error());
# We'll run through TWO result sets "in parallel"
# Advancing the "selected" names only when we have a "match":
$selected_row = 0;
while (list($name_id, $name) = mysql_fetch_row($allnames)){
if ($name_id == mysql_result($selected_names, $selected_row,
0)){
$selected = 'SELECTED';
$selected_row++;
}
else{
$selected = '';
}
echo "<OPTION VALUE=$name_id $selected>$name</OPTION>\n";
}
?>
</SELECT>
<INPUT TYPE=SUBMIT>
</FORM>
--
WARNING richard@zend.com address is an endangered species -- Use
ceo@l-i-e.com
Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm
Volunteer a little time: http://chatmusic.com/volunteer.htm