Mark (or anyone who’s feeling incredibly generous): can I ask just a little more advice on schema planning, and what the code looks like to pull this off?
Here’s my issue: I have what’s basically a table of stats that each user enters, like this:
http://dl.dropbox.com/u/2180905/createstats.jpg
As you can see, each stat is tied to a "school year" tab, and also to a week. I also need to tie all of these stats to a single user. I later need to run a query on this table (I’m pretty sure this should be a single table, anyway) to compare data across users.
My question, considering our earlier discussion about joining to the user table, is what’s the best way to store this? Should a.) each user just have a single row in the DB with lots and lots of fields? Or b.) should I loop through and create a new row for each field, with a schema that looks like this:
1. id
2. userId
3. year
4. position
5. week
6. statName
7. statValue
I can’t think of any reason not to do Option B (I think Option A is going to make it very hard to compare the same stat that falls in different weeks), but I’m not sure of the best way to write the script (I’ve modified formit2DB for this). To split the field, I’m putting the position, week and statName in the field name and exploding it. Here’s what I have so far, which kind of works, but if I have more than one field, it only adds the last field in the form:
<?php
$prefix=$scriptProperties['prefix'];
$packageName = $scriptProperties['packageName'];
$tablename = $scriptProperties['tablename'];
$packagepath = $modx->getOption('core_path') . 'components/'.$packageName.'/';
$modelpath = $packagepath.'model/';
$modx->addPackage($packageName,$modelpath,$prefix);
$manager= $modx->getManager();
$generator= $manager->getGenerator();
$classname = $generator->getClassName($tablename);
//$errorMsg = '<pre>'.print_r($scriptProperties['fields'],true).'</pre>';
//$hook->addError('error_message',$errorMsg);
//$scriptProperties['hook']->formit->config['emailTo']='[email protected]';
$dataobject=$modx->getObject($classname,array('id'=>$hook->getValue('resource_id')));
if (empty($dataobject)){
$dataobject = $modx->newObject($classname);
//$dataobject->set('createdby', $modx->user->get('id'));
}
else{
//$dataobject->set('editedby', $modx->user->get('id'));
}
if (!is_object($dataobject) || !($dataobject instanceof xPDOObject)) {
$errorMsg='Failed to create object of type: ' . $classname;
$hook->addError('error_message',$errorMsg);
return false;
}
$allFormFields = $hook->getValues();
foreach ($allFormFields as $field=>$value){
if ($field !== 'spam' && $field !== 'resource_id'){
$user = $modx->getUser();
$userid = $user->get('id');
$dataobject->set('userId',$userid);
$fieldcheck = substr($field, 0, 9);
if ($fieldcheck == 'statField') {
$fieldArray = explode('_', $field);
$dataobject->set('position',$fieldArray[1]);
$dataobject->set('week',$fieldArray[2]);
$dataobject->set('statName',$fieldArray[3]);
$dataobject->set('statValue',$value);
}
else {
$dataobject->set($field,$value);
}
}
$dataobject->save();
}
return true;
Is this the best approach? And if so, can you spot what I’m doing wrong with the looping?
☆ A M B ☆
☆ A M B ☆
I guess I wasn’t thinking about the user info being in a single table, so it’s really just one additional call if I do it as a join. Derp.