insert data results from a query, NOT a form

[NoDoze]

Basically, I have two tables in a mssql db. I'm trying to manually copy one row at a time based on the ProjectSID I enter from a form. However, some ProjectSID have multiple Items.

 

To be clear, I enter a ProjectSID on a form, this php searches one table for any existing ProjectSID, then if it finds any, first deletes any existing data on the second table, then it enters the table one data into the second table.

 

This is what I currently have...

$v = $_POST["ProjectSID"];

$database = odbc_connect( "", "","");

$query="SELECT ProjectSID, EstItemSID, EstItemCD, EstItemNm FROM tblBudget_Estimate WHERE ProjectSID= $v";
$result=odbc_exec($database, $query);

$count=odbc_num_rows($result);

while (odbc_fetch_row($result))
  {
  $ProjectSID=odbc_result($result,"ProjectSID");
  $EstItemSID=odbc_result($result,"EstItemSID");
  $EstItemCD=odbc_result($result,"EstItemCD");
  $EstItemNm=odbc_result($result,"EstItemNm");
  echo "$ProjectSID $EstItemSID $EstItemCD $EstItemNm<br>";
  }
if($result){echo "<br>";}
else {exit ("NO Budget Estimate records were found!<br><br>DONE!");} 

$query2="DELETE FROM tblProject_Task WHERE ProjectSID= $v";
$result2=odbc_exec($database,$query2);
if($result2){echo "The OLD Project Task was DELETED<br>";}
else {exit ("The OLD Project Task was NOT deleted<br>");} 


$ent1="
INSERT INTO tblProject_Task (ProjectSID, TaskID, TaskNm, TaskNm_full, PerComp, ActualWork, EstItemSID) 
VALUES ('$ProjectSID', '$EstItemCD', '$EstItemNm', '$EstItemNm', 0, 0, '$EstItemSID')";
$result3=odbc_exec($database,$ent1);

if($result3){echo "The NEW Project Task was ENTERED<br>";}
else {exit ("The NEW Project Task was NOT entered<br>");} 

echo "<br>Completed Successfully<br>";

odbc_close($database);

This works when there is a single entry and the ProjectSID has a single Item...

 

Now if there are multiple Items, the "INSERT INTO tblProject_Task" will have to be repeated for each Item.

I think it needs to be a foreach loop, but have no idea how to implement it.

 

In this forums I found this:

$values = array();
foreach ($_POST['imagename'] as $imagename)
{
  $values[] = "('6666', '{$imagename}')";  
}

$sql = "INSERT INTO image_list (ProductID, imagename)
        VALUES " . implode(', ', $values);
mysql_query( $sql, $international);

...which looks fairly close... The major hangup is that all the info I find about foreach, even on google, is about pulling the data from a form, hence the POST in the example above, when in my case I'm pulling the results from a mssql query!

 

Can anyone send me a link for more info, or show me what I need to do...???

 

Thanks!

[DavidAM]

To do this in PHP, you need to do the insert inside your fetch loop:

 

// THIS IS PSUEDO-CODE DO NOT COPY AND PASTE
$sqlFind = 'SELECT * FROM projectTable1 WHERE id = ' . $projID;
$resFind = execute_query($sqlFind);
while ($rowFind = fetch($resFind)) {
  $sqlIns = 'INSERT INTO projectTable2 ...';
  $resIns = execute_query($sqlIns);
  if ($resIns) {
    $sqlDel = 'DELETE projectTable1 ...';
    execute_query($sqlDel);
  } else {
    echo 'INSERT failed ...';
    break;
  }
}

 

Do NOT DELETE the data until you check the status of the INSERT execution and are satisfied that it was successful.

 

However, if the tables are on the same connection, you can just copy them in sql:

 

INSERT INTO projectTable2 (column names here) 
SELECT column names FROM projectTable1 WHERE ID = ...

 

Then do the DELETE in a separate query call.

 

I would wrap it all in a TRANSACTION and verify that the INSERT and DELETE operated on the same number of rows to make sure that no data is lost.

 

[NoDoze]

Hmmm...I'm still thinking through this one....

 

But wanted to mention... The reason the delete is there is cause I need to delete the existing data from table2 so that I can enter the data from table1.

 

Yes, they are the same connection, I did this the first time and how/why I'm doing this now....They didn't copy properly... So I'm having to manually verify and enter the data into the second table, with the correct values.

[NoDoze]

...sigh....

 

This is what I have now....

 

$v = $_POST["ProjectSID"];

$database = odbc_connect( "", "","");

$query="SELECT ProjectSID, EstItemSID, EstItemCD, EstItemNm FROM tblBudget_Estimate WHERE ProjectSID= $v";
$result=odbc_exec($database, $query);

if($result){

{
$query2="DELETE FROM tblProject_Task WHERE ProjectSID= $v";
$result2=odbc_exec($database,$query2);
if($result2){echo "The OLD Project Task was DELETED<br>";}
else {exit ("The OLD Project Task was NOT deleted<br>");} 
}

while (odbc_fetch_row($result))
  {
  $ProjectSID=odbc_result($result,"ProjectSID");
  $EstItemSID=odbc_result($result,"EstItemSID");
  $EstItemCD=odbc_result($result,"EstItemCD");
  $EstItemNm=odbc_result($result,"EstItemNm");
  
  $ent1="
INSERT INTO tblProject_Task (ProjectSID, TaskID, TaskNm, TaskNm_full, PerComp, ActualWork, EstItemSID) 
VALUES ('$ProjectSID', '$EstItemCD', '$EstItemNm', '$EstItemNm', 0, 0, '$EstItemSID')";
$result3=odbc_exec($database,$ent1);
if($result3){echo "The NEW Project Task was ENTERED<br>";}
else {exit ("The NEW Project Task was NOT entered<br>");} 
  }
echo "<br>Completed Successfully<br>";
}
else {exit ("NO Budget Estimate records were found!<br><br>DONE!");} 


odbc_close($database);

 

And its dying at "The OLD Project Task was NOT deleted"

 

[DavidAM]

Oops, I didn't read the code very clearly did I?  Sorry, about that.

 

As to your current problem, I have had this problem (with mysql) as well.  odbc_exec() returns a resource, which is essentially an integer.  I have had it return a valid resource with an integer value of zero and therefore the if ($res) fails (zero is false).  So I now test all of my exec()'s using:

$res = exec_sql(...);
if ($res === false) { 
  // IT FAILED
} else {
  // IT WORKED -- imagine that
}

 

the three equal signs in the IF statement mean that the statement will only be TRUE if the values are the same AND have the same data type.

 

If you want to have the WORKED part first and the FAILED part last you can turn it around

if ($res !== false) {
  // IT DID NOT FAIL
} else {
  // CRIPES!  FOILED AGAIN!
}

that's two equal signs after the bang (!)

[NoDoze]

Hmmm....I can't get it to work...

[NoDoze]

ok....now I'm trying this:

 

$query3="SELECT ProjectSID, EstItemSID, EstItemCD, EstItemNm FROM tblBudget_Estimate WHERE ProjectSID= $v";
$result3=odbc_exec($database, $query3);
while (odbc_fetch_row($result3))
  {
  $ProjectSID=odbc_result($result3,"ProjectSID");
  $EstItemSID=odbc_result($result3,"EstItemSID");
  $EstItemCD=odbc_result($result3,"EstItemCD");
  $EstItemNm=odbc_result($result3,"EstItemNm");

$ent1="
INSERT INTO tblProject_Task (ProjectSID, TaskID, TaskNm, TaskNm_full, PerComp, ActualWork, EstItemSID) 
VALUES ('$ProjectSID', '$EstItemCD', '$EstItemNm', '$EstItemNm', 0, 0, '$EstItemSID')";
odbc_exec($database,$ent1);
  }

 

Any ideas would help

[NoDoze]

ok....I feel like I'm really close here...

 

while (odbc_fetch_row($result3))
  {
  $ProjectSID=odbc_result($result3,"ProjectSID");
  $EstItemSID=odbc_result($result3,"EstItemSID");
  $EstItemCD=odbc_result($result3,"EstItemCD");
  $EstItemNm=odbc_result($result3,"EstItemNm");
  $database2 = "odbc_connect(, , )";
  $insert = "INSERT INTO tblProject_Task (ProjectSID, TaskID, TaskNm, TaskNm_full, PerComp, ActualWork, EstItemSID)";
  $values = "VALUES ('$ProjectSID', '$EstItemCD', '$EstItemNm', '$EstItemNm', 0, 0, '$EstItemSID')";
  $result4 = odbc_exec($database2,$insert $values);
  }
if($result4){echo "The NEW Project Task was ENTERED<br>";}
else {echo mysql_error(); exit ("<br>The NEW Project Task was NOT entered<br>");} 

 

However, on this line:

$result4 = odbc_exec($database2,$insert $values);

I'm getting a

PHP Parse error:  syntax error, unexpected T_VARIABLE

 

When I echo it out, everything looks correct.

When I remove the $result4, I still get the same error.

 

What am I doing wrong in this line...?

 

Thanks!

[DavidAM]

$result4 = odbc_exec($database2,$insert $values);

should probably be:

$result4 = odbc_exec($database2,$insert . ' ' . $values);

[NoDoze]

Hmmmm...

 

I made the change...and now the page displays and runs...but the INSERT doesn't work. I get this new error:

odbc_exec() expects parameter 1 to be resource

[NoDoze]

Hmmmm...puzzled....

 

So basically it this there is no info in the connect line...

$database2 = "odbc_connect(server,username,password)";

 

I've tried:

$database2 = "odbc_connect('server','username','password')";

$database2 = "odbc_connect("server","username","password")";

$database2 = "odbc_connect(\"server\",\"username\",\"password\")";

...but no go....same error...

 

I tried googling to see if you can even have a odbc_connect within a while statment...but couldn't find anything...

 

...Any ideas?

 

Thanks!

[NoDoze]

ok...I think I'm closer....

 

while (odbc_fetch_row($result3))
  {
  $ProjectSID=odbc_result($result3,"ProjectSID");
  $EstItemSID=odbc_result($result3,"EstItemSID");
  $EstItemCD=odbc_result($result3,"EstItemCD");
  $EstItemNm=odbc_result($result3,"EstItemNm");
  $database2=odbc_connect( "server", "user","pasword");
  //$sql = "
  //INSERT INTO tblProject_Task (ProjectSID, TaskID, TaskNm, TaskNm_full, PerComp, ActualWork, EstItemSID) 
  //VALUES ('$ProjectSID', '$EstItemCD', '$EstItemNm', '$EstItemNm', 0, 0, '$EstItemSID')
  //";
  $result4 = odbc_prepare($database2, "
					  INSERT INTO tblProject_Task (ProjectSID, TaskID, TaskNm, TaskNm_full, PerComp, ActualWork, EstItemSID) 
						  VALUES ('$ProjectSID', '$EstItemCD', '$EstItemNm', '$EstItemNm', 0, 0, '$EstItemSID')
					  ");
  }
  
if(odbc_execute($result4)){echo "The NEW Project Task was ENTERED<br>";}
else {echo mysql_error(); exit ("<br>The NEW Project Task was NOT entered<br>");} 

 

It enters only the last insert statement...

 

Any ideas on how to get it to enter all the insert statements the while creates?

 

Thanks!

[NoDoze]

BINGO!!! IT WORKS!

 

The final code:

while (odbc_fetch_row($result3))
  {
  $ProjectSID=odbc_result($result3,"ProjectSID");
  $EstItemSID=odbc_result($result3,"EstItemSID");
  $EstItemCD=odbc_result($result3,"EstItemCD");
  $EstItemNm=odbc_result($result3,"EstItemNm");
  $database2=odbc_connect( "server", "user","password");
  $array[] = odbc_prepare($database2, "
					  INSERT INTO tblProject_Task (ProjectSID, TaskID, TaskNm, TaskNm_full, PerComp, ActualWork, EstItemSID) 
						  VALUES ('$ProjectSID', '$EstItemCD', '$EstItemNm', '$EstItemNm', 0, 0, '$EstItemSID')
					  ");
  }
  
foreach ($array as $key => $value)
{
	odbc_execute($value);
}