Page 1 of 2
Best way to insert multiple values with checkboxes into db
Posted: Mon Sep 14, 2009 4:05 pm
by getseen
Hi guys,
I think this is the best place for this.
Im designing a property portfolio website. People are going to be choosing more than one type of property to view during a search. For example Ive got checkboxes with Chalet, Terrace, Detached etc.
What is the best way to deal with this when inputting it into the database. Should I insert it into the database and then run an if statement that says if $chalet == 'chalet' and if it doesnt don't show it? Should I do it numerically? Does anyone else have any better ideas?
Many thanks for any help I get.
Re: Best way to insert multiple values with checkboxes into db
Posted: Mon Sep 14, 2009 5:43 pm
by califdon
I'm afraid you haven't given us much information to go on. The very first thing we would need to know is your database schema. If that's what you are trying to design, you need to say so. It has nothing to do with checkboxes or any other display features, it has to do with defining the entities that you need to represent in your database. Then you need to explain what you want your web site to do. I can guess that you want to allow a visitor to specify a number of parameters and then deliver a list of properties that meet those parameters, probably allowing the visitor to select individual properties and view additional details. But you force me to guess that, which isn't very helpful. If you will describe those things, perhaps we can assist you.
Re: Best way to insert multiple values with checkboxes into db
Posted: Mon Sep 14, 2009 7:02 pm
by Christopher
You could store the options as:
1. strings
2. IDs (with a look-up table for the strings)
You could store data as:
1. one record per option
2. all in a field as delimited data
3. all in a field as serialized data
Re: Best way to insert multiple values with checkboxes into db
Posted: Tue Sep 15, 2009 4:17 am
by Ollie Saunders
Have each of the property types as fields in a property table:
Code: Select all
table Property { bool isChalet, bool isTerrace, bool isDetached }
And build a query based on the checkboxes.
Code: Select all
$q = 'SELECT * FROM Propery WHERE '
. 'isChalet = ' . (int)$_GET['chkBoxIsChalet'] . ' AND '
. 'isTerrace = ' . (int)$_GET['chkBoxIsTerrace'] . ' AND '
. 'isDetached = ' . (int)$_GET['chkBox isDetached'];
Re: Best way to insert multiple values with checkboxes into db
Posted: Tue Sep 15, 2009 4:33 am
by onion2k
Ollie Saunders wrote:Have each of the property types as fields in a property table:
Code: Select all
table Property { bool isChalet, bool isTerrace, bool isDetached }
And build a query based on the checkboxes.
Code: Select all
$q = 'SELECT * FROM Propery WHERE '
. 'isChalet = ' . (int)$_GET['chkBoxIsChalet'] . ' AND '
. 'isTerrace = ' . (int)$_GET['chkBoxIsTerrace'] . ' AND '
. 'isDetached = ' . (int)$_GET['chkBox isDetached'];
Can homes be more than one type? I wouldn't have thought so. You just need a "type" column and a query like...
[sql]SELECT * FROM `homes` WHERE `type` IN (1,2,3);[/sql]
Re: Best way to insert multiple values with checkboxes into db
Posted: Tue Sep 15, 2009 5:01 am
by CoderGoblin
Normal method is not to rely on a single table with "IsType". I would use
Table "properties"
id:
info:
....
Table property2type
id_ref:
value:
you can then join the tables
Code: Select all
SELECT * from properties WHERE property.id=property_type.id AND property.value=1;
to get the values you are after
Where:
1= Chalet
2= Terrace
3 = Detached
This means adding additional "types" require no changes to the database tables. if you want to add another one such as "farmhouse" all you need to do is add another value. These values are normally stored in another table of allowed property types. This may be used to build your checkboxes as well insuring adding additional values rely on little if any coding.
Re: Best way to insert multiple values with checkboxes into db
Posted: Tue Sep 15, 2009 7:59 am
by Ollie Saunders
Can homes be more than one type? I wouldn't have thought so. You just need a "type" column and a query like...
I didn't want to risk it.
Re: Best way to insert multiple values with checkboxes into db
Posted: Wed Oct 07, 2009 10:42 am
by getseen
Hi again,
I've left this a while due to other projects.
Thanks for the replies so far but Im just a little bit lost.
Basically I have a property website and I want buyers to select property types that they wish to view, when they log in, from a choice of check boxes. Ie Chalet, Semi Detached etc. There is a form with other information thats get submitted to a table ie email etc.
It then goes into a table called buyers. In this table is the users name, email etc. I also want to be able to store the checkboxes they ticked in this table somewhere. What is the best way to do this?
Cheers
Re: Best way to insert multiple values with checkboxes into db
Posted: Wed Oct 07, 2009 1:06 pm
by thinsoldier
Here's an example of the evolution of the ways I've handled property types over the years on my real estate projects
property_type = 'condo';
property_type = 'condo,townhouse,single-family';
property_type = '19,22,25,36,44,47';
I've found this last way works best... up to a point....
But then again, I suck at JOINS so it's very likely that using JOINS and 3 tables is better than comma delimited id numbers.
Not that's all related to how you're saving the type data in relation to the properties.
For just saving the user's checked choices I would have a single field called 'chosen_query_options' and save a comma separated string of the id #'s of the types they checked.
$userdata = mysql()->Buyers->getByID( $_SESSION[logged_in_id] );//sql query
$choices = $userdata['choices']; // comma separated id numbers of types in type table
$choices = expolde(',' , $choices); // tur it into an array
$orOrOr = '';
foreach($choices as $typeid)
{ $orOrOr .="type_id='$typeid' OR "; }
$sql = "select property_id from properties2types where ( $orOrOr )";
This query will give you the id number of every property that matches any of the buyers selected types.
Then u use those propert ids in another query or you could have done everything at once with some sort of join query.
Re: Best way to insert multiple values with checkboxes into db
Posted: Wed Oct 07, 2009 2:08 pm
by getseen
Thank you, I will have a go at that.
Re: Best way to insert multiple values with checkboxes into db
Posted: Wed Oct 07, 2009 2:51 pm
by califdon
I'm afraid that's bad advice. The first rule of normalization requires that every column represents a SINGLE-VALUED data element, so comma-delimited strings is one of the most fundamental mistakes you can make. The first thing to determine is whether any single property record can be described by more than one "type". Can a "chalet" also be "terraced"? If the answer is No, it's quite simple: just have a column for "type". If the number of "types" is small (say, under ten or so), I would use an enumerated data type ("enum"), which will only accept values defined in the table; this avoids misspellings, which then affect searches.
If a property might have more than one "type", you are describing a many-to-many relationship (one property may have "many" types, a "type" may be associated with "many" properties) and that always requires 3 tables. In this case, your property table, a table of possible "types", and the third intermediate table that holds the linking records; for every property-type relationship, there will be one record in the 3rd table. This is standard relational database design and trying to do it any other way is guaranteed to be a problem in the future operation of your database. Read a basic tutorial on relational database design, such as
http://www.ehow.com/way_5465335_relatio ... orial.html or
http://parallel.vub.ac.be/documentation/database/tutor/.
Re: Best way to insert multiple values with checkboxes into db
Posted: Wed Oct 07, 2009 7:10 pm
by getseen
Hi, that was some very informative "literature" you gave me there, thanks.
What I have now, is a table for buyers, which holds the buyers unique client id, their email, their name etc. I then have buyers_choice which has the clientid field and then all the property types.
When the buyer registers on the homepage, they are assigned a unique ID. This is stored both in buyers and buyers_choice. Later when they fill in their property choices, it updates the buyers_choice fields that he/she has selected.
Does that sound ok?
Re: Best way to insert multiple values with checkboxes into db
Posted: Wed Oct 07, 2009 7:34 pm
by califdon
getseen wrote:Hi, that was some very informative "literature" you gave me there, thanks.
What I have now, is a table for buyers, which holds the buyers unique client id, their email, their name etc. I then have buyers_choice which has the clientid field and then all the property types.
When the buyer registers on the homepage, they are assigned a unique ID. This is stored both in buyers and buyers_choice. Later when they fill in their property choices, it updates the buyers_choice fields that he/she has selected.
Does that sound ok?
No. Relational database design is a nonintuitive process and those who attempt it before they have studied the fundamentals usually encounter severe problems later, sometimes requiring scrapping the entire project. This happens all the time. All I can do is urge you to read some basic tutorials before you do anything else. You can certainly ignore my advice, but that's your choice.
Relational database design starts with defining your data model. What entities will be represented? In your case, it seems that would be buyers, properties, and perhaps others. Then you must explicitly define the relationships between your entities. Each entity requires a table to hold the data for that entity, and if there are any many-to-many relationships, they require additional tables to hold the many-to-many links. Then you must identify the attributes of each entity, which become columns in the tables. There are very strict rules that govern these attributes, called the Normal Forms of a relational database. The first rule is that every column must be "atomic", that is, no "lists" or comma delimited multiple values. That's called 1NF or First Normal Form. The next rule is that there should be no "repeating groups", such as child1name, child2name, child3name. Then the rules become harder to describe, referring to keys and dependencies, but they state essentially that a column in a table should be dependent only on the Primary Key and never on non-Primary Key values in the same table. This results in the rule "don't store calculated data". You can find lots of material online if you search with the terms
database relational model or
database normal forms.
Can you make a database without learning this material? Maybe you could get some crude sort of one-table database to function for a limited purpose, but if you are ever going to have a need to expand its functionality or perform complex data retrieval from it, you better start off with a sound relational model!
Re: Best way to insert multiple values with checkboxes into db
Posted: Thu Oct 08, 2009 8:06 am
by getseen
califdon wrote:getseen wrote:Hi, that was some very informative "literature" you gave me there, thanks.
What I have now, is a table for buyers, which holds the buyers unique client id, their email, their name etc. I then have buyers_choice which has the clientid field and then all the property types.
When the buyer registers on the homepage, they are assigned a unique ID. This is stored both in buyers and buyers_choice. Later when they fill in their property choices, it updates the buyers_choice fields that he/she has selected.
Does that sound ok?
No. Relational database design is a nonintuitive process and those who attempt it before they have studied the fundamentals usually encounter severe problems later, sometimes requiring scrapping the entire project. This happens all the time. All I can do is urge you to read some basic tutorials before you do anything else. You can certainly ignore my advice, but that's your choice.
Relational database design starts with defining your data model. What entities will be represented? In your case, it seems that would be buyers, properties, and perhaps others. Then you must explicitly define the relationships between your entities. Each entity requires a table to hold the data for that entity, and if there are any many-to-many relationships, they require additional tables to hold the many-to-many links. Then you must identify the attributes of each entity, which become columns in the tables. There are very strict rules that govern these attributes, called the Normal Forms of a relational database. The first rule is that every column must be "atomic", that is, no "lists" or comma delimited multiple values. That's called 1NF or First Normal Form. The next rule is that there should be no "repeating groups", such as child1name, child2name, child3name. Then the rules become harder to describe, referring to keys and dependencies, but they state essentially that a column in a table should be dependent only on the Primary Key and never on non-Primary Key values in the same table. This results in the rule "don't store calculated data". You can find lots of material online if you search with the terms
database relational model or
database normal forms.
Can you make a database without learning this material? Maybe you could get some crude sort of one-table database to function for a limited purpose, but if you are ever going to have a need to expand its functionality or perform complex data retrieval from it, you better start off with a sound relational model!
Thanks again,
I was following this link
http://www.edm2.com/0612/msql7.html
The first one you gave me kind of stopped short and the second one overcomplicated itself.
Say Ive got my table buyers and buyers choice, clientid is the primary key for buyers and also the foreign key for buyers_choice. Surely there is a relationship between these two then?
Forgive my ignorance if im wrong.
Just thinking, you are very correct. I need a seperate "properties" table as well. Because just as the buyers are choosing which properties they wish to look at, the sellers are also defining what their property "types" are.
Im going to draw a diagram, for myself, of what information will be going where.
Re: Best way to insert multiple values with checkboxes into db
Posted: Thu Oct 08, 2009 9:49 am
by getseen
Ok, i've done this diagram.
Sellers_property has a primary key of sellers_id. It has a foreign key with property_types which is 'property_id'. Im planning on have the 'seller' input their property type when they fill in the form. It will be stored in 'sellers_property' along with other information about their house. But it will also have 'property_id' and be stored in table 'property_types' So when it comes to searching for properties I will have a list of the property types that match the search description and it can be linked with the other details about the property, that are kept in 'sellers_property' through 'property_id'.
I feel as though I am missing a link between 'buyers_choice' and 'property_types' though.
I believe I am on the right track though, am I correct?