Page 1 of 1

Link MSSQL field with MySQL field & perform query.

Posted: Tue Jan 16, 2007 3:49 pm
by JimiH
Hi

I want to link a field (TPL) from an MSSQL db with a field (TPL) from a MySQL db then perform a query to produce a report.

The query will produce all records from the MSSQL db that match a MySQL db using the linked field.

Example if the MSSQL db has five records that match the MySQL db then the report will display the five records

TPL Customer
123456 Dixons
123457 Currys

etc etc

The linked field is "TPL" which appears in both db's

I can do this no problem with a single database with PHP but with two db's on different platforms I cant do it.

Thanks

Geoff

Posted: Tue Jan 16, 2007 3:54 pm
by aaronhall
This would require that each database be able to communicate with the other, and I don't know of any applications that bridge the two. One option is to import the mssql data into mysql (or vice versa) and perform the query with one DB.

Posted: Tue Jan 16, 2007 3:57 pm
by feyd
MySQL has ODBC drivers. I don't know if that would be a solution, but it is something to check into.

Posted: Tue Jan 16, 2007 5:56 pm
by JimiH
yeah I have used ODBC within MS Access to link the two tables, this worked fine if a little slow.

I have sucessfully queried both db's within PHP with sererate connections however linking them is a little more difficult. I thought about importing one of the db's into the other but the MSSQL db is our MRP system called Fourth Shift so dont want to mess around in there and bring the company to a halt.

Our IT guy has Crystal Reports so I'll get him to produce the report I need. Would have been nice to do this all with PHP but never mind.

Thanks

Geoff

Posted: Tue Jul 03, 2007 11:28 am
by JimiH
Right what goes around comes around

I have my conn.php below

Code: Select all

// Connect to MSSQL

$server="EU-SKE-SQL-01";
$username="username";
$password="pass";
$db_name="db";
$connection=mssql_connect("$server","$username","$password") or die("I Couldn't connect");
$db=mssql_select_db($db_name,$connection) or die("I Couldn't select your database");
$branch="dbo.jt.COshipped.Orders"; // Table name


// Connect to Ms Access

$conn=odbc_connect('KPITPL','','');    //KPITPL (LOCAL DNS SETUP ON WEBSERVER)
I can produce a report which lists all records it both tables, below

Code: Select all

error_reporting(E_ALL);
     
//Connect to FS (jt_COShipped_Orders)

include("conn.php");  

//MSSQL DATA

$sqlquery = ("SELECT * FROM jt_COShipped_Orders") or die(mysql_error());  
  
$results = mssql_query($sqlquery); 

echo "<table Align = 'center' border='1'>";
echo "<tr> <th>TPL</th> <th>Shipped</th> <th>Unit Price</th> </tr>";

While ($row=mssql_fetch_array ($results)){

echo "<tr><td>";

echo $row['CatTpld'];
echo "</td><td>";

echo $row['Quantity'];
echo "</td><td>";


echo $row['UnitPrice'];
echo "</td><td>";

}

 // ACCESS DATA

$querystr  ="SELECT * FROM TPL2";       //TPL2 (TABLE NAME)

$query = odbc_exec($conn, $querystr) or die (odbc_errormsg()); 

echo "<table Align = 'center' border='1'>";
echo "<tr> <th>TPL</th> <th>Date</th> <th>Customer</th> </tr>";

while($row = odbc_fetch_array($query)) 
{ 

echo "<tr><td>";

echo $row['TPL_NUMBER'];
echo "</td><td>";

echo $row['TPLDATE'];
echo "</td><td>";

echo $row['CUSTOMER'];
echo "</td><td>";

}
Is there no way I can run a query on both these tables, using the "TPL_NUMBER" field from Access and linking it to "tpld" from MSSQL.

Maybe each time I load the page it dumps all the records into a MySQL db automatically, if thats possible?

Any ideas

Geoff