Re: Looking for specific data

-------- Original Message -------- Subject: [PHP-DB] Looking for specific data From: DeadTOm <deadtom@mtlaners.org> To: php-db@lists.php.net 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";