Page 1 of 1

Queries on data stored as an array from a form

Posted: Sun Jul 22, 2007 4:16 am
by fullfocus
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


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[]):

Code: Select all

<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.

Code: Select all

$_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:

Code: Select all

$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:

Code: Select all

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:

Code: Select all

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.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sun Jul 22, 2007 7:09 am
by s.dot
I believe you have your table setup wrong. It seems like you should be inserting a new row for each attribute.

ID | CLIENT_ID | ATTRIBUTE

1 | 345 | Depression
2 | 345 | Physical

Something like that. You'll notice both rows have the same CLIENT_ID. That is OK. When you write the query(ies) to extract the information, you won't know how many rows there are. It's OK for a client to have 1 row in this table, or 20, depending on the symptoms.

Posted: Mon Jul 23, 2007 6:14 am
by fullfocus
Hello:

I wanted to store each attribute seperately. I was running into problems because of the form. The form contains checkboxes which allows the person to select more than one attribute. The ony way I knew how to deal with checkboxes and multiple values is to create an array (behavior[]). The result then being when storing into a table as an array in a single string.

Do you know how I can create my form so I can have checkboxes, allow multiple value selections, and then store the data with each attribute in a separate row?

Thanks.

Posted: Mon Jul 23, 2007 6:58 am
by superdezign
Run through the generated HTML array.

Code: Select all

foreach($_POST['behavior'] as $checkbox)
{
    print_r($checkbox);
}

Posted: Mon Jul 23, 2007 8:09 am
by s.dot
You will generate the values into an array, as you have said.

So, $_POST['symptoms'] (or similar) becomes an array. You will need to loop through this array.

Code: Select all

foreach($_POST['symptoms'] AS $symptom)
{
     //insert query, perhaps
}

Posted: Mon Jul 23, 2007 8:16 am
by fullfocus
Hi,

I will give it a try.

Thanks for the help.