Page 1 of 2

Updating multiple row with multiple values

Posted: Mon Dec 18, 2006 11:07 am
by Wade
Good morning! I'm a php noob and am hoping somebody can help me out...

I'm creating a php/MySQL application to manage in-house contact lists. Each contact con be associated to many communities, and each community can have upto 15 items that can be set (checkboxes/bool)

A snippet of the display/edit form is:

Code: Select all

<?php  
mysql_data_seek( $result1, 0 );
while ($row1 = mysql_fetch_assoc($result1)) { ?>
<input type="hidden" name="rowData[]" value="<?php echo $row1 ?>" />
<tr><td><input type="text" value="<?php echo $row1['CName']; ?>" name="CName[]" size="15" /></td>
<td><input type="checkbox" name="HOContact[]" <?php if($row1["HOContact"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="HOAcctContact[]" <?php if($row1["HOAcctContact"]){echo " CHECKED";} ?> /></td>
<td><input type="checkbox" name="MUAreaMgr[]" <?php if($row1["MUAreaMgr"]){echo " CHECKED";} ?> /></td>
<td><input type="checkbox" name="StAreaMgr[]" <?php if($row1["StAreaMgr"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="MFAreaMgr[]" <?php if($row1["MFAreaMgr"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="SCContact[]" <?php if($row1["SCContact"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="VMAttendee[]" <?php if($row1["VMAttendee"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="ConMgr[]" <?php if($row1["ConMgr"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="SiteSuper[]" <?php if($row1["SiteSuper"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="SiteSuperAsst[]" <?php if($row1["SiteSuperAsst"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="VIPOpenInvite[]" <?php if($row1["VIPOpenInvite"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="CCCLunchInvite[]" <?php if($row1["CCCLunchInvite"]){echo " CHECKED";} ?>  /></td></tr>
<?php } ?>
And the Save php file snippet is:

Code: Select all

$rowData = $_POST["rowData"];
$CName = $_POST["CName"];
$HOContact = $_POST["HOContact"];
$HOAcctContact = $_POST["HOAcctContact"];
$MUAreaMgr = $_POST["MUAreaMgr"];
$StAreaMgr = $_POST["StAreaMgr"];
$MFAreaMgr = $_POST["MFAreaMgr"];
$SCContact = $_POST["SCContact"];
$VMAttendee = $_POST["VMAttendee"];
$ConMgr = $_POST["ConMgr"];
$SiteSuper = $_POST["SiteSuper"];
$SiteSuperAsst = $_POST["SiteSuperAsst"];
$VIPOpenInvite = $_POST["VIPOpenInvite"];
$CCCLunchInvite = $_POST["CCCLunchInvite"];

...

$n        = count($rowData);
$i        = 0;
while ($i < $n)
{
$query2 = "UPDATE communities SET HOContact='" . $HOContact[$i] . "',HOAcctContact='" . $HOAcctContact[$i] .
 "',MUAreaMgr='" . $MUAreaMgr[$i] . "',StAreaMgr='" . $StAreaMgr[$i] . "',MFAreaMgr='" . $MFAreaMgr[$i] .  "',SCContact='" . $SCContact[$i] .
 "',VMAttendee='" . $VMAtttendee[$i] . "',ConMgr='" . $ConMgr[$i] .  "',SiteSuper='" . $SiteSuper[$i] . "',SiteSuperAsst='" . $SiteSuperAsst[$i] .
 "',VIPOpenInvite='" . $VIPOpenInvite[$i] .  "',CCCLunchInvite='" . $CCCLunchInvite[$i] . "'  WHERE CName=" . $CName[$i] . " AND ContactID=" . $id;
echo("Exisiting record SQL for Communities Table: " . $query2 . "<br><br>");
$i++;
}
So for instance if this person was associated to 4 communities but HOContact was only set for 2 of them (say 2nd and last) then the $query2 will put the 'on' for records 1 & 2 instead of where it should be.

I think the easiest thing might be to set the off flag for the check boxes not selected, but I'm having trouble figuring that out.

Or maybe there is a completely different way to do it...

Thanks for you time.

Wade

Posted: Mon Dec 18, 2006 11:24 am
by feyd
What about using a counter to create arrays for each record?

For example if you named the HOContact checkbox "record[2][HOContact]" then it will stay associated with the second record generated and be in a named element "HOContact."

Posted: Mon Dec 18, 2006 11:36 am
by Wade
feyd - Thank you for your quick response. Can you expand on that a bit? As I mentioned I'm a php noob and I don't reall get what your suggesting...

:oops:

Thanks

Posted: Mon Dec 18, 2006 1:57 pm
by feyd
Expand on what part?

Counters are done similar to this:

Code: Select all

while(/*some loop condition*/)
{
  ++$x;
}
You're already using a while(), so you just need to add an initialization and increment for a counter ($x) in the above example.

Basically what I'm suggesting will do two things: it will allow you to group your records so they become a single entity and create named elements so it has more meaning than "6."

Posted: Mon Dec 18, 2006 2:01 pm
by Wade
So something like:

Code: Select all

<?php 
$x=0;
mysql_data_seek( $result1, 0 );
while ($row1 = mysql_fetch_assoc($result1)) { ?>
...

<td><input type="checkbox" name="record[$x]HOContact[]" <?php if($row1["HOContact"]){echo " CHECKED";} ?>  /></td> 

...
$x++;

Posted: Mon Dec 18, 2006 2:03 pm
by feyd
Similar. .. although in the format I posted previously.

Posted: Mon Dec 18, 2006 2:04 pm
by Wade
the format of the increment? (I noticed I switched ++ placement)

Posted: Mon Dec 18, 2006 2:06 pm
by feyd
"record[2][HOContact]" versus "record[$x]HOContact[]"

In this context, the placement of the "++" for the increment isn't a problem and can be placed in either location with the same end effect.

Posted: Mon Dec 18, 2006 3:06 pm
by Wade
How would it deal with multiple rows though? If I use record[2][HOContact] everytime it iterates through the while lopp will it not overwrite the current contents?

Example is that this person is associated with 3 different communities, so for each iteration we'd have the community name (CName) and the items for that community HOContact, HOAcctContact, etc

So would using record[2][HOContact] save every item for HOContact? would I use the same thing for the next field? record[2][HOAcctContact], and so on?

Thanks very much feyd!

:)

Posted: Mon Dec 18, 2006 3:15 pm
by feyd
Well, you replace the "2" with $x, but everything else remains for the most part as I've already given in the example.

Posted: Mon Dec 18, 2006 3:18 pm
by Wade
[quote="feyd"]"record[2][HOContact]" versus "record[$x]HOContact[]"

Ahh sorry I though by the above you meant actually use the number 2, but I see how my brackets were misplaced.

Excellent! I'll begin changing the code right away, and keep you posted.

Thanks again.

Posted: Mon Dec 18, 2006 3:50 pm
by Wade
Ok so I've switched the form code to:

Code: Select all

<?php  
$x=0;
mysql_data_seek( $result1,0);
while ($row1 = mysql_fetch_assoc($result1)) { ?>
<tr><td><input type="text" value="<?php echo $row1['CName']; ?>" name="record[$x][CName]" size="15" /></td>
<td><input type="checkbox" name="record[$x][HOContact]" <?php if($row1["HOContact"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][HOAcctContact]" <?php if($row1["HOAcctContact"]){echo " CHECKED";} ?> /></td>
<td><input type="checkbox" name="record[$x][MUAreaMgr]" <?php if($row1["MUAreaMgr"]){echo " CHECKED";} ?> /></td>
<td><input type="checkbox" name="record[$x][StAreaMgr]" <?php if($row1["StAreaMgr"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][MFAreaMgr]" <?php if($row1["MFAreaMgr"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][SCContact]" <?php if($row1["SCContact"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][VMAttendee]" <?php if($row1["VMAttendee"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][ConMgr]" <?php if($row1["ConMgr"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][SiteSuper]" <?php if($row1["SiteSuper"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][SiteSuperAsst]" <?php if($row1["SiteSuperAsst"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][VIPOpenInvite]" <?php if($row1["VIPOpenInvite"]){echo " CHECKED";} ?>  /></td>
<td><input type="checkbox" name="record[$x][CCCLunchInvite]" <?php if($row1["CCCLunchInvite"]){echo " CHECKED";} ?>  /></td></tr>
<?php ++$x; ?>
<?php }?>
and the save.php to :

Code: Select all

...
$rowData = $_POST["record"];
...
$n        = count($rowData);
$i        = 0;
echo("rowData count is: " . $n);
while ($i < $n)
{
$query2 = "UPDATE communities SET HOContact='" . $rowData[$i]["HOContact"] . "',HOAcctContact='" . $rowData[$i]["HOAcctContact"] .
 "',MUAreaMgr='" . $rowData[$i]["MUAreaMgr"] . "',StAreaMgr='" . $rowData[$i]["StAreaMgr"] . "',MFAreaMgr='" . $rowData[$i]["MFAreaMgr"] .
 "',SCContact='" . $rowData[$i]["SCContact"] . "',VMAttendee='" . $rowData[$i]["VMAtttendee"] . "',ConMgr='" . $rowData[$i]["ConMgr"] .
 "',SiteSuper='" . $rowData[$i]["SiteSuper"] . "',SiteSuperAsst='" . $rowData[$i]["SiteSuperAsst"] .
 "',VIPOpenInvite='" . $rowData[$i]["VIPOpenInvite"] .  "',CCCLunchInvite='" . $rowData[$i]["CCCLunchInvite"] . "'  WHERE CName=" . $rowData[$i]["CName"] . " AND ContactID=" . $id;
echo("Exisiting record SQL for Communities Table: " . $query2 . "<br><br>");
$i++;
}
and I get the following results:

Code: Select all

rowData count is: 1
Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 51

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 51

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 52

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 52

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 52

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 53

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 53

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 53

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 54

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 54

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 55

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 55

Notice: Undefined offset: 0 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 55
Exisiting record SQL for Communities Table: UPDATE communities SET HOContact='',HOAcctContact='',MUAreaMgr='',StAreaMgr='',MFAreaMgr='',SCContact='',VMAttendee='',ConMgr='',SiteSuper='',SiteSuperAsst='',VIPOpenInvite='',CCCLunchInvite='' WHERE CName= AND ContactID=280


Notice: Undefined variable: result in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\savemisc.php on line 81
Any thoughts?

Posted: Mon Dec 18, 2006 4:13 pm
by feyd
$x should be written by PHP. Currently, it's straight HTML. ;)

Posted: Mon Dec 18, 2006 4:18 pm
by Wade
feyd wrote:$x should be written by PHP. Currently, it's straight HTML. ;)
How do I get it to do that?

Posted: Mon Dec 18, 2006 4:26 pm
by John Cartwright

Code: Select all

<?php echo $x; ?>
:wink: