Jump to content

insert data results from a query, NOT a form


NoDoze

Recommended Posts

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!

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

...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"

 

Link to comment
Share on other sites

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 (!)

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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);
}

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.