Checkboxes/Arrays/MySQL Functions

[focus310]

Hello:

I have a table which stores three fields generated by checkboxes from a form. I need to create statistical reports based on these fields.

The code for the HTML form which creates the checkboxes (there can be multiple selections; I used an array for my checkboxes: behavior[], current[], prior[]):

<form action="app_admission_pg4.php" method="post">
<tr><td class="a"><input type="checkbox" name="behavior[]" value="Abuse"<?php echo ( isset ( $_SESSION['behavior'] ) && in_array('Abuse', $_SESSION['behavior']) ? ' checked="checked"' : '' ); ?> />Abuse&nbsp;&nbsp;</td>
<td class="a">
        <input type=checkbox name=current[] value="Physical"<?php echo ( isset ( $_SESSION['current'] ) && in_array('Physical', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Physical&nbsp;&nbsp;
        <input type=checkbox name=current[] value="Emotional"<?php echo ( isset ( $_SESSION['current'] ) && in_array('Emotional', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Emotional<br>
        <input type=checkbox name=current[] value="Sexual"<?php echo ( isset ( $_SESSION['current'] ) && in_array('Sexual', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Sexual&nbsp;&nbsp;&nbsp;&nbsp;
        <input type=checkbox name=current[] value="Recent"<?php echo ( isset ( $_SESSION['current'] ) && in_array('Recent', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Recent<br></td>
<td class="a">
        <input type=checkbox name=prior[] value="Physical"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('Physical', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Physical&nbsp;&nbsp;
        <input type=checkbox name=prior[] value="Emotional"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('Emotional', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Emotional<br>
        <input type=checkbox name=prior[] value="Sexual"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('Sexual', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Sexual&nbsp;&nbsp;&nbsp;&nbsp;
        <input type=checkbox name=prior[] value="Recent"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('Recent', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Recent<br></td>
</tr>   

<tr><td class="a"><input type="checkbox" name="behavior[]" value="Depression"<?php echo ( isset ( $_SESSION['behavior'] ) && in_array('Depression', $_SESSION['behavior']) ? ' checked="checked"' : '' ); ?> />Depression&nbsp;&nbsp;</td>
<td class="a">
        <input type=checkbox name=current[] value="depr_Mild"<?php echo ( isset ( $_SESSION['current'] ) && in_array('depr_Mild', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Mild&nbsp;&nbsp;
        <input type=checkbox name=current[] value="depr_Mod"<?php echo ( isset ( $_SESSION['current'] ) && in_array('depr_Mod', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Mod&nbsp;&nbsp;
        <input type=checkbox name=current[] value="depr_Severe"<?php echo ( isset ( $_SESSION['current'] ) && in_array('depr_Severe', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Severe<br></td>
<td class="a">
        <input type=checkbox name=prior[] value="depr_Mild"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('depr_Mild', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Mild&nbsp;&nbsp;
        <input type=checkbox name=prior[] value="depr_Mod"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('depr_Mod', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Mod&nbsp;&nbsp;
        <input type=checkbox name=prior[] value="depr_Severe"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('depr_Severe', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Severe<br></td>
</tr>

<tr><td class="a"><input type="checkbox" name="behavior[]" value="Lying"<?php echo ( isset ( $_SESSION['behavior'] ) && in_array('Lying', $_SESSION['behavior']) ? ' checked="checked"' : '' ); ?> />Lying&nbsp;&nbsp;</td>
<td class="a">
        <input type=checkbox name=current[] value="lying_Mild"<?php echo ( isset ( $_SESSION['current'] ) && in_array('lying_Mild', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Mild&nbsp;&nbsp;
        <input type=checkbox name=current[] value="lying_Mod"<?php echo ( isset ( $_SESSION['current'] ) && in_array('lying_Mod', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Mod&nbsp;&nbsp;
        <input type=checkbox name=current[] value="lying_Severe"<?php echo ( isset ( $_SESSION['current'] ) && in_array('lying_Severe', $_SESSION['current']) ? ' checked="checked"' : '' ); ?> />Severe<br></td>    

<td class="a">
        <input type=checkbox name=prior[] value="lying_Mild"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('lying_Mild', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Mild&nbsp;&nbsp;
        <input type=checkbox name=prior[] value="lying_Mod"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('lying_Mod', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Mod&nbsp;&nbsp;
        <input type=checkbox name=prior[] value="lying_Severe"<?php echo ( isset ( $_SESSION['prior'] ) && in_array('lying_Severe', $_SESSION['prior']) ? ' checked="checked"' : '' ); ?> />Severe<br></td>    

</tr> 

<input type="submit" name="btnSubmit" id="btnSubmit" value="Page 5 >>" class="btn" >
<input type="hidden" name="submitted" value="TRUE" />

</form>

To store the data in the table for all three checkboxes I used the implode function.

$_SESSION['behavior1'] = implode(' , ', $_SESSION['behavior']);
$_SESSION['current1'] = implode(' , ', $_SESSION['current']);
$_SESSION['prior1'] = implode(' , ', $_SESSION['prior']);

I inserted the values into the table with the following SQL statement:

$query7 = "INSERT INTO behavior_check (client_id, behavior, current, prior)
          VALUES ($cid, '$_SESSION[behavior1]', '$_SESSION[current1]', '$_SESSION[prior1]')";

$result7 = mysql_query ($query7) or die (mysql_error());

The result of the query produces the following table:

client_id   |   behavior                          |  current                                    |    prior
1              | Abuse, Lying, Depression  | Physical, Emotional, Mild, Mild  |  Emotional,  Severe, Severe
2              | Abuse, Depression     | Emotional, Sexual, Physical, Severe, Mild  | Physical, Severe, Severe 

So, I have the table with all the values by client_id. From this table, I need to create statistical reports to show, for example,
- total number of clients by behavior
Abuse = 2
Lying = 1
Depression = 2

• total number of current by behavior
  Abuse - Physical = 2
  Abuse - Emotional = 1

• total number of prior by behavior
  Abuse = Emotional = 1

In theory, I would need to write SQL statements like:

SELECT COUNT(behavior) from behavior;

Is it possible, when using the implode function to store data, to create statistical reports as I described? If so, how do I go about doing that? Since all the fields in the table are arrays, I don't know how to extract the data in order to query against those fields.

Can someone help me out?
Thank you in advance.

[firemankurt]

I think you would be much better off if you broke the data down into individual tables.

--------- PATIENT Table --------

| ID | NAME |

| 20 | John Doe |
| 21 | Sally James |

--------- Current Table --------

| ROW | PATIENT | BEHAV |

| 0 | 20 | Abuse |
| 1 | 20 | Lying |

--------- Behavior Table --------

| ROW | PATIENT | BEHAV |

| 0 | 20 | Abuse |
| 1 | 20 | Lying |

You could then do stuff like:

SELECT * FROM Current WHERE BEHAV= 'Lying'

SELECT COUNT(PATIENT) FROM Current WHERE BEHAV= 'Lying'

Otherwise you have to do very complex queries to extract partial field data. ( Very ugly, time consuming and error prone)

implode is great for creating queries but make sure you check user supplied data before executing it in query.

You might even go further to create a table of behavior types:

like
--------- BehaviorTypes --------

| ROW | BEHAV |

| 0 | Abuse |
| 1 | Lying |

Then storing behavior numbers from your original form:

--------- Current Table --------

| ROW | PATIENT | BEHAV |

| 0 | 20 | 0 |
| 1 | 20 | 1 |

The advantage is that you can easily difuse bad data by doing this:

// Column "ROW" left out because it auto increments
$q = "INSERT INTO Current (PATIENT,BEHAV) VALUES ";

foreach ($_POST['current'] AS $Data)
  {

  $Current[] = "( ".$PtID."," . ($Data *1) . ")";
  }

$q .= implode(",",  $Current);

Then SQL looks like :

SELECT * FROM Current, BehaviorTypes
WHERE
Current.BEHAV = BehaviorTypes.ROW
AND
BehaviorTypes.BEHAV= 'Lying';

[focus310]

Hi,

I wanted to do something what you suggested from the beginning. The problem that I ran into was how to create my form so that I can use the checkboxes which will allow the person completing the form to select multipe options.

If you take a look at my form at the beginning of my post, you will see I have a "form name=behavior[]". There are approximately 50 behaviors. Some clients may have one - others may have 10. The only way I was able to get this accomplished was to create the behavior[] array in the form. Within each "behavior" there is a current and a prior. Again, a client can have one or multiple. Therefore, I created "current[]" and "prior[]" arrays.

From this point, I was not able to figure out a way to store the data into the tables as you suggested. I don't know if I have to design the form differently or I have to create different form names for each.

Can you help me?

[firemankurt]

/*Change the value= in the form to numbers matching the id of the behavior you are trying to capture:
--------- BehaviorTypes --------

| ID | CAT | BEHAV |

| 0 | 0 | Physical |
| 1 | 0 | Emotional |
| 2 | 0 | Sexual |
| 3 | 0 | Recent |
| 4 | 1 | Physical |
| 5 | 1 | Emotional |
| 6 | 1 | Sexual |
| 7 | 1 | Recent |
| 8 | 2 | Mild |
| 9 | 2 | Mod |
|10 | 2 | Severe |

--------- Categories --------

| ROW | CAT |

| 0 | Abuse - Current|
| 1 | Abuse - Prior |
| 2 | Depression - Current|
| 2 | Depression - Prior|

--------- Stats --------

| ROW | PATIENT | CATEGORY | BEHAV |

| 0 | 20 | 1 | 4 |
| 1 | 20 | 1 | 6 |
| 2 | 20 | 2 | 8 |
| 3 | 20 | 2 | 9 |

/...*/
<?php
// Get Previous Selections from Stats 
$q = "
SELECT 
	*
FROM 
	Stats
WHERE
	PATIENT = 20
";
// do query and put results in arrays
$Result = mysql_query($query);
while (  $row= mysql_fetch_assoc($Result)){
$PrevSel[$row['CATEGORY']][] = $row['BEHAV'];
}

// Get Categories and Selections
$q = "
SELECT 
	Categories.ROW, 
	Categories.CAT, 
	BehaviorTypes.ID, 
	BehaviorTypes.BEHAV 
FROM 
	Categories,
	BehaviorTypes
WHERE
	Categories.CAT = BehaviorTypes.CAT
";
// do query and put results in arrays
$Result = mysql_query($query);
while (  $row= mysql_fetch_assoc($Result)){
$CatNames[$row['ROW']] = $row['CAT'];
$CatChoices[$row['ROW']][$row['ID']] = $row['BEHAV'];
}
?>

<form action="app_admission_pg4.php" method="post">

<?php
foreach ($CatNames as $CatKey->$CatLabel)
{ 
?>
<h2><?php echo $CatLabel; ?></h2>

<?php
foreach ($CatChoices[$CatKey] as $BehavID->$BehavLabel)
{ 
?> 
<input type="checkbox" 
	name="Behav<?php echo $CatKey."_".$BehavID;?>" 
	value="0" <?php echo ( $PrevSel[$CatKey][$BehavID]? ' checked="checked"' : '' ); ?>
/>
	<?php echo $BehavLabel;?><br/>
}
<input type="submit" name="btnSubmit" id="btnSubmit" value="Page 5 >>" class="btn" >
<input type="hidden" name="submitted" value="TRUE" />

</form>
/...
 

--------- Stats --------

| ROW | PATIENT | CATEGORY | BEHAV |

| 0 | 20 | 1 | 4 |
| 1 | 20 | 1 | 6 |
| 2 | 20 | 2 | 8 |
| 3 | 20 | 2 | 9 |

Then in "app_admission_pg4.php"

<?php
// Column "ROW" left out because it auto increments
$query7 = "INSERT INTO Stats (PATIENT,CATEGORY,BEHAV) VALUES ";

foreach ($_POST AS $Data)
  {
	if (substr($Data, 0, 5)="Behav"){
	list($CatKey,$BehavID) = explode("_",substr($Data, 4, -1));
  	$input[] = "( ".$PtID."," . ($CatKey *1)."," . ($BehavID *1) . ")";
  }

$query7 .= implode(",",  $input);


$result7 = mysql_query ($query7) or die (mysql_error());
 ?>

[focus310]

I will make an attempt to try this. I'll first read through everything a few times so I understand what is happening.