Page 1 of 1

[SOLVED] Need some direction on how I can determine ...

Posted: Tue Feb 06, 2007 10:17 am
by Wade
Ok so the deal is that I am trying to save some data from a form, the data I'm having issues with is referred to as community data.

Now for each contact, they can be associated to none, some , many different communities. For each community they belong to they can have several attributes go along with it... Ok now for the code

I'm using a foreach to iterate through the community data that gets posted from the previous page...

Code: Select all

foreach($rowData as $v){
if($v["HOContact"]) {$v["HOContact"] = 1;} else {$v["HOContact"] = 0;}
if($v["HOAcctContact"]) {$v["HOAcctContact"] = 1;} else {$v["HOAcctContact"] = 0;}
if($v["MUAreaMgr"]) {$v["MUAreaMgr"] = 1;} else {$v["MUAreaMgr"] = 0;}
if($v["StAreaMgr"]) {$v["StAreaMgr"] = 1;} else {$v["StAreaMgr"] = 0;}
if($v["MFAreaMgr"]) {$v["MFAreaMgr"] = 1;} else {$v["MFAreaMgr"] = 0;}
if($v["SCContact"]) {$v["SCContact"] = 1;} else {$v["SCContact"] = 0;}
if($v["VMAttendee"]) {$v["VMAttendee"] = 1;} else {$v["VMAttendee"] = 0;}
if($v["ConMgr"]) {$v["ConMgr"] = 1;} else {$v["ConMgr"] = 0;}
if($v["SiteSuper"]) {$v["SiteSuper"] = 1;} else {$v["SiteSuper"] = 0;}
if($v["SiteSuperAsst"]) {$v["SiteSuperAsst"] = 1;} else {$v["SiteSuperAsst"] = 0;}
if($v["VIPOpenInvite"]) {$v["VIPOpenInvite"] = 1;} else {$v["VIPOpenInvite"] = 0;}
if($v["CCCLunchInvite"]) {$v["CCCLunchInvite"] = 1;} else {$v["CCCLunchInvite"] = 0;}
So here I'm trying to see if there is an existing record...

Code: Select all

$query99 = "SELECT * FROM communities WHERE ContactID=" . $ContactID . " AND CName='" . $v["CName"] . "'";
$result99 = mysql_query ($query99)or die ("I cannot connect to the database because: " . mysql_error());
$row99 = mysql_fetch_array($result99, MYSQL_ASSOC);
And trying some logic here...

Code: Select all

if ($row99["CName"] = $v["CName"]){
$query2 = "UPDATE communities SET HOContact='" . $v["HOContact"] . "',HOAcctContact='" . $v["HOAcctContact"] .
"',MUAreaMgr='" . $v["MUAreaMgr"] . "',StAreaMgr='" . $v["StAreaMgr"] . "',MFAreaMgr='" . $v["MFAreaMgr"] .
"',SCContact='" . $v["SCContact"] . "',VMAttendee='" . $v["VMAttendee"] . "',ConMgr='" . $v["ConMgr"] .
"',SiteSuper='" . $v["SiteSuper"] . "',SiteSuperAsst='" . $v["SiteSuperAsst"] .
"',VIPOpenInvite='" . $v["VIPOpenInvite"] . "',CCCLunchInvite='" . $v["CCCLunchInvite"] . "' WHERE CName='" . $v["CName"] . "' AND ContactID=" . $ContactID;
echo("Existing record SQL for Communities Table: " . $query2 . "<br><br>");
$result2 = mysql_query ($query2) or die ("I couldn't save the record to the database because: " . mysql_error());

} else {

$query2 = "INSERT INTO communities (CName,ContactID,HOContact,HOAcctContact,MUAreaMgr,StAreaMgr,MFAreaMgr,SCContact,VMAttendee,ConMgr,SiteSuper,SiteSuperAsst,VIPOpenInvite,CCCLunchInvite)
VALUES
(
'" . $v["CName"] . "',
'" . $ContactID . "',
'" . $v["HOContact"] . "',
'" . $v["HOAcctContact"] . "',
'" . $v["MUAreaMgr"] . "',
'" . $v["StAreaMgr"] . "',
'" . $v["MFAreaMgr"] . "',
'" . $v["SCContact"] . "',
'" . $v["VMAttendee"] . "',
'" . $v["ConMgr"] . "',
'" . $v["SiteSuper"] . "',
'" . $v["SiteSuperAsst"] . "',
'" . $v["VIPOpenInvite"] . "',
'" . $v["CCCLunchInvite"] . "'
)";
echo("New record SQL for Communities Table: " . $query2 . "<br><br>");
$result2 = mysql_query ($query2) or die ("I couldn't save the record to the database because: " . mysql_error());
}
}
Basically it's not working right, I've tried many different approaches, but either get results saying there are records (when there isn't) or creating new records when some exist already.

I apologize for the noobish coding, just getting into php so please excuse.

What I need it to do is update a record with the community data if it exists or make a new if it doesn't.

I also need to mention that the community names (CName) and user ID (ContactID) will be unique for each record. Basically for each ContactID each CName must be unique

Thanks everyone.

Wade

Posted: Tue Feb 06, 2007 10:55 am
by superdezign
Well I didn't read your entire post so excuse me if my answer doesn't help the entirety.

I haven't tried it, but in theory, it should work.

Code: Select all

mysql_query('UPDATE whatever WHERE whatever') or mysql_query('INSERT INTO whatever WHERE whatever');
Just make it try updating first and, if it fails, attempt to insert.

Posted: Tue Feb 06, 2007 11:32 am
by blackbeard
Try using REPLACE instead of UPDATE or INSERT

Posted: Tue Feb 06, 2007 12:39 pm
by Wade
Thanks for the idea Superdezign, but doesn't seem to be working.

Blackbeard - the issue with that (IMO) is that it's deleting good data, then reinserting it. the record ID numbers will get very large I would think ...


I think maybe the way to go would be an INSERT ... ON DUPLICATE KEY UPDATE statement, but fighting with syntax.

Can anyone provide some examples? I find the ones on the MySQL site kinda hard to follow.

Posted: Thu Feb 08, 2007 9:33 am
by Wade
/bump

Still hoping someone could help out with the syntax...

Thanks

Posted: Thu Feb 08, 2007 3:27 pm
by Wade
Well I ended up using the REPLACE INTO function, although I'm not convinced thats the best solution as it increments the record counter each time it does the replace. Wondering if anyone has any suggestions for dealing with it another way?

Thanks to all who have commented, I've learned some new stuff so that's always good!

Cheers,

Wade

:D

Posted: Thu Feb 08, 2007 5:40 pm
by RobertGonzalez
Maybe something like this?

Code: Select all

<?php
foreach ($rowData as $v)
{
    $HOContact = isset($v["HOContact"]);
    $HOAcctContact = isset($v["HOAcctContact"]);
    $MUAreaMgr = isset($v["MUAreaMgr"]);
    $StAreaMgr = isset($v["StAreaMgr"]);
    $MFAreaMgr = isset($v["MFAreaMgr"]);
    $SCContact = isset($v["SCContact"]);
    $VMAttendee = isset($v["VMAttendee"]);
    $ConMgr = isset($v["ConMgr"]);
    $SiteSuper = isset($v["SiteSuper"]);
    $SiteSuperAsst = isset($v["SiteSuperAsst"]);
    $VIPOpenInvite = isset($v["VIPOpenInvite"]);
    $CCCLunchInvite = isset($v["CCCLunchInvite"]);


    $query99 = "SELECT * FROM communities WHERE ContactID=" . $ContactID . " AND CName='" . $v["CName"] . "'";
    $result99 = mysql_query($query99) or die("I cannot connect to the database because: " . mysql_error());
    $row99 = mysql_fetch_array($result99);
    
    
    if ($row99["CName"] == $v["CName"])
    {
        $sql = "UPDATE communities 
                SET 
                    HOContact='$HOContact', 
                    HOAcctContact='$HOAcctContact', 
                    MUAreaMgr='$MUAreaMgr', 
                    StAreaMgr='$StAreaMgr', 
                    MFAreaMgr='$MFAreaMgr', 
                    SCContact='$SCContact', 
                    VMAttendee='$VMAttendee',  
                    ConMgr='$ConMgr', 
                    SiteSuper='$SiteSuper', 
                    SiteSuperAsst='$SiteSuperAsst', 
                    VIPOpenInvite='$VIPOpenInvite', 
                    CCCLunchInvite='$CCCLunchInvite' 
                WHERE CName='{$v['CName']}' 
                AND ContactID=$ContactID";
        echo("Existing record SQL for Communities Table: " . $sql . "<br><br>");
    } 
    else 
    {
        $sql = "INSERT INTO communities 
                SET 
                    CName = '{$v['CName']}', 
                    ContactID = $ContactID,
                    HOContact = '$HOContact',
                    HOAcctContact = '$HOAcctContact',
                    MUAreaMgr='$MUAreaMgr', 
                    StAreaMgr='$StAreaMgr', 
                    MFAreaMgr='$MFAreaMgr', 
                    SCContact='$SCContact', 
                    VMAttendee='$VMAttendee',  
                    ConMgr='$ConMgr', 
                    SiteSuper='$SiteSuper', 
                    SiteSuperAsst='$SiteSuperAsst', 
                    VIPOpenInvite='$VIPOpenInvite', 
                    CCCLunchInvite='$CCCLunchInvite'"; 
        echo("New record SQL for Communities Table: " . $query2 . "<br><br>");
    }
    
    $result = mysql_query($sql) or die("I couldn't save the record to the database because: " . mysql_error());
}

Posted: Thu Feb 08, 2007 5:46 pm
by Wade
Wow! that looks hot! I'll try it first thing am, and let you know.

Cheers and thanks so much!!

Posted: Thu Feb 08, 2007 6:09 pm
by RobertGonzalez
Keep in mind the original code looped the $rowData array and assigned a 1 or 0 based on set values of the array members. I replaced those with boolean true or false calls.

Posted: Fri Feb 09, 2007 10:23 am
by Wade
Everah wrote:Keep in mind the original code looped the $rowData array and assigned a 1 or 0 based on set values of the array members. I replaced those with boolean true or false calls.
Everah thank you so much that worked awesome!

This is what I ended up with:

Code: Select all

foreach($rowData as $v){
          if($v["HOContact"]) {$HOContact = 1;} else {$HOContact = 0;}
          if($v["HOAcctContact"]) {$HOAcctContact = 1;} else {$HOAcctContact = 0;}
          if($v["MUAreaMgr"]) {$MUAreaMgr = 1;} else {$MUAreaMgr = 0;}
          if($v["StAreaMgr"]) {$StAreaMgr = 1;} else {$StAreaMgr = 0;}
          if($v["MFAreaMgr"]) {$MFAreaMgr = 1;} else {$MFAreaMgr = 0;}
          if($v["SCContact"]) {$SCContact = 1;} else {$SCContact = 0;}
          if($v["VMAttendee"]) {$VMAttendee = 1;} else {$VMAttendee = 0;}
          if($v["ConMgr"]) {$ConMgr = 1;} else {$ConMgr = 0;}
          if($v["SiteSuper"]) {$SiteSuper = 1;} else {$SiteSuper = 0;}
          if($v["SiteSuperAsst"]) {$SiteSuperAsst = 1;} else {$SiteSuperAsst = 0;}
          if($v["VIPOpenInvite"]) {$VIPOpenInvite = 1;} else {$VIPOpenInvite = 0;}
          if($v["CCCLunchInvite"]) {$CCCLunchInvite = 1;} else {$CCCLunchInvite = 0;} 
          $query99 = "SELECT * FROM communities WHERE ContactID=" . $ContactID . " AND CName='" . $v["CName"] . "'";
          $result99 = mysql_query($query99) or die("I cannot connect to the database because: " . mysql_error());
          $row99 = mysql_fetch_array($result99);
   	  if ($row99["CName"] == $v["CName"]){
       		  $sql = "UPDATE communities
                  SET
                      HOContact='$HOContact',
                      HOAcctContact='$HOAcctContact',
                      MUAreaMgr='$MUAreaMgr',
                      StAreaMgr='$StAreaMgr',
                      MFAreaMgr='$MFAreaMgr',
                      SCContact='$SCContact',
                      VMAttendee='$VMAttendee', 
                      ConMgr='$ConMgr',
                      SiteSuper='$SiteSuper',
                      SiteSuperAsst='$SiteSuperAsst',
                      VIPOpenInvite='$VIPOpenInvite',
                      CCCLunchInvite='$CCCLunchInvite'
                  WHERE CName='{$v['CName']}'
                  AND ContactID=$ContactID";
          		//echo("Existing record SQL for Communities Table: " . $sql . "<br><br>");
    	    } else {
        	$sql = "INSERT INTO communities
                SET
                      CName = '{$v['CName']}',
                      ContactID = $ContactID,
                      HOContact = '$HOContact',
                      HOAcctContact = '$HOAcctContact',
                      MUAreaMgr='$MUAreaMgr',
                      StAreaMgr='$StAreaMgr',
                      MFAreaMgr='$MFAreaMgr',
                      SCContact='$SCContact',
                      VMAttendee='$VMAttendee', 
                      ConMgr='$ConMgr',
                      SiteSuper='$SiteSuper',
                      SiteSuperAsst='$SiteSuperAsst',
                      VIPOpenInvite='$VIPOpenInvite',
                      CCCLunchInvite='$CCCLunchInvite'";
         		//echo("New record SQL for Communities Table: " . $sql . "<br><br>");
            }
     $result = mysql_query($sql) or die("I couldn't save the record to the database because: " . mysql_error());
}
I kept the code to assign the variables 1 or 0 values.

Once again thank you for your help!

Cheers,

Wade

Posted: Fri Feb 09, 2007 10:33 am
by RobertGonzalez
You got it. Glad I could help.