data from odbc to Mysql

[rasmas]

Hi all

I need to read data from ODBC source, substring the once coloum then input the data into a Mysql Data Base

I have been able to read the data and get it substringed, the problem I am Having is getting it data into the MySQL data base.

Below is the code, if I uncomment the out "display the ODBC Result" It displays the correct result.

// open the odbc date Base
      $conn = odbc_connect('bop','','')
          or die ("Sorry - Could not Connect to odbc");


// Select Stock number and the Period      

    $sql="SELECT
STOCKNO,
PERIOD01
FROM
stockloc
WHERE
TYPE = 'N'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}

while (odbc_fetch_row($rs))
{	
  $STOCKNO=odbc_result($rs,"STOCKNO");
  $PERIOD1=odbc_result($rs,"PERIOD01");

  $n00 = substr($PERIOD1,0,10);
  $n01 = substr($PERIOD1,10,10);
  $n02 = substr($PERIOD1,20,10);
  $n02 = substr($PERIOD1,20,10);
  $n03 = substr($PERIOD1,20,10);
  $n04 = substr($PERIOD1,20,10);
  $n05 = substr($PERIOD1,20,10);
  $n06 = substr($PERIOD1,20,10);
  $n07 = substr($PERIOD1,20,10);
  $n08 = substr($PERIOD1,20,10);
  $n09 = substr($PERIOD1,20,10);
  $n10 = substr($PERIOD1,20,10);
  $n11 = substr($PERIOD1,20,10);
  $n12 = substr($PERIOD1,20,10);
  $n13 = substr($PERIOD1,20,10);
  $n14 = substr($PERIOD1,20,10);
  $n15 = substr($PERIOD1,20,10);
  $n16 = substr($PERIOD1,20,10);
  $n17 = substr($PERIOD1,20,10);
  $n18 = substr($PERIOD1,20,10);
  $n19 = substr($PERIOD1,20,10);
  $n20 = substr($PERIOD1,20,10);



// display the ODBC Result


//  echo "<table><tr>";

//  echo "<tr><td>$STOCKNO</td>";
  //echo "<td>$PERIOD1</td>";
//  echo "<td>$n00</td>";
//  echo "<td>$n01</td>";
//  echo "<td>$n02</td>";
//  echo "</tr>";
}
 odbc_close($conn);
//echo "</table>"; 


// open the mysqldate Base
      $conn = mysql_connect( "localhost", "*", "*" )
          or die ("Sorry - Could not Connect to MySQL");

 	$rs = mysql_select_db("bopfincon", $conn) 
      or die ("Sorry - Could not Connect to Database");


//insert normal stock into data base

  $sql_stock_add="insert into stocklocmarch2007 (
          					STOCKNO,
          					n00,
          					n01,
          					n02,
                    n03,
                    n04,
                    n05,
                    n06,
                    n07,
                    n08,
                    n09,
                    n010,
                    n011,
                    n012,
                    n013,
                    n014,
                    n015,
                    n016,
                    n017,
                    n018,
                    n019,
                    n020
                    )
		    					values (
          					'$STOCKNO',
          					'$00',
          					'$01',
          					'$02',
          					'$03',
          					'$04',
          					'$05',
          					'$06',
          					'$07',
          					'$08',
          					'$09',
          					'$010',
          					'$011',
          					'$012',
          					'$013',
          					'$014',
          					'$015',
          					'$016',
          					'$017',
          					'$018',
          					'$019',
          					'$020',
                    )";

					 $result = mysql_query($sql_stock_add, $conn);
# echo($sql_price_add);
 						if ($result)
								{	
									$ia++;
									set_time_limit(600);
								}	

echo("There were $ia stock items added");

I dont get any error messages, just no data is inserted into the database

Where am I going wrong

Thanks

Martin

[cahva]

In the insert sql you have '$020',. Take the , out from the end.

BTW, you are inserting only the last row of odbc results.
When you define for example:
$n00 = substr($PERIOD1,0,10);

..in a loop, $n00 will be replaced every time. So only the last row will be assigned to the variable.

If you really want to insert all of them, use arrays or just insert the rows to mysql inside the odbc loop. Theres no law against using 2 different database connections at once 😉

[rasmas]

Ok I have corrected and fixed

but now I get the following error for every line that is generated

Below is my existing code as corrected

Warning: mysql_query(): supplied resource is not a valid MySQL-Link resource in C:\webserver\monthendstockupdate.php on line 157

Thanks
Martin

<?php

// open the mysqldate Base
      $conn = mysql_connect( "localhost", "*", "*" )
          or die ("Sorry - Could not Connect to MySQL");

 	$rs = mysql_select_db("bopfincon", $conn) 
      or die ("Sorry - Could not Connect to Database");


// open the odbc date Base
      $conn = odbc_connect('bop','','')
          or die ("Sorry - Could not Connect to odbc");


// Select Stock number and the Period      

    $sql="SELECT
STOCKNO,
PERIOD01
FROM
stockloc
WHERE
TYPE = 'N'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}

while (odbc_fetch_row($rs))
{	
  $STOCKNO=odbc_result($rs,"STOCKNO");
  $PERIOD1=odbc_result($rs,"PERIOD01");

  $n00 = substr($PERIOD1,0,10);
  $n01 = substr($PERIOD1,10,10);
  $n02 = substr($PERIOD1,20,10);
  $n02 = substr($PERIOD1,30,10);
  $n03 = substr($PERIOD1,40,10);
  $n04 = substr($PERIOD1,50,10);
  $n05 = substr($PERIOD1,60,10);
  $n06 = substr($PERIOD1,70,10);
  $n07 = substr($PERIOD1,80,10);
  $n08 = substr($PERIOD1,90,10);
  $n09 = substr($PERIOD1,100,10);
  $n10 = substr($PERIOD1,110,10);
  $n11 = substr($PERIOD1,120,10);
  $n12 = substr($PERIOD1,130,10);
  $n13 = substr($PERIOD1,140,10);
  $n14 = substr($PERIOD1,150,10);
  $n15 = substr($PERIOD1,160,10);
  $n16 = substr($PERIOD1,170,10);
  $n17 = substr($PERIOD1,180,10);
  $n18 = substr($PERIOD1,190,10);
  $n19 = substr($PERIOD1,200,10);
  $n20 = substr($PERIOD1,210,10);



//insert normal stock into data base

  $sql_stock_add="insert into stocklocmarch2007 (
          					STOCKNO,
          					n00,
          					n01,
          					n02,
                                                n03,
                    n04,
                    n05,
                    n06,
                    n07,
                    n08,
                    n09,
                    n10,
                    n11,
                    n12,
                    n13,
                    n14,
                    n15,
                    n16,
                    n17,
                    n18,
                    n19,
                    n20
                    )
		    					values (
          					'$STOCKNO',
          					'$n00',
          					'$n01',
          					'$n02',
          					'$n03',
          					'$n04',
          					'$n05',
          					'$n06',
          					'$n07',
          					'$n08',
          					'$n09',
          					'$n10',
          					'$n11',
          					'$n12',
          					'$n13',
          					'$n14',
          					'$n15',
          					'$n16',
          					'$n17',
          					'$n18',
          					'$n19',
          					'$n20'
                                              )";

			 $result = mysql_query($sql_stock_add, $conn);

}
odbc_close($conn);



?>

[cahva]

You are using $conn for both mysql and odbc. So change the other one to something else(and remeber to change them to every other place accordingly).

[rasmas]

HI

Thanks I had noticed it and a few other thing to and had changed it, I also discovered the table in the database was wrong.

In my "haste" I dropped the data base and not the table.

I now need to rebuild the database before I can retest my script

Will let you know the outcome tomorrow

Thanks for the help

Martin

[rasmas]

Hi

By change the database connections and the table layout it now works.

Thanks for the Help