xoops forums

mjoel

Quite a regular
Posted on: 1/26 5:47
mjoel
mjoel (Show more)
Quite a regular
Posts: 296
Since: 2006/12/9
#1

help with mysql xoops query and msaccess database

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

zyspec

Module Developer
Posted on: 1/27 1:48
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#2

Re: help with mysql xoops query and msaccess database

@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");

zyspec

Module Developer
Posted on: 1/27 14:06
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#3

Re: help with mysql xoops query and msaccess database

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

implode(',''deptIdArray')

should be:
implode(','$deptIdArray)

goffy

Quite a regular
Posted on: 1/27 17:02
goffy
goffy (Show more)
Quite a regular
Posts: 392
Since: 2010/12/27
#4

Re: help with mysql xoops query and msaccess database

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.

mjoel

Quite a regular
Posted on: 1/28 1:13
mjoel
mjoel (Show more)
Quite a regular
Posts: 296
Since: 2006/12/9
#5

Re: help with mysql xoops query and msaccess database

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

mjoel

Quite a regular
Posted on: 1/28 1:34
mjoel
mjoel (Show more)
Quite a regular
Posts: 296
Since: 2006/12/9
#6

Re: help with mysql xoops query and msaccess database

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

mjoel

Quite a regular
Posted on: 1/28 6:54
mjoel
mjoel (Show more)
Quite a regular
Posts: 296
Since: 2006/12/9
#7

Re: help with mysql xoops query and msaccess database

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();

mjoel

Quite a regular
Posted on: 1/28 8:18
mjoel
mjoel (Show more)
Quite a regular
Posts: 296
Since: 2006/12/9
#8

Re: help with mysql xoops query and msaccess database

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 mydepartmentsdepartments AS departments 
SET mydepartments
.DEPTNAME departments.DEPTNAME
WHERE departments
.DEPTID mydepartments.DEPTID;

mjoel

Quite a regular
Posted on: 1/29 3:30
mjoel
mjoel (Show more)
Quite a regular
Posts: 296
Since: 2006/12/9
#9

Re: help with mysql xoops query and msaccess database

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)");

zyspec

Module Developer
Posted on: 1/29 15:59
zyspec
zyspec (Show more)
Module Developer
Posts: 1060
Since: 2004/9/21
#10

Re: help with mysql xoops query and msaccess database

Great! Glad you got it working....