Adding and searching for Multiple selection form data in MySQL?

egenart

Hello Coders!
I'm working on a project where I want to send multiple selected data from a form to be stored in a MySQL table. I havn't figured out how to make the PHP coding so MySQL will store it in a way that later will make it possible to retrieve the data according to search criteria WHERE I only want one of the multiple selections to be the key for what's being SELECTed from the table.
I suppose I would need to use Multiple Arrays here, but I don't get it how I construct the form to post it in the right way into MySQL.
The following pieces of HTML and PHP is what I've come up with so far, but this will only add ONE value to the table column "Categories":

<B>Konstnärskategorier:</B><BR>
<INPUT name="kategorier" type="radio" value="malare"

<? if ($kategorier == malare) {
echo "checked";
} ?>

Målare<BR>

<INPUT name="kategorier" type="radio" value="skulptor"

<? if ($kategorier == skulptor) {
echo "checked";
} ?>

Skulptör<BR>

<INPUT name="kategorier" type="radio" value="grafiker"

<? if ($kategorier == grafiker) {
echo "checked";
} ?>

Grafiker<BR>

<INPUT name="kategorier" type="radio" value="konsthantverkare"

<? if ($kategorier == konsthantverkare) {
echo "checked";
} ?>

Konsthantverkare<BR>

<INPUT name="kategorier" type="radio" value="fotograf"

<? if ($kategorier == fotograf) {
echo "checked";
} ?>

Fotograf<BR>

Say, that I'm an artist working as "painter", "sculptor" and "photographer" and I want all three values to be stored in the table. Now, if someone i searching for "sculptors", I want the PHP script to SELECT only those artists that have "sculptor" as one of its artistic fields of work.

I've looked through several books on both PHP and MySQL and I havn't found this problem being described anywhere (or rather its solution).

Does anyone have a tip for how I can can move on - or a hint on where I can read about the problem?

Lars

konsu

hello, suppose you have a table "artists" that contains a list of artists. the problem that you are trying to solve is to be able to tell what kind of artist a particular row in this table represents? and each artist in the table can be a painter as well as a sculptor, as well as a photographer all at the same time? so, you want to find out what field(s) you need to add to the table to store this information as well? is this correct?

egenart

Thanks for your questions Konsu. They make it a bit more clear what I'm looking for.
Yes, you are right about what I want to accomplish.
I have one field called "kategorier" (categories, in Swedish) where I hope to store all of the artist's alternatives - like "painter", "sculptor", "photographer" etc. I.E. an array of possible categories.
But as you put it, it looks as maybe each alternative should have its own field in the table? Do you think that would make it easier to solve the problem?

When searching for a certain kind of artists (like "sculptors") maybe it would be a problem to pick only one of the alternatives in an array, that is stored in only one field?
Lars

leatherback

Hi:

tables:

information // All the basic information of the artist
Artist_id | Artist_name

Categories: // All the different categories
Category_id | Category_label

info_to_Cat: // Links categories with artists. Multiple options possible
ic | Cat_nr | Artist_nr

Where:
Artist_id = Artist_nr
Category_id = Cat_nr
(id = for unique numbers, Nr. doesn't have to be unique)

Then you can
select * from artists
left join info_to_Cat on Artist_id = Artist_nr
left join Categories on Category_id = Cat_nr
where Category_label = "painter"

konsu

leatherback's solution is a good way to do it. another way is to add a bit field (boolean field) in to the artists table (in leatherback's example it is called "information") for each artist type:

create table artists (id int, name varchar(256), is_painter bool, is_sculptor bool, is_photographer bool)

this way you won't need the other two tables. but if you go with the leatherback's solution, you will be able to extend the list of artist types indefinitely, whereas with mine you won't be able to do it without changing the database schema.

in mysql 5.0.3 and later yo can use BIT field type instead of BOOL.

egenart

Thanks, both Leatherback and Konsu.
I havn't gone into joins yet, in my learning, so I tend to go for your recent idea, Konsu. It seems that with the boolean values I could use much of the update script in PHP that I've already built.

I could make this part of the form something like this:

<INPUT name="painter" type="checkbox" value="1"

<? if ($painter == 1) {
echo "checked";
} ?>

Painter<BR>

<INPUT name="drawer" type="checkbox" value="1"

<? if ($drawer == 1) {
echo "checked";
} ?>

Drawer<BR>

<INPUT name="sculptor" type="checkbox" value="1"

<? if ($sculptor == 1) {
echo "checked";
} ?>

Sculptor<BR>

....etc.etc

I've read a great deal about the SET COLUMN TYPE, which first seemed like a good solution, but I couldn't figure out how to search for only "sculptors" or "painters", without learning a lot more than I know (which isn't that much).

Thanks again. I'll continue this project, with a boiling brain ....

egenart

Once more, thanks, Konsu. I've solved all of my problems for now. Your idea with boolean values was the last push to find the right solution.
It all works!
Lars 🙂