Link MSSQL field with MySQL field & perform query.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JimiH
Forum Commoner
Posts: 92
Joined: Thu Jun 15, 2006 6:10 am

Link MSSQL field with MySQL field & perform query.

Post 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
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

MySQL has ODBC drivers. I don't know if that would be a solution, but it is something to check into.
JimiH
Forum Commoner
Posts: 92
Joined: Thu Jun 15, 2006 6:10 am

Post 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
JimiH
Forum Commoner
Posts: 92
Joined: Thu Jun 15, 2006 6:10 am

Post 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
Post Reply