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
Link MSSQL field with MySQL field & perform query.
Moderator: General Moderators
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
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
Right what goes around comes around
I have my conn.php below
I can produce a report which lists all records it both tables, below
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
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)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>";
}Maybe each time I load the page it dumps all the records into a MySQL db automatically, if thats possible?
Any ideas
Geoff