-------- Original Message --------
Subject: Looking for specific data
From: DeadTOm <deadtom@xxxxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx
Date: 22.11.2007 3:22
There has to be an easier way to do this. I have a page with a list of
29 options utilizing checkboxes. The options that are checked get put
into a mysql table as a 1 and the options not checked get entered as a 0.
So the table looks like this
id cs01 cs02 cs03 cs04
1 0 0 1 0
2 1 0 0 0
3 0 0 0 1
The problem I'm running into is getting these things back out of the
table. I only want the columns where the answer is a 1. What I'm doing
right now looks like this:
$getPos = "SELECT * FROM pos WHERE id = '$id'";
$getPos_res = mysql_query($getPos,$conn) or die(mysql_error());
while ($getPos_info= mysql_fetch_array($getPos_res)) {
$getPos_cs01 = $getPos_info['cs01'];
$getPos_cs02 = $getPos_info['cs02'];
$getPos_cs03 = $getPos_info['cs03'];
$getPos_cs04 = $getPos_info['cs04'];
}
Then I use a series of IF statements to filter out the variables
containing a 0 so that on another page I can view only the options they
selected and leave out the ones they didn't. Obviously this takes a
crazy amount of code to accomplish this way. Is there some way, using
something like mysql_fetch_array or in my SELECT statement, to just get
the data containing a 1? Or can someone propose an entirely different
way of accomplishing the same thing?
Thanks!
depends if cs01..cs04 are related.
*1. Related (option in multiple select)*
something like "choose favorite colors: red/white/black/blue"
there are 2 ways how to handle that:
/a. m:n/
table customer (
idCustomer)
table color_options (
idColor
colorName)
table customer_favorite_color (
idCustomer
idColor)
SELECT idCustomer FROM customer,customer_favorite_color WHERE
customer_favorite_color.idCustomer=customer.idCustomer GROUP BY
customer.idCustomer;
/
b. set
/table customer (
idCustomer,
favoriteColor <= varchar, you will store idColor separated by commas
like for example '1,2,3'
)
table color_options (
idColor
colorName)
SELECT idCustomer FROM customer WHERE favoriteColor IS NOT NULL;
*2. Not related
*$testColums=array("cs01","cs02","cs03","cs04");
$where=join("!=0 or ",$testColumns)."!=0";
$query="SELECT * FROM pos WHERE $where";
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
