Looking for specific data

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!

--
DeadTOm
Http://www.mtlaners.org
------------------------
"The object of war is not to die for your country, but to make the other bastard die for his."
-General George S Patton

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Looking for specific data

DeadTOm wrote:
> 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.

Try this?

[ requires mysql 4.0+ ]

select id, 'cs01' as column_name from table where cs01='1' union all
select id, 'cs02' as column_name from table where cs02='1' union all
select id, 'cs03' as column_name from table where cs03='1' union all
select id, 'cs04' as column_name from table where cs04='1';

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Looking for specific data

Sweet. I can work with that.
Thanks!

Chris wrote:
> DeadTOm wrote:
>> 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.
>
> Try this?
>
> [ requires mysql 4.0+ ]
>
> select id, 'cs01' as column_name from table where cs01='1' union all
> select id, 'cs02' as column_name from table where cs02='1' union all
> select id, 'cs03' as column_name from table where cs03='1' union all
> select id, 'cs04' as column_name from table where cs04='1';
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Looking for specific data

-------- Original Message --------
Subject: [PHP-DB] Looking for specific data
From: DeadTOm
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";

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php