Page 1 of 2

Form Design to DB Help

Posted: Wed May 30, 2007 9:05 pm
by fullfocus
Hello:

I'm seeking some help. I have a form which is a pyshological checklist of current and past behaviors. The social worker will interview the client and check off the behavior, how the behavior appears currently, and what it was like in the past.

For example, there is a behavior called Abuse. The person is currently abused both physically and emotionally. In the past, the person was abused only sexually.

The social worker wants the form to appear like this, I placed check marks to represent the selections:

Code: Select all

Behavior          Current                Prior 
[x]Abuse         [x]Physical           []Physical 
                       [x]Emotional        []Emotional 
                       []Sexual               [x]Sexual 
                       []Other                 []Other 

[x]Aggression   []Physical             []Physical 
                       [x]Verbal               []Verbal 
                       [x]Object               []Object 
                       []Passive             []Passive
I am having lots of trouble coding this form so I can properly store this data in a table. Can someone help me out?
Thank you in advance.




Posted: Wed May 30, 2007 9:29 pm
by superdezign
What part? The form or the storing?

Posted: Wed May 30, 2007 9:37 pm
by fullfocus
Hi,

I am having trouble doing the insert of the data. Though, I strongly feel that the way I have the form programmed is causing my problem of inserting the data.

Posted: Wed May 30, 2007 9:51 pm
by superdezign
It shouldn't prevent you from inserting it, though it may make the insertion difficult. The best set up for your checkboxes is:

Code: Select all

<input type="checkbox" name="check[]" /> Checkbox #1
<input type="checkbox" name="check[]" /> Checkbox #2
<input type="checkbox" name="check[]" /> Checkbox #3
This will automatically have them posted together in an array named check. check[0] is checkbox #1, check[1] is checkbox #2, etc.

Then, you loop through them all and insert them into your database.
Be sure to set it up in a way that accommodates the different checkboxes. Either serialize the array for future use in PHP, turn them into a comma-delimited string, or have different columns for each box.

Posted: Wed May 30, 2007 10:07 pm
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]


Hi,

This is how I have my form coded thus far.  I believe it follows what you are suggesting.  This is only a snippet of the form.  The form has close to 50 behaviors and the number of choices for each behavior varies.

[syntax="html"]
<table>
<tr><td valign="top">
<input type="checkbox" name="behavior[]" id="Abuse" value="Abuse">Abuse</td>

<td align="top"><input type="checkbox" name="current[]" id="Physical" value="Physical">Physical<br>
<input type="checkbox" name="current[]" id="Emotional" value="Emotional">Emotional<br>
<input type="checkbox" name="current[]" id="Sexual" value="Sexual">Sexual<br>
<input type="checkbox" name="current[]" id="Other" value="Other">Other<br><br></td>

<td align="top"><input type="checkbox" name="prior[]" id="Physical" value="Physical">Physical<br>
<input type="checkbox" name="prior[]" id="Emotional" value="Emotional">Emotional<br>
<input type="checkbox" name="prior[]" id="Sexual" value="Sexual">Sexual<br>
<input type="checkbox" name="prior[]" id="Other" value="Other">Other<br><br></td>
</tr>

<tr><td><input type="checkbox" name="behavior[]" id="Aggression" value="Agression">Aggression</td>
<td align="top"><input type="checkbox" name="current[]" id="Physical" value="Physical">Physical<br>
<input type="checkbox" name="current[]" id="Verbal" value="Verbal">Verbal<br>
<input type="checkbox" name="current[]" id="Object" value="Object">Object<br>
<input type="checkbox" name="current[]" id="Passive" value="Passive">Passive<br><br></td>

<td align="top"><input type="checkbox" name="prior[]" id="Physical" value="Physical">Physical<br>
<input type="checkbox" name="prior[]" id="Verbal" value="Verbal">Verbal<br>
<input type="checkbox" name="prior[]" id="Object" value="Object">Object<br>
<input type="checkbox" name="prior[]" id="Passive" value="Passive">Passive<br><br></td>

</tr>

</table>
Yes, the looping is the problem that I am having. I'm not sure how to properly do the loop. I have three arrays: behavior[], current[], prior[]. If I serialize/implode the arrays, will I be able to query against these fields?
You also mentioned creating a column for each checkbox. So if I have I have a total of 100 checkboxes, named uniquely, then I'm going to have 100 columns in my table? Therefore, each client will have one record per since all the checkboxes are going across columns instead of rows.
If I go about the column route, will I be able to query against these fields in a way like, how many were physically abused. Abuse is a behavior which would be a column, physical is an attribute of behavior which will also be a column. I guess I would probably do a count on the column.[/syntax]


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: Wed May 30, 2007 10:26 pm
by s.dot
Yes, the looping is the problem that I am having. I'm not sure how to properly do the loop. I have three arrays: behavior[], current[], prior[]. If I serialize/implode the arrays, will I be able to query against these fields?
Yes, but it would require unserializing and searching the unserialized data.
You also mentioned creating a column for each checkbox. So if I have I have a total of 100 checkboxes, named uniquely, then I'm going to have 100 columns in my table? Therefore, each client will have one record per since all the checkboxes are going across columns instead of rows.
Yes. This is the best way to do it. Each new ROW that gets inserted will be a new client. Each row (client) will have 100 columns of possible attributes. You could set each column to default to 0, and set it to 1 when that attribute is checked in the form.
If I go about the column route, will I be able to query against these fields in a way like, how many were physically abused. Abuse is a behavior which would be a column, physical is an attribute of behavior which will also be a column. I guess I would probably do a count on the column.
Yes. Queries would then become very easy, such as:

Code: Select all

SELECT count(*) FROM `clients` WHERE `physically_abused` = 1

Posted: Wed May 30, 2007 10:54 pm
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]


Hi,

I, originally, thought about going the column route but I thought that was a bad way.  Why?  No clue.

I do follow what you are saying.  Now, how do check to see if a checkbox is checked?  I also need the functionality of remembering what was checked so if a person makes a mistake they don't have to answer the questions all over again.  They can change what they need to.

My original thought about setting default to 0 and if checked to 1 is like so:

Code: Select all

if ( isset ( $_POST['abuse']))  { 
     $abuse = 1;   
   } else { 
     $abuse = 0;
   }
Would that be correct? Is this what you are talking about in checking the checkboxes?

As for the remembering of the choices, I thought this was a way to go:

Code: Select all

<p><label for="abuse">Abuse:</label>
    <?php
      if(isset($_SESSION['abuse']) && $_SESSION['abuse'] == 'emotional')
       echo '<input type="checkbox" name="abuse" id="abuse" value="emotional" checked> Emotional&nbsp;';
       else
       echo '<input type="checkbox" name="abuse" id="abuse" value="emotional">Emotional&nbsp;';
     ?>
</p>
Would that be the way to go? Thank you for the help.


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: Wed May 30, 2007 10:56 pm
by s.dot
You are correct on both counts. :D

Posted: Thu May 31, 2007 4:20 am
by fullfocus
Hi,

I have a looping question.

I believe I have my form coded with proper arrays. When I print the results of the array, this is what I get:

Code: Select all

Array
(
    [behavior] => Array
        (
            [1] => Abuse
            [2] => Agression
        )

    [current] => Array
        (
            [1] => Array
                (
                    [1] => Physical
                    [2] => Emotional
                )

            [2] => Array
                (
                    [2] => Verbal
                    [4] => Passive
                )

        )

    [prior] => Array
        (
            [1] => Array
                (
                    [3] => Sexual
                    [4] => Other
                )

            [2] => Array
                (
                    [1] => Physical
                )

        )
)
The array is read as:

1,1,1 =>Abuse,Current,Physical
1,1,2 =>Abuse, Current,Emotional
1,1,3 =>Abuse,Prior,Sexual
1,1,4 =>Abuse,Prior,Other
2,2,2 =>Aggression,Current,Verbal
2,2,4 =>Aggression,Current,Passive
2,2,1 =>Aggression,Prior,Physical


Now, current and prior attributes can be a mis-match of values so I was planning on storing the results in two tables.

My "current" table should look like this:
current_id,client_id,behavior,current
1,1,Abuse,Physical
2,1,Abuse,Emotional
3,1,Aggression,Verbal
4,1,Aggression,Passive

My "prior" table should look like this:
prior_id, client_id, behavior, prior
1,1,Abuse,Sexual
2,1,Abuse,Other
3,1,Aggression,Physical

I am having difficulty properly looping through this array so I can get the data into the tables as shown above. Can you help me out? Thanks.

Posted: Thu May 31, 2007 7:59 am
by superdezign
Well, you need to understand the way that the arrays are set up based on your form. Items that are grouped together are put into the array together. The best way to see the structure is to check every checkbox, and THEN output the posted data.

Because you have so many different arrays, you'll have to, pretty much, insert them into the database manually.

i.e.

Code: Select all

$abuse = isset($_POST['behavior'][0]) ? 1 : 0;
$aggression = isset($_POST['behavior'][1]) ? 1 : 0;
...
mysql_query("UPDATE `clients` SET `Abuse`='$abuse', `Aggression`='$aggression'...");
The way your array is set up, looping is not an option. However, if you were to have every checkbox put into the same array (all of them named checkbox[] or something), THEN you could pretty much just loop through the array.

i.e.

Code: Select all

for($i = 0; $i < $amountOfOptions; $i++)
{
    $results[$i] = isset($_POST['checkbox'][$i]) ? 1 : 0;
}
...
mysql_query("UPDATE `clients` SET `Abuse`='" . $results[0] . "', `Physical`='" . $results[1] . "', ...");
If you wanted to shrink the code of the query, you'd have to name the table columns all the same name with a number in it that you could increment through...

Code: Select all

$query = "UPDATE `clients` SET ";
for($i = 0; $i < sizeof($results); $i++)
{
    $query .= "`Column" . ($i + 1) . "`='" . $results[$i];
    if(($i + 1) < sizeof($results))
        $query .= ", ";
}
...
mysql_query($query . ";");
Just make sure you always know what's what.

In the interest of knowing what's what though, I prefer the serialization method, but it only works if the only time you plan on viewing the data is through PHP. But as long as your not too lazy to write the scripts, it'd work for you.

Posted: Thu May 31, 2007 9:44 am
by fullfocus
Hi,

Something tells me that the best approach is as you suggested earlier which is to create a separate column in the table for each checkbox. Then each client will have a single row with all it's attributes going across.

Once the applicant submits the form, the administrative department will need to retrieve the information and be able to update if necessary. I also think that this approach will give me the ability to retrieve the data and re-populate the form and do updates. You think?

Posted: Thu May 31, 2007 11:04 am
by superdezign
All of the methods that we've suggested will work fine, but you have a trade-off.

Neater, more organized code, or neater, more organized tables.

Posted: Thu May 31, 2007 11:12 am
by fullfocus
I'm going to opt for the neater more organized tables.

Posted: Thu May 31, 2007 11:28 am
by superdezign
fullfocus wrote:I'm going to opt for the neater more organized tables.
:D Good man. :-p

Any more questions?

Posted: Thu May 31, 2007 11:38 am
by fullfocus
No more questions. I'm going to proceed in putting together a table with a large number of columns....