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 ?
$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
$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:

// 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.

implode(',', 'deptIdArray')

should be:
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: 543

  • 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


// 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
$GLOBALS['xoopsDB']->
and
$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

// 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 ?

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
// 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 »

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

411 user(s) are online (358 user(s) are browsing Support Forums)

Members: 0

Guests: 411

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Nov 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits