[SOLVED] Conditional Statements

[KresentPhresh]

Okay, this is where my lack of knowledge on conditional statements freaks out. I have three sets of SQL Queries (all of which work correctly by themselves), but I want them to execute given one of three different conditions.

 

I have two database tables. (We'll call them Table One and Table Two.) I'm copying relevent information from Table One to Table Two so I can use Table Two as a sort of workbench (cuz I can't alter Table One - It's for our ERP System).

 

Here's the first part of the code;

 

	while ($row = mssql_fetch_row($result))
{list($a, $b, $c, $d, $e, $f, $g, $h, $i) = $row;

 

This is organizing the information from Table One (where we got $result) and formats it before it's ready to be entered into Table Two. The queries I have written all work just fine given this variable structure.

 

So here's what I need to do;

 

Assuming $a is the Unique Identifier and $i is the "Change ID" (the number of times that row has been altered in Table One), here's an idea of what I'm trying to do.

 

1) If the Unique Identifier already exists within this table, check the Change ID on Table Two.

2) If the Change ID is the same, start the "while" loop over again with the next set of results.

3) If the Change ID on Table Two is less than what's in $result, update that row. (Query already written.)

4) If the Unique Identifier doesn't exist at all, dump the row into Table Two.

 

Anyone able to help me structure my conditional formatting given these? Here's the full code as I have it right now;

 

	while ($row = mssql_fetch_row($result))
{list($a, $b, $c, $d, $e, $f, $g, $h, $i) = $row;

if ($changeID = mssql_query ("SELECT ChangeID FROM purchases WHERE PurchOrderID = '$a' AND POLineNbr = '$b'") && '$changeID' < '$i')

{mssql_query ("UPDATE purchases SET 

PurchOrderDate = '$c', 
VendorID = '$d', 
ItemID = '$e', 
ItemName = '$f', 
RequiredQty = '$g', 
RequiredDate = '$h', 
ChangeID = '$i' 

WHERE PurchOrderID = '$a' AND POLineNbr = '$b'"); continue;

} elseif (!$check = mssql_query ("SELECT ChangeID FROM purchases WHERE PurchOrderID = '$a' AND POLineNbr = '$b'")) 

{mssql_query("INSERT INTO purchases

(PurchOrderID, POLineNbr, PurchOrderDate, VendorID, ItemID, ItemName, RequiredQty, RequiredDate, ChangeID)

VALUES ('$a', '$b', '$c', '$d', '$e', '$f', '$g', '$h', '$i')", $smsconnect); continue;}}

 

 

Any and all help would be greatly appreciated. Thanks.

[Barand]

You might get away with

 

REPLACE INTO table2 (a,b,c,d,e,f,g,h,i)

SELECT a,b,c,d,e,f,g,h,i FROM table1

[KresentPhresh]

It's not the queries - For the sake of this whole thing, let us assume the queries are completely correct. (If you read my whole message, you might have noticed that I already said this - Ignore the queries, they work.) What I need to do is figure out how to syntactically get the code to run through the conditional statements. They should be, more or less, something like this;

 

1) IF "Unique Identifier" exists within Table Two AND If ChangeID in $result (from Table One) is higher than ChangeID in Table Two, run "Update Row Query" (which I already know works fine).

 

2) IF "Unique Identifier" exists within Table Two AND if ChangeID in $result (from Table One) is NOT HIGHER than ChangeID in Table Two, RESTART THE ORIGINAL "WHILE" LOOP MOVING TO THE NEXT ITERATION OF "$result" - (This is key, since I don't quite know how to get the loop to restart if the code is running through an IF statement nested in a WHILE statement.)

 

3) If "Unique Identifier" DOES NOT exist within Table Two, run "Dump $result into Table Two Query" (which also works fine).

 

...Any advice?

[Barand]

I suggested REPLACE because that's what is does. If the key (primary key) exists, it updates. If it doesn't exist, it inserts

[KresentPhresh]

I suggested REPLACE because that's what is does. If the key (primary key) exists, it updates. If it doesn't exist, it inserts

 

...I don't think you understand - The problem is not with the QUERY, the Query executes just fine using UPDATE. Here's my problem;

 

With the script the way I have it right now, it successfully checks the row in Table Two and, if there's a difference between the Change ID in Table Two and the Change ID in the $result from Table One, it updates it SUCCESSFULLY. The query works just fine.

 

But now it WON'T do the code underneath it. THIS part works just fine;

 

	if ($changeID = mssql_query ("SELECT ChangeID FROM purchases WHERE PurchOrderID = '$a' AND POLineNbr = '$b'") && '$changeID' < '$i')

{mssql_query ("UPDATE purchases SET 

PurchOrderDate = '$c', 
VendorID = '$d', 
ItemID = '$e', 
ItemName = '$f', 
RequiredQty = '$g', 
RequiredDate = '$h', 
ChangeID = '$i' 

WHERE PurchOrderID = '$a' AND POLineNbr = '$b'");

 

The above code is WORKING correctly - It doesn't need to be fixed. But underneath that, I have;

 

	} elseif (!$check = mssql_query ("SELECT ChangeID FROM purchases WHERE PurchOrderID = '$a' AND POLineNbr = '$b'")) 

{mssql_query("INSERT INTO purchases

(PurchOrderID, POLineNbr, PurchOrderDate, VendorID, ItemID, ItemName, RequiredQty, RequiredDate, ChangeID)

VALUES ('$a', '$b', '$c', '$d', '$e', '$f', '$g', '$h', '$i')", $smsconnect); continue;}}

 

THIS chunk of code isn't working at all - If it WAS, it would be adding a whole new row to the table, but it's not. Like I said, it has to do with the way I've structured my conditional statements, and I'm not sure which order I need to get the computer to understand that "IF this line item already exists, see if it's outdated and, if so, update it - If it DOESN'T exist, ADD it."

[KresentPhresh]

	while ($row = mssql_fetch_row($result))

{

list($a, $b, $c, $d, $e, $f, $g, $h, $i) = $row;

$verifiercheck = mssql_query

("SELECT 

PurchOrderID, 
POLineNbr, 
ChangeID 

FROM purchases 

WHERE PurchOrderID = '$a' 
AND POLineNbr = '$b'", 

$smsconnect);

$check = mssql_fetch_row($verifiercheck);

list($checkone, $checktwo, $changeID) = $check;

//NO ERRORS - This "IF" Statement updates the row in the SMS Table if the information in 
//            ERP Database has been changed.

if (isset ($checkone) && isset ($checktwo) && $changeID < $i)

{

mssql_query ("UPDATE purchases SET 

PurchOrderDate = '$c', 
VendorID = '$d', 
ItemID = '$e', 
ItemName = '$f', 
RequiredQty = '$g', 
RequiredDate = '$h', 
ChangeID = '$i' 

WHERE PurchOrderID = '$a' AND POLineNbr = '$b'", $smsconnect);

echo "Row for $CheckOne, Line Item $checktwo Updated!<br>";

} 

//NO ERRORS - This "IF" Statement inserts the entire row into the SMS Table if it isn't 
//            already there.

elseif (empty($check)) 

{

mssql_query("INSERT INTO purchases

(PurchOrderID, 
POLineNbr, 
PurchOrderDate, 
VendorID, 
ItemID, 
ItemName, 
RequiredQty, 
RequiredDate, 
ChangeID)

VALUES ('$a', '$b', '$c', '$d', '$e', '$f', '$g', '$h', '$i')", $smsconnect);

echo "Row for $a, Line Item $b Inserted!<br>";

}

//NO ERRORS - This "IF" Statement skips the row if it's already present and up-to-date.

elseif ($checkone == $a && $checktwo == $b && $changeID >= $i) 

{

echo "$checkone, Line Item $checktwo Present - Skipping!<br>";

}

//NO ERRORS - This will generate an error message if something went wrong.

else 

{echo "Something weird happened!<br><br>";}

}



?>

 

Turns out my code wasn't parsing correctly because I simply forgot to fetch the results from the returned array from the query made on Table Two.