XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. 2.5.x Support 
  4.  / help with mysql xoops query and msaccess database
Register
1
mjoel
help with mysql xoops query and msaccess database

  • 2020/1/26 5:47

  • mjoel

  • Quite a regular

  • Posts: 325

  • Since: 2006/12/9


Hi...i have this query where i have two tables mydepartments (xoops database) and department (msaccess database)

what i would like to do is to compare both of the tables and insert new data to xoops table from ms access table

how do i combine this two queries together ?
le="color: #000000"><?php $sql= "INSERT INTO ".$xoopsDB->prefix("mydepartments")."(`DEPTID`, `DEPTNAME`) SELECT DEPTID,DEPTNAME FROM DEPARTMENTS AS o WHERE o.DEPTID NOT IN (SELECT DEPTID FROM ".$xoopsDB->prefix("mydepartments").")";


right now in xoops i reveive departments tables not exist error

i used odbc to connect to msaccess database
le="color: #000000"><?php $conn=odbc_connect('mydata','',''); if (!$conn) {exit("Connection Failed: " . $conn);} $sql="SELECT DEPTID,DEPTNAME FROM DEPARTMENTS"; $rs=odbc_exec($conn,$sql); if (!$rs) {exit("Error in SQL");}


thank you in advance for any input
2
zyspec
Re: help with mysql xoops query and msaccess database

  • 2020/1/27 1:48

  • zyspec

  • Module Developer

  • Posts: 1095

  • Since: 2004/9/21


@mjoel,

I haven't tried this but I think I'd take an approach something like this:

le="color: #000000"><?php // first get a list of department IDs $result = $GLOBALS['xoopsDB']->query('SELECT DISTINCT(DEPTID) FROM ' . $GLOBALS['xoopsDB']->prefix('mydepartments')); list($deptIdArray) = $GLOBALS['xoopsDB']->fetchRow($result); // connect to the Access dB $conn = odbc_connect('mydata','',''); if (!$conn) { exit("Connection Failed: " . $conn); } // now get the Access dB departments not in XOOPS $accessQuery = "SELECT DEPTID, DEPTNAME FROM DEPARTMENTS WHERE DEPTID NOT IN (" . implode(',', 'deptIdArray') . ")"; $rs = odbc_exec($conn, $accessQuery); if (!$rs) { exit("Error in SQL"); } // now put new departments into 'mydepartments' table while(odbc_fetch_row($rs)){ list($deptId, $deptName) = odbc_result($rs, 'DEPTID', 'DEPTNAME'); $result = $GLOBALS['xoopsDB']->query("INSERT INTO " . $xoopsDB->prefix('mydepartments') . "($deptId, $deptName)); if (!$result) { exit('Error inserting new department'); } ++$i; } } exit("{$i} new departments entered into the database");
3
zyspec
Re: help with mysql xoops query and msaccess database

  • 2020/1/27 14:06

  • zyspec

  • Module Developer

  • Posts: 1095

  • Since: 2004/9/21


sorry... just looked at the code I posted again.

le="color: #000000"><?php implode(',', 'deptIdArray')

should be:
le="color: #000000"><?php implode(',', $deptIdArray)
4
goffy
Re: help with mysql xoops query and msaccess database

  • 2020/1/27 17:02

  • goffy

  • Just can't stay away

  • Posts: 554

  • Since: 2010/12/27


Hi mjoel

I do it always the other way around (because I am very familiar with access :) )
I link the mysql tables via odbc into my ms access database (only mysql-driver is necessary).
Then you can handle the data in the access db.
5
mjoel
Re: help with mysql xoops query and msaccess database

  • 2020/1/28 1:13

  • mjoel

  • Quite a regular

  • Posts: 325

  • Since: 2006/12/9


le="color: #000000"><?php // first get a list of department IDs $result = $GLOBALS['xoopsDB']->query('SELECT DISTINCT(DEPTID) FROM ' . $GLOBALS['xoopsDB']->prefix('mydepartments')); list($deptIdArray) = $GLOBALS['xoopsDB']->fetchRow($result); // connect to the Access dB $conn = odbc_connect('mydata','',''); if (!$conn) { exit("Connection Failed: " . $conn); } // now get the Access dB departments not in XOOPS $accessQuery = "SELECT DEPTID, DEPTNAME FROM DEPARTMENTS WHERE DEPTID NOT IN (" . implode(',', $deptIdArray) . ")"; $rs = odbc_exec($conn, $accessQuery); if (!$rs) { exit("Error in SQL"); } // now put new departments into 'mydepartments' table while(odbc_fetch_row($rs)){ list($deptId, $deptName) = odbc_result($rs, 'DEPTID', 'DEPTNAME'); $result = $GLOBALS['xoopsDB']->query("INSERT INTO " . $xoopsDB->prefix('mydepartments') . "($deptId, $deptName)"); if (!$result) { exit('Error inserting new department'); } ++$i; } } exit("{$i} new departments entered into the database");

Thanks zyspec for the input....i received error in SQL..

btw may i know what is the diffrence between
le="color: #000000"><?php $GLOBALS['xoopsDB']->
and
le="color: #000000"><?php $xoopsDB->
which is better ?
6
mjoel
Re: help with mysql xoops query and msaccess database

  • 2020/1/28 1:34

  • mjoel

  • Quite a regular

  • Posts: 325

  • Since: 2006/12/9


Quote:

goffy wrote:
Hi mjoel

I do it always the other way around (because I am very familiar with access :) )
I link the mysql tables via odbc into my ms access database (only mysql-driver is necessary).
Then you can handle the data in the access db.


Hi Goffy

what im trying to do here is to migrate all the data to xoops and stop using msaccess database
since the access db now is too big and slow
7
mjoel
Re: help with mysql xoops query and msaccess database

  • 2020/1/28 6:54

  • mjoel

  • Quite a regular

  • Posts: 325

  • Since: 2006/12/9


zyspec i made some adjustment to your code and now its working ..thank you again for the help

le="color: #000000"><?php // first get a list of department IDs $result=$GLOBALS['xoopsDB']->query("SELECT DEPTID FROM ".$xoopsDB->prefix("mydepartments").""); while($row=$GLOBALS['xoopsDB']->fetchArray($result)) {$deptId[]=$row['DEPTID'];} $deptIdArray = implode(', ', $deptId); // connect to the Access dB $conn = odbc_connect('mydata','',''); if (!$conn) { exit("Connection Failed: " . $conn); } // now get the Access dB departments not in XOOPS $accessQuery = "SELECT DEPTID, DEPTNAME FROM DEPARTMENTS WHERE DEPTID NOT IN ($deptIdArray)"; $rs = odbc_exec($conn, $accessQuery); if (!$rs) { exit("Error in SQL"); } // now put new departments into 'mydepartments' table while(odbc_fetch_row($rs)){ $deptId=odbc_result($rs,"DEPTID"); $deptName=odbc_result($rs,"DEPTNAME"); $resultx= $GLOBALS['xoopsDB']->query("INSERT INTO ".$xoopsDB->prefix("mydepartments")."(`DEPTID`, `DEPTNAME`) VALUES ('$deptId','$deptName')"); if (!$resultx) { redirect_header($_SERVER['PHP_SELF'], 2, 'Error inserting new department'); exit(); } ++$i; } redirect_header($_SERVER['PHP_SELF'], 2, "{$i} new departments entered into the database"); exit();
8
mjoel
Re: help with mysql xoops query and msaccess database

  • 2020/1/28 8:18

  • mjoel

  • Quite a regular

  • Posts: 325

  • Since: 2006/12/9


How about update process if there's changes in access db departments update it in xoops db mydepartments

whats the best way to combine with the code above ?

le="color: #000000"><?php UPDATE mydepartments AS mydepartments, departments AS departments SET mydepartments.DEPTNAME = departments.DEPTNAME WHERE departments.DEPTID = mydepartments.DEPTID;
9
mjoel
Re: help with mysql xoops query and msaccess database

  • 2020/1/29 3:30

  • mjoel

  • Quite a regular

  • Posts: 325

  • Since: 2006/12/9


nevermind i got it..thanks again to zyspec for the help
le="color: #000000"><?php // get the Access dB departments Name not in XOOPS $accessQuery1 = "SELECT DEPTID, DEPTNAME FROM DEPARTMENTS WHERE DEPTNAME NOT IN ('$deptNameArray')"; $rs1 = odbc_exec($conn, $accessQuery1); if (!$rs1) { exit("Error in SQL"); } // now update departments into 'mydepartments' table while(odbc_fetch_row($rs1)){ $deptId=odbc_result($rs1,"DEPTID"); $deptName=odbc_result($rs1,"DEPTNAME"); $updateprocess= $GLOBALS['xoopsDB']->query("UPDATE ".$xoopsDB->prefix("mydepartments")." SET DEPTNAME='$deptName' WHERE DEPTID='$deptId'"); } // get the Access dB all departments $accessQuery2 = "SELECT DEPTID FROM DEPARTMENTS"; $rs2 = odbc_exec($conn, $accessQuery2); if (!$rs2) { exit("Error in SQL"); } // now delete department in 'mytr_departments' table while(odbc_fetch_row($rs2)){ $deptId2[]=odbc_result($rs2,"DEPTID"); } $deptIdArray2 = implode(', ', $deptId2); $deleteprocess=$GLOBALS['xoopsDB']->queryF("DELETE FROM ".$xoopsDB->prefix("mydepartments")." WHERE DEPTID NOT IN ($deptIdArray2)");
10
zyspec
Re: help with mysql xoops query and msaccess database

  • 2020/1/29 15:59

  • zyspec

  • Module Developer

  • Posts: 1095

  • Since: 2004/9/21


Great! Glad you got it working....
(1) 2 »

Post Reply

      
9 - 3 = ?  
Input the result from the expression
Maximum attempts you can try: 10
* = Required

Search

Advanced Search

Recent Posts

Who's Online

194 user(s) are online (157 user(s) are browsing Support Forums)

Members: 0

Guests: 194

more...

Donat-O-Meter

Stats
Goal: $15.00
Due Date: Jul 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $15.00
Make donations with PayPal!

Latest GitHub Commits