Queries on data stored as an array from a form

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Queries on data stored as an array from a form

Post 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]
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Run through the generated HTML array.

Code: Select all

foreach($_POST['behavior'] as $checkbox)
{
    print_r($checkbox);
}
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post by fullfocus »

Hi,

I will give it a try.

Thanks for the help.
Post Reply