Form Design to DB Help

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

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

Form Design to DB Help

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



User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

What part? The form or the storing?
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

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

Post 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.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

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]


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

Post 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
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

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]


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

Post by s.dot »

You are correct on both counts. :D
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

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

Post 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.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

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

Post 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.
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post by fullfocus »

I'm going to opt for the neater more organized tables.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

fullfocus wrote:I'm going to opt for the neater more organized tables.
:D Good man. :-p

Any more questions?
fullfocus
Forum Commoner
Posts: 33
Joined: Fri Jun 30, 2006 2:51 pm

Post by fullfocus »

No more questions. I'm going to proceed in putting together a table with a large number of columns....
Post Reply