Checkboxes
I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? I hope I am asking this question correctly. If not, please feel free to flame me. Nick
Re: Checkboxes
Nicholas Vettese wrote: I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? If you have N = a large number of checkboxes, if the list will definitely not change, and if they are not likely to require frequent individual edits, saving them as a string of N 0|1|? values can be quite efficient for saving retrieving them. We've written test scoring software based on that. But if frequent item edits are possible, you are further ahead saving each as a named TINYINT. P. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/399 - Release Date: 7/25/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checkboxes
Looks like more of a php question that a mysql question ;) anyway, the mysql part: use either an unsigned interger (tinyint, if less than 256 possibilities, smallint if over 256 but less than 65565, etc) or use a set or enum. Using a varchar is not really the way to go. If you choose to use an integer and you need some kind of string representation of the choice, use a secondary table to hold the string values and let the integer in the main table be a foreign key to the secondary table ;) Nicholas Vettese wrote: I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? I hope I am asking this question correctly. If not, please feel free to flame me. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Checkboxes
Nick, It looks like the mysql part has been answered. I was going to suggest using a ENUM field myself. For the PHP part, if you are trying to keep multiple rows in your HTML correlated, ie $customer[1] = $mod_type[1] you may not want to use a checkbox field checkbox fields only pass a field if they are checked - so if you have information for customer[0], and the checkbox is not checked, then $mod_type[0] would NOT be from the first row, it would be from the first CHECKED row. The best solution I have found is to use a RADIO button with the value set to the default. this way it always gets a value. I have seen others suggest to using a hidden field to preset this field so there is always a value -- input type=hidden name=mod_type_0 value=other/ input type=checkbox name=mod_type_0 value=alternative /Alternativebr /) This way, if the checkbox is unchecked, it gets a value of other, but if it is checked, it will pass a value ot alternative. Note - I am not sure how this would work with an array (mod_type[]). PHP might treat the hidden field as index 0, and if the checkbox is checked, it would be index 1 - George -Original Message- From: Nicholas Vettese [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 12:04 PM To: mysql@lists.mysql.com Subject: Checkboxes I am trying to allow a user to use checkboxes for multiple selections, and I am trying to get the database to record the choices made by the user. I am using a VARCHAR(250), but I am guessing that may be wrong. Also, other than adding a [] to the end of the 'name' (e.g. input type=checkbox name=mod_type[] value=alternative /Alternativebr /), what do I need to do? I hope I am asking this question correctly. If not, please feel free to flame me. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex SQL involving 10 checkboxes
It comes down to detecting if the value you are adding to your list is a
continuation of the list or the first item.
You need to set a variable or even a counter at the start of processing then
you can use the value of that variable to determine if you are processing
the first list item or not.
like so... (I am not A PHP programmer so please forgive any syntax erros)
$First = TRUE
$SQL .= penpals.agegroup = ;
if (isset($HTTP_GET_VARS['check00'])) {
// if we are producing the first item in the list
// then we do not append the OR
if($First) {
$First = FALSE;
}else{
$SQL .= OR;
}
$SQL .= '00';
}
if (isset($HTTP_GET_VARS['check01'])) {
if($First) {
$First = FALSE;
}else{
$SQL .= OR;
}
$SQL .= '01';
}
and so on..
the best thing to do is put one block of the above code into a function and
pass in the HTTP_GET_VARS string, the SQL string, a ValueSeperator string
and First status like so...
(again please excuse sytnax, I don't know how to define a function in PHP)
FUNCTION (String HttpVarParam, String QueryValueParam, String
ValueSeperatorParam, Boolean First){
if (isset($HTTP_GET_VARS['HttpvarParam'])) { // Check if VAR exists in
HTTP VARS
if($First) {// Is it the
first one in our list?
$First = FALSE;
}else{
$SQL .= ValueSeperatorParam; // if not then we need
to append a
separator to the list
}
$SQL .= QueryValueParam;// and don't forget to
append the value
}
}
you can then call it like this
$First = TRUE
$SQL .= penpals.agegroup = ;
CALL FUNCTION ('check00','00','OR', $First) // $SQL will contain
penpals.agegroup = 00
CALL FUNCTION ('check01','01','OR', $First) // $SQL will contain
penpals.agegroup = 00 OR 01
CALL FUNCTION ('check04','04','OR', $First) // $SQL will contain
penpals.agegroup = 00 OR 01 OR 04
The beauty of this is that it is easy to change the sql value that is
associated with a n HTTP variable name
and you can change the seprator too in case you want your query to be using
an IN( , , , ) clause like this...
$First = TRUE
$SQL .= penpals.agegroup IN ( ;
CALL FUNCTION ('check00','00',',', $First) // $SQL will contain
penpals.agegroup IN ( 00
CALL FUNCTION ('check01','01',',', $First) // $SQL will contain
penpals.agegroup IN ( 00,01
CALL FUNCTION ('check04','04',',', $First) // $SQL will contain
penpals.agegroup IN ( 00,01,04
$SQL .= ); // $SQL will contain
penpals.agegroup IN ( 00,01,04 )
That should do the trick...
-Original Message-
From: Don Read [mailto:[EMAIL PROTECTED]
Sent: Friday, June 20, 2003 8:32 AM
To: vernon
Cc: [EMAIL PROTECTED]
Subject: RE: Complex SQL involving 10 checkboxes
On 19-Jun-2003 vernon wrote:
OK, I've done this.
Problem occurs when a user selects 1 and 9.
The SQL statement I have reads like so:
if (isset($HTTP_GET_VARS['check00'])) {
$age00 = '00';
$s_age00 = penpals.agegroup = $age00 AND;
}
Problem is the AND statement. Some times the user will pick checkbox 1
and 5
but not 2,3,4,6, 7, 8, and 9. And then again there will be times when a
user
only uses one check box needing the AND statement on the end, but at
other
times the user will select more than one needing an OR statement between
the
two checkboxes. Make sense?
Use an array.
$agesel=implode(', ', $HTTP_GET_VARS['ageselect,]);
$qry=SELECT * FROM foo
WHERE penpals.agegroup IN ('$agesel');
Your HTML boxes will look like:
input TYPE=CHECKBOX NAME=ageselect[] value=00
input TYPE=CHECKBOX NAME=ageselect[] value=10
input TYPE=CHECKBOX NAME=ageselect[] value=15 CHECKED
input TYPE=CHECKBOX NAME=ageselect[] value=20 CHECKED
input TYPE=CHECKBOX NAME=ageselect[] value=25
...
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex SQL involving 10 checkboxes
On 19-Jun-2003 vernon wrote:
OK, I've done this.
Problem occurs when a user selects 1 and 9.
The SQL statement I have reads like so:
if (isset($HTTP_GET_VARS['check00'])) {
$age00 = '00';
$s_age00 = penpals.agegroup = $age00 AND;
}
Problem is the AND statement. Some times the user will pick checkbox 1
and 5
but not 2,3,4,6, 7, 8, and 9. And then again there will be times when a
user
only uses one check box needing the AND statement on the end, but at
other
times the user will select more than one needing an OR statement between
the
two checkboxes. Make sense?
Use an array.
$agesel=implode(', ', $HTTP_GET_VARS['ageselect,]);
$qry=SELECT * FROM foo
WHERE penpals.agegroup IN ('$agesel');
Your HTML boxes will look like:
input TYPE=CHECKBOX NAME=ageselect[] value=00
input TYPE=CHECKBOX NAME=ageselect[] value=10
input TYPE=CHECKBOX NAME=ageselect[] value=15 CHECKED
input TYPE=CHECKBOX NAME=ageselect[] value=20 CHECKED
input TYPE=CHECKBOX NAME=ageselect[] value=25
...
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex SQL involving 10 checkboxes
OK, I've done this.
Problem occurs when a user selects 1 and 9.
The SQL statement I have reads like so:
if (isset($HTTP_GET_VARS['check00'])) {
$age00 = '00';
$s_age00 = penpals.agegroup = $age00 AND;
}
Problem is the AND statement. Some times the user will pick checkbox 1 and 5
but not 2,3,4,6, 7, 8, and 9. And then again there will be times when a user
only uses one check box needing the AND statement on the end, but at other
times the user will select more than one needing an OR statement between the
two checkboxes. Make sense?
-- Original Message ---
From: Mike Hillyer [EMAIL PROTECTED]
To: vernon [EMAIL PROTECTED],
Sent: Thu, 19 Jun 2003 12:57:20 -0600
Subject: RE: Complex SQL involving 10 checkboxes
Well, if one checkbox represents ages 1-5, another represents 6-9,
and another represents 10-15, then you have the checkboxes add where
clauses. Here is a pseudocode/basic example:
*START WITH A SELECT*
SQL = SELECT * FROM TABLE WHERE
*BUILD THE WHERE CLAUSES*
IF check1 THEN SQL = SQL age BETWEEN 1 AND 5 AND
IF check2 THEN SQL = SQL age BETWEEN 6 AND 9 AND
IF check3 THEN SQL = SQL age BETWEEN 10 AND 15 AND
*TRIM THE UNUSED AND STATEMENT
SQL = left(sql, len(SQL) - 3)
Add some GROUP and ORDER and LIMIT clauses as needed and send the
resulting string to the server.
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: vernon [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:53 PM
To: [EMAIL PROTECTED]
Subject: Complex SQL involving 10 checkboxes
I'm trying to setup a SQL statement that involes using 10 different
checkboxes. The checkboxes repersent age groups that I need
to forward to an
SQL statement. What would be the easiest way to do this?
As I think this through I'm thinking I have to check it 1 and
2 are checked
and not 3-9 and so forth which can drive a person to drink
espically when I
have other SQL statemenst I need to add to it as well.
Any ideas on the best way to do this? I have this whole thing
I was going to
post to the list but figured I start out with the basic
question first.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--- End of Original Message ---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex SQL involving 10 checkboxes
Well first of all, I am obviously having an off day for using AND at all
in my example.
Why not this?
if (isset($HTTP_GET_VARS['check00'])) {
$SQL .= penpals.agegroup = '00' OR;
}
This is probably a question for the PHP general mailing list as it is
more a code question. Essentially you just build the query in chunks. By
the nature of checkboxes you probably just need Ors, not ANDs. You may
need to rip the trailing OR, maybe replacing it with an AND if you need
to add some other condition on the end that needs an AND. Unless of
course someone can belong to more than one agegroup. If so you better
post what the table looks like.
Regards,
Mike Hillyer
-Original Message-
From: vernon [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 1:25 PM
To: [EMAIL PROTECTED]
Subject: RE: Complex SQL involving 10 checkboxes
OK, I've done this.
Problem occurs when a user selects 1 and 9.
The SQL statement I have reads like so:
if (isset($HTTP_GET_VARS['check00'])) {
$age00 = '00';
$s_age00 = penpals.agegroup = $age00 AND;
}
Problem is the AND statement. Some times the user will pick
checkbox 1 and 5
but not 2,3,4,6, 7, 8, and 9. And then again there will be
times when a user
only uses one check box needing the AND statement on the end,
but at other
times the user will select more than one needing an OR
statement between the
two checkboxes. Make sense?
-- Original Message ---
From: Mike Hillyer [EMAIL PROTECTED]
To: vernon [EMAIL PROTECTED],
Sent: Thu, 19 Jun 2003 12:57:20 -0600
Subject: RE: Complex SQL involving 10 checkboxes
Well, if one checkbox represents ages 1-5, another represents 6-9,
and another represents 10-15, then you have the checkboxes
add where
clauses. Here is a pseudocode/basic example:
*START WITH A SELECT*
SQL = SELECT * FROM TABLE WHERE
*BUILD THE WHERE CLAUSES*
IF check1 THEN SQL = SQL age BETWEEN 1 AND 5 AND
IF check2 THEN SQL = SQL age BETWEEN 6 AND 9 AND
IF check3 THEN SQL = SQL age BETWEEN 10 AND 15 AND
*TRIM THE UNUSED AND STATEMENT
SQL = left(sql, len(SQL) - 3)
Add some GROUP and ORDER and LIMIT clauses as needed and send the
resulting string to the server.
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: vernon [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 12:53 PM
To: [EMAIL PROTECTED]
Subject: Complex SQL involving 10 checkboxes
I'm trying to setup a SQL statement that involes using 10
different
checkboxes. The checkboxes repersent age groups that I need
to forward to an
SQL statement. What would be the easiest way to do this?
As I think this through I'm thinking I have to check it 1 and
2 are checked
and not 3-9 and so forth which can drive a person to drink
espically when I
have other SQL statemenst I need to add to it as well.
Any ideas on the best way to do this? I have this whole thing
I was going to
post to the list but figured I start out with the basic
question first.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--- End of Original Message ---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex SQL involving 10 checkboxes
When I have these kind of settings/testing, I make one text-field and set a specific code ex. age 0-5 [age00-05] age 6-10 [age06-10] and so on - then you reduce the SQL to always read that specific tablefield, and leave the complexity to the program instead. best regards Peter - Original Message - From: Mike Hillyer [EMAIL PROTECTED] To: vernon [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 8:57 PM Subject: RE: Complex SQL involving 10 checkboxes Well, if one checkbox represents ages 1-5, another represents 6-9, and another represents 10-15, then you have the checkboxes add where clauses. Here is a pseudocode/basic example: *START WITH A SELECT* SQL = SELECT * FROM TABLE WHERE *BUILD THE WHERE CLAUSES* IF check1 THEN SQL = SQL age BETWEEN 1 AND 5 AND IF check2 THEN SQL = SQL age BETWEEN 6 AND 9 AND IF check3 THEN SQL = SQL age BETWEEN 10 AND 15 AND *TRIM THE UNUSED AND STATEMENT SQL = left(sql, len(SQL) - 3) Add some GROUP and ORDER and LIMIT clauses as needed and send the resulting string to the server. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:53 PM To: [EMAIL PROTECTED] Subject: Complex SQL involving 10 checkboxes I'm trying to setup a SQL statement that involes using 10 different checkboxes. The checkboxes repersent age groups that I need to forward to an SQL statement. What would be the easiest way to do this? As I think this through I'm thinking I have to check it 1 and 2 are checked and not 3-9 and so forth which can drive a person to drink espically when I have other SQL statemenst I need to add to it as well. Any ideas on the best way to do this? I have this whole thing I was going to post to the list but figured I start out with the basic question first. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex SQL involving 10 checkboxes
Why not have each one look like this:
if (isset($HTTP_GET_VARS['check00'])) {
$SQL .= penpals.agegroup = '00' OR;
}
Then after you go through them all, strip the last two characters (the
trailing OR) and then attach and AND
That way it will accommodate any checks, and because you rip the last or
and replace it with an and, you are always Ok for what follows.
-Original Message-
From: vernon [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 1:49 PM
To: Mike Hillyer
Subject: RE: Complex SQL involving 10 checkboxes
if (isset($HTTP_GET_VARS['check00'])) {
$SQL .= penpals.agegroup = '00' OR;
}
Because there is a statement after these that neends to have the AND
statement.
This is probably a question for the PHP general mailing
list as it is
more a code question.
There I always get them telling me to come here, SQL issue. :(
Essentially you just build the query in
chunks. By the nature of checkboxes you probably just need Ors, not
ANDs. You may need to rip the trailing OR, maybe replacing it with
an AND if you need to add some other condition on the end
that needs
an AND. Unless of course someone can belong to more than one
agegroup. If so you better post what the table looks like.
It's not that they belong to more than one age group they may
be looking for
more than one age group, which is the case (it's a dating site). The
statement immidiately after words MUST have the AND
statement. My only
problem is with the checkboxes. Maybe they select only one in
which case it
needs and AND if they select more than one it needs an OR and
AND on the
last one. Make sense?
V
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
