Insert odbc results into mysql?

[benderulz]

The code below creates the table and inserts the rows, but it runs very slow. Is there a better method of inserting the results into my table? The select query could run as high as 18,000 rows depending on the time frame. Any direction on how to do this more efficiently would be appreciated.

<?php 

$con = mysqli_connect("localhost","user","password","test_tables");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// Create Outbound table
$create="CREATE TABLE pieces (PRIMARY KEY(part),part CHAR(30),picked CHAR(30))";

// Execute query
if (mysqli_query($con,$create))
  {
  echo "Table pieces created successfully";
  }
else
  {
  echo "Error creating table: " . mysqli_error($con);
  }

$conn=odbc_connect("odbc_db","user","password");

$sum = "SELECT part, Sum(qty) AS picked
FROM trans
WHERE (((date) Between '2013-12-09 07:30:00' And '2013-12-09 08:30:00') 
GROUP BY part";
   
$results = odbc_exec($conn,$sum);

while (odbc_fetch_row($results)){

$part= odbc_result($results,"part");
$picked= odbc_result($results,"picked");

mysqli_query($con, "INSERT INTO pieces (part, picked) VALUES ($part, $picked)");
}
?>

 

[jpadie]

Quicker to use the shell.

 

[benderulz]

I'm not sure what you mean when you say "use the shell"?

 

[jpadie]

I'm not sure what you mean when you say "use the shell"?

bash on linux (or sh etc). cmd in windows.

you have not specified which database is being connected to in the odbc connection.

another alternative, which will be slightly speedier is to use mysqli prepared statements. but the real slow down is the odbc driver and the routing through php.

$results = odbc_exec($conn,$sum);
$statement = mysqli_prepare($con, "INSERT INTO pieces (part, picked) VALUES (?, ?");
while (odbc_fetch_row($results)):
 mysqli_stmt_bind_param($statement, 's', odbc_result($results,"part") );
 mysqli_stmt_bind_param($statement, 's', odbc_result($results,"picked") );
 mysqli_stmt_execute($statement);
endwhile;