mySql - i need help with a check list for multiple accounts!

[zrweber]

I'm making a checklist. One table holds the list with IDs. There are about 224 rows, each with its own ideas.

 

Now I have another table to hold user accounts. When you create an account, it shows you a fresh new checklist that you need to start checking off.

 

Could anyone please share techniques so I can have multiple accounts have their own list they need to check off? (ie, when a new person creatures a new account they should have their own list with NOTHING checked)

 

The only way I can think of doing this is making 224 fields for the user account with the IDs of the checklist table to check if I checked it or not. Surely there's an easier way? Thanks

[xyph]

Store the results in an array, where the key is your checklist idea ID, and the value is boolean.

 

Create your own simple un/serialize function to save a little space.

 

<?php

$arr = array(
0=>1,
1=>0,
2=>1,
3=>1,
4=>0,
100=>1
// etc...
);

$toDB = checklist_to_db($arr);

echo $toDB . '<br />';

$backToArr = checklist_from_db( $toDB );

print_r( $backToArr );

function checklist_to_db( $array ) {
$r = array();
foreach( $array as $key => $val )
	$r[]=$key.'>'.$val;
return implode(',',$r);
}

function checklist_from_db( $string ) {
$array = explode(',',$string);
$r = array();
foreach( $array as $val ) {
	$loc = strpos($val,'>');
	$r[substr($val,0,$loc)] = substr($val,$loc+1);
}
return $r;
}

?>

 

Store that string in a text field probably.

 

You could also just have a responses table. If you're comfortable designing streamlined code, that is.

Have 4 columns, id,uid,cid,response.

 

ID is the table's primary key

UID is the user's id for the response

CID is the idea's/checklist option's ID

response is a boolean containing the answer.

 

Each user could have up to x rows in each table, where x is the amount of rows in your checklist. This will save you space over storing them as text in the user table. It will potentially save you CPU time as well, not having to serialize the options. You will be dealing with a huge table. If you have 100 users, the table could be up to 22,400 rows in size. MySQL is designed to handle stuff like this though, and assuming your queries are clean, it shouldn't bog down. A MySQL expert might be able to clarify which is the better option.