transfer data from odbc to mysql with php

[jakebur01]

I am having trouble transferring data from provideX ODBC to MySQL using Navicat. So, I am wanting to write a php script that will write all of the data to a text file, then do a MySQL "DATA LOAD INFILE".

 

I am VERY limited as far as the SQL functions that are available to me with the ODBC connection. The table does not have a special unique identifier other than the item number.

 

This table I am pulling from has a little over 100,000 items.  How can I get php to process the full 100,000 rows.  I thought about getting it to process 5,000 rows sorted by item #> break > pass the last item to the next page > then process the next batch of 5,000 sorted by item # where the item is greater than the item processed on the last page. ??

 

Jake

 

I have not tested this code yet. I just kind of threw it together.

<?php
set_time_limit(900);
ini_set('max_execution_time', '999');

$myFile = "item_master.txt";
unlink($myFile);

$fp = fopen("item_master.txt", "w"); 


require("..\inc/data.inc");

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT ITEM_NUM, DESCRIPTION_1, DESCRIPTION_2, ITEM_CLASS, ALPHA_SORT, STANDARD_PACK, GL_TABLE, PRIMARY_VND_NUM, VENDOR_ITEM_NUM, ACTIVE, ITEM_PRICE_CLS FROM ic_inventry_mast";

$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
while (odbc_fetch_row($rs))
  {
$ITEM_NUM=trim(odbc_result($rs,"ITEM_NUM"));
$DESCRIPTION_1=trim(odbc_result($rs,"DESCRIPTION_1"));
$DESCRIPTION_2=trim(odbc_result($rs,"DESCRIPTION_2"));
$ITEM_CLASS=trim(odbc_result($rs,"ITEM_CLASS"));
$ALPHA_SORT=trim(odbc_result($rs,"ALPHA_SORT"));
$STANDARD_PACK=trim(odbc_result($rs,"STANDARD_PACK"));
$GL_TABLE=trim(odbc_result($rs,"GL_TABLE"));
$PRIMARY_VND_NUM=trim(odbc_result($rs,"PRIMARY_VND_NUM"));
$VENDOR_ITEM_NUM=trim(odbc_result($rs,"VENDOR_ITEM_NUM"));
$ACTIVE=trim(odbc_result($rs,"ACTIVE"));
$ITEM_PRICE_CLS=trim(odbc_result($rs,"ITEM_PRICE_CLS"));


$ITEM_NUM=str_replace('@','',$ITEM_NUM);
$DESCRIPTION_1=str_replace('@','',$DESCRIPTION_1);
$DESCRIPTION_2=str_replace('@','',$DESCRIPTION_2);
$ITEM_CLASS=str_replace('@','',$ITEM_CLASS);
$ALPHA_SORT=str_replace('@','',$ALPHA_SORT);
$STANDARD_PACK=str_replace('@','',$STANDARD_PACK);
$GL_TABLE=str_replace('@','',$GL_TABLE);
$PRIMARY_VND_NUM=str_replace('@','',$PRIMARY_VND_NUM);
$VENDOR_ITEM_NUM=str_replace('@','',$VENDOR_ITEM_NUM);
$ACTIVE=str_replace('@','',$ACTIVE);
$ITEM_PRICE_CLS=str_replace('@','',$ITEM_PRICE_CLS);


$row="$ITEM_NUM@$DESCRIPTION_1@$DESCRIPTION_2@$ITEM_CLASS@$ALPHA_SORT@$STANDARD_PACK@$GL_TABLE@$PRIMARY_VND_NUM@$VENDOR_ITEM_NUM@$ACTIVE@$ITEM_PRICE_CLS\r\n";

fwrite($fp, $row); 

  }
  
  
fclose($fp); 

?>

 

[jakebur01]

bump