Problem getting checkbox array from form to MySQL with php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thief425
Forum Newbie
Posts: 2
Joined: Wed Apr 21, 2010 12:13 pm

Problem getting checkbox array from form to MySQL with php

Post by thief425 »

To be frank and honest up front, I've always been scared of php. I haven't done html in a couple of years, but I'm trying to put together a rather simple form to collect information from a form and post it to a database for online storage. The field I work in employs a huge referral network, and I want to create an online database that allows agencies to list themselves for storage and search later. I've tried to piece together the following pages for data entry and pushing to the database, but when I run it, it updates every row of the database with the information from the array.

I'm sure that I'm doing something totally wrong, so don't bash me for that. I don't expect anyone to write a ton of code for me, just help me find the breakdown that is causing my db to change the entire column when I submit a new form. I suspect that my problem lies in the relationships between the following parts of the code, but my inexperience doesn't help me figure out what is actually going wrong. Thanks in advance for your help. Also, I can't get textarea on the form to submit anything, ever. It's just blank. I've tried it a number of ways, thinking maybe I was missing something minor, so take what you see there with a grain of salt.

Code: Select all

mysql_query ("INSERT INTO Agency_Contacts (category, agency_name, primary_contact_name, phone_no, email_web, street, city, state, region, client_pop, hours, services) 
VALUES ('$category', '$agency_name', '$primary_contact_name', '$phone_no', '$email_web', '$street', '$city', '$state', '$region', '$client_pop', '$hours', '$services')
"); 
if ($CATEGORY_ARRAY)
{
$CATEGORY = implode($CATEGORY_ARRAY, ",");
$result = mysql_query ("UPDATE Agency_Contacts
SET CATEGORY = '$CATEGORY'
");
if(!$result) 
{
echo "<B>UPDATE unsuccessful:</b> ", mysql_error();
exit;
}
}



DataEntry.html

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form id="Agency Submission" name="form1" method="GET" action="../../cgi-bin/submitform.php">
    Agency Category (check all that apply):
<br />
  <label>
  <input type="checkbox" name="CATEGORY_ARRAY[]" id="Administrative" value="Administrative" />
  Administrative</label>
  <label>
  <br />
  <input type="checkbox" name="CATEGORY_ARRAY[]" id="Advocacy/Social Justice" value="Advocacy/Social Justice" />
  Advocacy/Social Justice</label>
  
  <label><br />
  <input type="checkbox" name="CATEGORY_ARRAY[]" id="Community Practice" value="Community Practice" />
  Community Practice</label>
  <p>Agency Name: 
    <input type=text name="agency_name" size=75 maxlength=75>
  </p>
  <p>Primary Contact: 
    <input type=text name="primary_contact_name" size=25 maxlength=25>
  </p>
  <p>
    Contact Phone no.: 
    <input name=phone_no type=text value="(555)555-1212" size=25 maxlength=25>
    </p>
  <p>Email/Website: 
    <input name=email_web type=text value="Separate with a comma" size=25 maxlength=75>
    </p>
  <p>Street Address: 
    <input type=text name=street size=25 maxlength=50>
    </p>
  <p>City: 
    <input type=text name=city size=25 maxlength=25>
    </p>
  <p>State: 
    <input name=state type=text value="XX" size=6 maxlength=2>
    </p>
  Region of your state in which services are available (check all that apply):  
  <label>
  <br />
  <input type="checkbox" name="REGION_ARRAY[]" id="North" value="North" />
  North</label>
  <label>
  <br />
  <input type="checkbox" name="REGION_ARRAY[]" id="East" value="East" />
  East</label>  
  <label><br />
  <input type="checkbox" name="REGION_ARRAY[]" id="South" value="South" />
  South</label>
  <label><br />
  <input type="checkbox" name="REGION_ARRAY[]" id="West" value="West" />
  West</label>
  &nbsp;
  <p>Client populations served (select all that apply):  </p>
  <label>
  <input type="checkbox" name="CLIENT_POP_ARRAY[]" id="Adult-Male" value="Adult-Male" />
  Adult-Male</label>
  <label>
  <br />
  <input type="checkbox" name="CLIENT_POP_ARRAY[]" id="Adult-Female" value="Adult-Female" />
  Adult-Female</label>  
  <label>
  <br />
  <input type="checkbox" name="CLIENT_POP_ARRAY[]" id="Child/School" value="Child/School" />
  Child/School</label>
  <label><br />
  <input type="checkbox" name="CLIENT_POP_ARRAY[]" id="Domestic Violence" value="Domestic Violence" />
  Domestic Violence</label>
  
  <p>Services Available:  </p>
  <p>
    <textarea cols=75 rows=5 name="SERVICES_ARRAY" value="">List services separated by a comma</textarea>
  </p>
  <p>
<input type=submit>

</form>
<p>&nbsp; </p>
</body>
</html>
submitform.php

Code: Select all

<html>
<body>
<p><?php 
$link = mysql_connect('host', 'user', 'pass'); 
mysql_select_db(resources2010); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
echo 'Connected successfully';
mysql_query ("INSERT INTO Agency_Contacts (category, agency_name, primary_contact_name, phone_no, email_web, street, city, state, region, client_pop, hours, services) 
VALUES ('$category', '$agency_name', '$primary_contact_name', '$phone_no', '$email_web', '$street', '$city', '$state', '$region', '$client_pop', '$hours', '$services')
"); 
if ($CATEGORY_ARRAY)
{
$CATEGORY = implode($CATEGORY_ARRAY, ",");
$result = mysql_query ("UPDATE Agency_Contacts
SET CATEGORY = '$CATEGORY'
");
if(!$result) 
{
echo "<B>UPDATE unsuccessful:</b> ", mysql_error();
exit;
}
}
if ($REGION_ARRAY)
{
$REGION = implode($REGION_ARRAY, ",");
$result = mysql_query ("UPDATE Agency_Contacts
SET REGION = '$REGION'
");
if(!$result) 
{
echo "<B>UPDATE unsuccessful:</b> ", mysql_error();
exit;
}
}
if ($CLIENT_POP_ARRAY)
{
$CLIENT_POP = implode($CLIENT_POP_ARRAY, ",");
$result = mysql_query ("UPDATE Agency_Contacts
SET CLIENT_POP = '$CLIENT_POP'
");
if(!$result) 
{
echo "<B>UPDATE unsuccessful:</b> ", mysql_error();
exit;
}
}
if ($SERVICES_ARRAY)
{
$SERVICES = implode($SERVICES_ARRAY, ",");
$result = mysql_query ("UPDATE Agency_Contacts
SET SERVICE = '$SERVICES'
");
if(!$result) 
{
echo "<B>UPDATE unsuccessful:</b> ", mysql_error();
exit;
}
}

print ("Thanks for submitting your agency information.");
?>
</p>
</body>
</html>
User avatar
Sofw_Arch_Dev
Forum Commoner
Posts: 60
Joined: Tue Mar 16, 2010 4:06 pm
Location: San Francisco, California, US

Re: Problem getting checkbox array from form to MySQL with p

Post by Sofw_Arch_Dev »

Thief,

I'll get back with more in a bit, I'm in a bit of a crunch, but this line stuck out to me.

Code: Select all

$result = mysql_query ("UPDATE Agency_Contacts SET CATEGORY = '$CATEGORY'");
Since you haven't supplied a "WHERE" clause to the UPDATE, this statement will update ALL rows, not just one in particular. If you want to update only one row, supply the following

Code: Select all

WHERE agency_name = 'single quoted string'
If you had an ID column on the Agency_Contacts table, you could use the ID in the WHERE clause instead of agency_name. I"ll try to take a look at the code further in a bit.
User avatar
Architek
Forum Commoner
Posts: 44
Joined: Wed Jul 01, 2009 5:01 am
Location: Portland OR

Re: Problem getting checkbox array from form to MySQL with p

Post by Architek »

I see a couple things that I would change.

make sure that all your variables in the form and the submit script are the same case... $CATEGORY is not the same as $category...

Also on the check box name references you have name="REGION_ARRAY[]" ... I have never built names with arrays like this and PHP does not like certain special characters used in a variable name. For each Check box I would create a unique name for each.

Check box 1:

Code: Select all

<input type="checkbox" name="color1" value="Blue"></input> 
Check box 1:

Code: Select all

<input type="checkbox" name="color2" value="Red"></input>
On the update to the DB I usually create a unique ID or use an instance id that is some sort of auto increment so I know I am updating a single record as noted by Sofw_Arch_Dev below.

sorry... kind of a sloppy post but like Sofw_Arch_Dev in a bind and have to run to get the kids.
thief425
Forum Newbie
Posts: 2
Joined: Wed Apr 21, 2010 12:13 pm

Re: Problem getting checkbox array from form to MySQL with p

Post by thief425 »

Sofw_Arch_Dev wrote:Thief,

I'll get back with more in a bit, I'm in a bit of a crunch, but this line stuck out to me.

Code: Select all

$result = mysql_query ("UPDATE Agency_Contacts SET CATEGORY = '$CATEGORY'");
Since you haven't supplied a "WHERE" clause to the UPDATE, this statement will update ALL rows, not just one in particular. If you want to update only one row, supply the following

Code: Select all

WHERE agency_name = 'single quoted string'
If you had an ID column on the Agency_Contacts table, you could use the ID in the WHERE clause instead of agency_name. I"ll try to take a look at the code further in a bit.
Question: If all of the fields are collected in the same form, and these are new records, not updates, how would I know the agency name? The only thing I can think of with my limited knowledge is by using $_POST at the top to establish all of the values coming from the form at the beginning, and recalling the values later in the php script.

Again, I'm very new, and working from tutorials and online resources, so I apologize in advance if I'm not getting a simple concept. I just don't understand how 'single quoted string' ties into the record being submitted at that time. I could see this working when updated records that had unique ID's, as you can call a record up and edit it, but when working with new entries, I don't see how the code can work it. Is it possible to make it WHERE agency_name = '$agency_name' works the same? I see now where it's giving the impression of an UPDATE in the $result line, but when I try to INSERT INTO it creates a new record from scratch, instead of inserting it along with the rest of the new entry. The textboxes worked perfectly, I wish checkboxes were as easy.
User avatar
Sofw_Arch_Dev
Forum Commoner
Posts: 60
Joined: Tue Mar 16, 2010 4:06 pm
Location: San Francisco, California, US

Re: Problem getting checkbox array from form to MySQL with p

Post by Sofw_Arch_Dev »

Also on the check box name references you have name="REGION_ARRAY[]" ... I have never built names with arrays like this and PHP does not like certain special characters used in a variable name. For each Check box I would create a unique name for each.
Creating a unique name for each checkbox is a bad idea. How thief originally designed his page is perfectly standard. I found this as an example; http://www.hiddenvalley.com/recipe/advanced-search/. Load the page and then view source. Each checkbox is named as an array. It's how PHP accepts multiple values for a single field. However I second the advice to name your variables consistently, and to not confuse an upper case variable with a lower case one. PHP is case sensitive.
Question: If all of the fields are collected in the same form, and these are new records, not updates, how would I know the agency name?
Thief, you're INSERTING the agency_name in the code, first of all, so you already have the single quoted string to use in the update statement. And your code is doing an UPDATE directly after the INSERT, so I reassert that these records as you've coded them indeed are updates as well as inserts.

Code: Select all

mysql_query ("INSERT INTO Agency_Contacts (category, agency_name,....
...
$result = mysql_query ("UPDATE Agency_Contacts
A question we need to ask here is why are you doing an INSERT and an UPDATE consecutively? Why not just do an INSERT? Move the implosion(s) to before the INSERT statement and you can remove the UPDATE(s).

Code: Select all

if ($CATEGORY_ARRAY) {
    $CATEGORY = implode($CATEGORY_ARRAY, ",");
}
else {
    $CATEGORY = "";
}

mysql_query ("INSERT INTO Agency_Contacts (category, agency_name, primary_contact_name, phone_no, email_web, street, city, state, region, client_pop, hours, services)
VALUES ('$CATEGORY', '$agency_name', '$primary_contact_name', '$phone_no', '$email_web', '$street', '$city', '$state', '$region', '$client_pop', '$hours', '$services')
");

Notice that the INSERT statement is now inserting $CATEGORY instead of $category. You can do something similar with your other imploded arrays, moving them before the INSERT and using the imploded string value (or empty string) in the INSERT. Make sure that the names of the inputs in your HTML match what you're calling them when you extract them from $_POST ( or $_REQUEST, or $_GET whichever you use), and that the variable assigned to the value of the implode matches what you're using in the INSERT.

Also, your textarea is named as an array but it's a single textarea, which means it's a single string as far as the $_POST is concerned. You may want to ask yourself why you're assuming it's an array.

Back to work....
User avatar
Architek
Forum Commoner
Posts: 44
Joined: Wed Jul 01, 2009 5:01 am
Location: Portland OR

Re: Problem getting checkbox array from form to MySQL with p

Post by Architek »

Sofw_Arch_Dev - I didnt want to get off topic on this thread so I sent you a PM. Thanks for the reference!
Post Reply