Limit to number of inserts per loop?

[Anon]

I am writing a very basic script to insert data from a query on an mssql into a mysql database.

The following script works, but only inserts the first 254 records each time. The query on the mssql database works fine(I know because it outputs to html during the loop as well as inserting to mysql).

I'm obvisouly missing something, so do me a favour and tell me if there's an easier way to do it. I'm a n00b when it comes to PHP...

Thanks.

<?

//set variables for connecting to mssql

$mshostname = "";
$msusername = "";
$mspassword = "";
$msdbName = "";

//set variables for connecting to mysql

$myhostname = "";
$myusername = "";
$mypassword = "";
$mydbname = "";
$mytablename = "";

//connect to mssql server and select database

mssql_pconnect($mshostname,$msusername,$mspassword) OR DIE("MSSQL Database connection failed.");

mssql_select_db($msdbName) or DIE("MSSQL DB unavailable");

//connect to mysql server, select database and delete contents

mysql_pconnect($myhostname,$myusername,$mypassword) OR DIE("MySQL Database connection failed.");

mysql_select_db($mydbname) or DIE("MySQL DB unavailable");

$sql_delete = "DELETE FROM $mytablename WHERE id>=0";

mysql_query($sql_delete);

//query to get required fields from finesse

$sql_out = "SELECT Account.[Acc_Reference], Account.[Acc_Description],Acc_Template.[AT_Description],Account_Balances.[AB_Actual], Account_Balances.[AB_Budget], Account_Balances.[AB_On_Order] FROM
{ oj ([Finesse].[dbo].[Account] Account
INNER JOIN [Finesse].[dbo].[Account_Balances] Account_Balances ON Account.[Acc_Id] = Account_Balances.[Acc_Id]) INNER JOIN [Finesse].[dbo].[Acc_Template] Acc_Template ON Account.[AT_Id] = Acc_Template.[AT_Id]}WHERE
Account_Balances.[FY_Id] = 6";

//run the query on the finesse database

$query = mssql_query($sql_out);

echo "<table border=1 width=100%%><tr bgcolor=yellow><td>id</td><td>Reference</td><td>Description</td><td>Template</td><td>Budget</td><td>Actual</td><td>On Order</td></tr>";

//loop to get each row from mssql

$i = 1;

while ($line = mssql_fetch_array($query)) {

//print rows to html

echo "<tr><td>",$i;
echo "</td><td>",$line["Acc_Reference"];
echo "</td><td>",$line["Acc_Description"];
echo "</td><td>",$line["AT_Description"];
echo "</td><td>",$line["AB_Budget"];
echo "</td><td>",$line["AB_Actual"];
echo "</td><td>",$line["AB_On_Order"];
echo "</td></tr>";

$acc_ref = $line["Acc_Reference"];
$acc_des = $line["Acc_Description"];
$at_des = $line["AT_Description"];
$ab_bud = $line["AB_Budget"];
$ab_act = $line["AB_Actual"];
$ab_ono = $line["AB_On_Order"];

//insert rows in mysql database

$sql_in = "INSERT INTO $mytablename (id,Acc_Reference,Acc_Description,AT_Description,AB_Budget,AB_Actual,AB_On_Order) VALUES ('$i','$acc_ref','$acc_des','$at_des','$ab_bud','$ab_act','$ab_ono')";

mysql_query($sql_in);

$i++;

} //close loop

echo "</table>";

?>

[Anon]

I'd try a couple of experiments:

1) Skip the INSERT if $i is less than 250 to see what happens when you get to 255.

2) Comment out the INSERT and see if it generates the HTML

[Anon]

LOL now I feel like an idiot.

The 'id' field in the mysql table was set to 'tinyint' instead of 'int'...

Problem solved, thanks for the suggestions.

One more thing, is this the most efficient way of manipulating the two tables at once, using m*sql_pconnect()??

Thanks