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

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

Post Reply
Wade
Forum Commoner
Posts: 41
Joined: Mon Dec 18, 2006 10:21 am
Location: Calgary, AB, Canada

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

Post 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
Last edited by Wade on Fri Feb 09, 2007 10:33 am, edited 1 time in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
blackbeard
Forum Contributor
Posts: 123
Joined: Thu Aug 03, 2006 6:20 pm

Post by blackbeard »

Try using REPLACE instead of UPDATE or INSERT
Wade
Forum Commoner
Posts: 41
Joined: Mon Dec 18, 2006 10:21 am
Location: Calgary, AB, Canada

Post 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.
Wade
Forum Commoner
Posts: 41
Joined: Mon Dec 18, 2006 10:21 am
Location: Calgary, AB, Canada

Post by Wade »

/bump

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

Thanks
Wade
Forum Commoner
Posts: 41
Joined: Mon Dec 18, 2006 10:21 am
Location: Calgary, AB, Canada

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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());
}
Wade
Forum Commoner
Posts: 41
Joined: Mon Dec 18, 2006 10:21 am
Location: Calgary, AB, Canada

Post by Wade »

Wow! that looks hot! I'll try it first thing am, and let you know.

Cheers and thanks so much!!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Wade
Forum Commoner
Posts: 41
Joined: Mon Dec 18, 2006 10:21 am
Location: Calgary, AB, Canada

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You got it. Glad I could help.
Post Reply