| Re: help with mysql xoops query and msaccess database |
| by Mamba on 2020/1/30 3:00:16 Thanks for reporting back, and for showing the working code |
| Re: help with mysql xoops query and msaccess database |
| by zyspec on 2020/1/29 15:59:33 Great! Glad you got it working.... |
| Re: help with mysql xoops query and msaccess database |
| by mjoel on 2020/1/29 3:30:18 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)");
|
| Re: help with mysql xoops query and msaccess database |
| by mjoel on 2020/1/28 8:18:55 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;
|
| Re: help with mysql xoops query and msaccess database |
| by mjoel on 2020/1/28 6:54:35 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();
|
Search
Recent Posts
Who's Online
Donat-O-Meter
| Stats | |
| Goal: | $15.00 |
| Due Date: | Jul 31 |
| Gross Amount: | $0.00 |
| Net Balance: | $0.00 |
| Left to go: | $15.00 |
|
|