Database Information Transfer w/ PHP

[KresentPhresh]

...Alright, I'll start out by saying I'm not the most knowledgeable with PHP. The scope of things that I actually require my code to do is pretty limited. I have a fairly decent understanding of how the code works, but now I'm just kind of stuck in a position where I'm not even sure where to look. I've searched for answers and can't seem to find an explanation or tutorial to assist me in what I'm trying to do.

 

So here's the deal - We have this fairly large database system that our ERP software runs off at work that's working off SQL Server 2000. We're beta-testing the latest release of our ERP Software on a new standalone server we recently purchased, which is working off SQL Server 2005. What I'm trying to do is query the SQL 2000 server for certain information (and when I have PHP output the returned results from the query, it all looks correct), then take the result of that query and dump it into a new table I created on the SQL 2005 server.

 

Here's the code I've written;

 

<?php

//NO ERRORS - Increase Allocatable Memory

ini_set("memory_limit","8M");

//NO ERRORS - Connect to Database Server

$initconnect = mssql_connect("<SQL 2000 Server>", "<User>", "<Password>");

//NO ERRORS - Select the Database

mssql_select_db("<ERP Database>");

//NO ERRORS - Query the PO Tables for all relevent fields

$result = mssql_query

("SELECT 

POM.POM_PurchOrderID AS PurchOrderID,
POI.POI_POLineNbr AS POLineNbr,
POM.POM_PurchOrderDate AS PurchOrderDate, 
POM.POM_VendorID AS VendorID, 
POI.POI_ItemID AS ItemID, 
POI.POI_ItemName AS ItemName,
POD.POD_RequiredQty AS RequiredQty, 
POD.POD_RequiredDate AS RequiredDate,
POM.POM_ChangeID AS ChangeID

FROM POM 
JOIN POI ON POM.POM_PurchOrderID = POI.POI_PurchOrderID
JOIN POD ON POI.POI_PurchOrderID = POD.POD_PurchOrderID AND POI.POI_POLineNbr 
	= POD.POD_POLineNbr


WHERE POD.POD_CompleteFlag = '0'
ORDER BY POM.POM_PurchOrderID");

//NO ERRORS - Set a variable that contains the number of rows of the original query

$numrows = mssql_num_rows($result); 

//NO ERRORS - Output how many rows were returned by the original Query on the PO Line Item Table

echo "There are $numrows rows of open Purchase Orders.<br><br>";

//DISABLED - Output whichever Query results you want to display for debugging

//	while ($row = mssql_fetch_assoc($result)) 
//		{while(list($var, $val) = each($row)) {print "<B>$var</B>: 
//			$val<br />";} print "<br />";}




//Below, we will be dumping information from our returned query 
//on the SQL 2000 Database into the new SQL 2005 Database we've set up.

//NO ERRORS - Connect to Supply Management System Database
$smsconnect = mssql_connect("<SQL 2005 Server>", "<user>", "<password>");

//NO ERRORS - Select the "SMS" Database
mssql_select_db("<Supply Management System Database>");

//NOT FUNCTIONING CORRECTLY - This is where we will attempt to parse the returned 
//query on the SQL 2000 Database and dump the information into the SQL 2005 Database Table 
//that we've created.

while ($row = mssql_fetch_assoc($result)) 
	{while(list($var, $val) = each($row)) {mssql_query("INSERT INTO dbo.purchases 
 	($var) VALUES ('$val')", $smsconnect);}}


?>

 

The query on the SQL 2000 Server returns 92 rows. And I get about 92 of these;

 

Warning: mssql_query() [function.mssql-query]: message: Cannot insert the value NULL into column 'PurchOrderID', table 'smssys.dbo.purchases'; column does not allow nulls. INSERT fails. (severity 16) in C:\TestSite\potest.php on line 73

 

I know I could just as easily copy the information over if I wanted to, but the purpose of this function is so I can routinely update the Supply Management System table with this information - The SMS table has a few extra columns that I've added so I can create a few custom modules to assist me.

 

...So anyone got any ideas? Thanks... And, uh, I know how irate the elitists can get about n00bs asking for help with simple problems, so if this IS a simple problem and I'm just too dumb to figure it out... Uh, sorry?

[KresentPhresh]

Update - Okay, so I did a couple of things - I changed PurchOrderID from being the PrimaryKey (which I'd have to do anyway since PurchOrderID can be used multiple times if a certain line item is there more than once) and added an AutoNumber column and set THAT as the Primary Key... And this sort of showed me another inherent issue that I was going to end up stumbling upon in the first place, SO....

 

...Ignore the above post - I'll debug this myself, now that I've at least gotten myself back to a place where I at least know what the problem is. (I assumed it would take each $var and $val and plug the whole row in that way, but...uh...it's creating a whole new row for every single value it returns...)

 

...Right... I'll add more if I run into another problem...

[KresentPhresh]

Okay, so... Something is wrong quite simply with my SQL Query, and I can't tell what the hell it is... If anyone can gimme some input on what it is I'm doing wrong, I would be very grateful, and so will all the hair I've yet to pull out...

 

Here's the Query that pulls info from the SQL 2000 Database;

 

$result = mssql_query

("SELECT

POM.POM_PurchOrderID AS PurchOrderID,
POI.POI_POLineNbr AS POLineNbr,
POM.POM_PurchOrderDate AS PurchOrderDate,
POM.POM_VendorID AS VendorID,
POI.POI_ItemID AS ItemID,
POI.POI_ItemName AS ItemName,
POD.POD_RequiredQty AS RequiredQty,
POD.POD_RequiredDate AS RequiredDate,
POM.POM_ChangeID AS ChangeID

FROM POM
JOIN POI ON POM.POM_PurchOrderID = POI.POI_PurchOrderID
JOIN POD ON POI.POI_PurchOrderID = POD.POD_PurchOrderID AND POI.POI_POLineNbr
	= POD.POD_POLineNbr


WHERE POD.POD_CompleteFlag = '0'
ORDER BY POM.POM_PurchOrderID");

 

Here's the code that tries to input that into the SQL 2005 Server.

 

	while ($row = mssql_fetch_row($result))
	{while(list($a, $b, $c, $d, $e, $f, $g, $h, $i) = $row)
{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);}}

 

And here's the error I'm getting;

 

Fatal error: Maximum execution time of 30 seconds exceeded in C:\TestSite\potest.php on line 99

 

Line 99 on TextPad is the one shown above where it says "$smsconnect);}}". I don't know how to work around that line, since I'm opening two completely seperate SQL connections and I need to tell the INSERT query which connection to go by...

 

...What the hell am I overlooking? >< Thanks...

[dj-kenpo]

you can't just export the entire database and then import onto the new one (and or don't want to?)

[KresentPhresh]

you can't just export the entire database and then import onto the new one (and or don't want to?)

 

No - Here's the deal; We got our ERP System running off the SQL 2000 Server. In my humble opinion, our ERP software totally sucks and doesn't allow me to do a handful of things that would help me immensely in managing purchasing. (I'm the Purchasing Manager at my company.) Such things like "Is the PO Confirmed?" "What was the confirmation ship date?" "Which PO's are due in the next couple of days?" I'd like to make some queues for things like that, so I can keep on top of the PO's I'm issuing out all day... It's hard to just kind of remember all of it, cuz I might do 30 PO's in a single day.

 

So the idea was to query the SQL 2000 Server for all open purchase orders, pulling out all the relevent information from a handful of different tables. There's the PO Delivery table, the PO Master table, the PO Line Item table, etc. I want to take the Purchase Order ID, the PO Line Number, and everything else in the SQL Query, then dump it into a NEW database that I've already created on an SQL Server 2005 machine we have running. This database has a table that all that information should get plugged into, and has the extra columns I need to flag certain attributes of the PO. (i.e. Is the PO Confirmed?)

 

Problem is, I'm not just trying to do a straight copy, cuz that will give me all the open PO's as of NOW, but I make more PO's every single day, and they only go into the SQL 2000 Server. I need some way of routinely pulling the information out of the SQL 2000 Server and plugging it into the 2005 Server that my Supply Management System is running off. I'll worry about data integrity once I figured out how to PUT the information into the table. (Cuz, obviously, if I run the script twice, it's going to dump the information in twice.) Our ERP System has a column for the PO Line Item table called "ChangeID" which marks how many times the PO's been altered. The next step is figuring out how to make my script check the ChangeID in the ERP Server against the one I imported into the 2005 server and, if the number is higher, then replace all the fields in that row with the updated ones from the ERP Server.

 

But if I can't even successfully TRANSFER the information in the FIRST place, then I'm SOL. At this point, I don't know what's wrong with my script - Syntactically, everything LOOKS correct, but I don't know what the hell is causing it to time out... It couldn't possibly just take that long for the query to complete... I'm at a complete loss as to what I can do at this point...