xoops forums

Forum Index


Board index » All Posts




mjoel

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

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: Today 1:13
mjoel
mjoel (Show more)
Quite a regular
Posts: 286
Since: 2006/12/9
#2

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 ?


goffy

Quite a regular
Posted on: Yesterday 17:02
goffy
goffy (Show more)
Quite a regular
Posts: 389
Since: 2010/12/27
#3

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.


zyspec

Module Developer
Posted on: Yesterday 14:06
zyspec
zyspec (Show more)
Module Developer
Posts: 1058
Since: 2004/9/21
#4

Re: help with mysql xoops query and msaccess database

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

implode(',''deptIdArray')

should be:
implode(','$deptIdArray)


zyspec

Module Developer
Posted on: Yesterday 1:48
zyspec
zyspec (Show more)
Module Developer
Posts: 1058
Since: 2004/9/21
#5

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


alain01

Quite a regular
Posted on: Yesterday 0:39
alain01
alain01 (Show more)
Quite a regular
Posts: 328
Since: 2003/6/20
#6

Re: (More ?) Useful Tags in User Message Manager

Haaaaa
Thank you Michael for this full anwser with examples !

I didnt know it !


Mamba

Moderator
Posted on: 1/26 23:48
Mamba
Mamba (Show more)
Moderator
Posts: 10828
Since: 2004/4/23
#7

Re: (More ?) Useful Tags in User Message Manager

Some of them work in the mail manager, because they are defined there, in /class/xoopsmailer.php

foreach ($this->toUsers as $user) {
            
// set some user specific variables
            
$subject str_replace('{X_UNAME}'$user->getVar('uname'), $this->subject);
            
$text    str_replace('{X_UID}'$user->getVar('uid'), $this->body);
            
$text    str_replace('{X_UEMAIL}'$user->getVar('email'), $text);
            
$text    str_replace('{X_UNAME}'$user->getVar('uname'), $text);
            
$text    str_replace('{X_UACTLINK}'XOOPS_URL '/register.php?op=actv&id=' $user->getVar('uid') . '&actkey=' $user->getVar('actkey'), $text);


You can also define them yourself in your module, as it was done, for example in SmartObject :

public static function sanitizeForCommonTags($text)
    {
        global 
$xoopsConfig;
        
$text str_replace('{X_SITENAME}'$xoopsConfig['sitename'], $text);
        
$text str_replace('{X_ADMINMAIL}'$xoopsConfig['adminmail'], $text);

        return 
$text;
    }


or in tdmspot:

$body             str_replace('{X_NAME}'$user_name$body);
            
$body             str_replace('{X_UNAME}'$user_uname$body);
            
$body             str_replace('{X_UEMAIL}'$user_email$body);
            
$body             str_replace('{X_ADMINMAIL}'$xoopsConfig['adminmail'], $body);
            
$body             str_replace('{X_SITENAME}'$xoopsConfig['sitename'], $body);
            
$body             str_replace('{X_SITEURL}'XOOPS_URL$body);
            
$tpitem['text']   = $body;
            
$meta_keywords    .= $body;
            
$meta_description .= $body;


Just search in all you modules for "{X_" and you'll find many examples of how it is done.
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs


alain01

Quite a regular
Posted on: 1/26 23:25
alain01
alain01 (Show more)
Quite a regular
Posts: 328
Since: 2003/6/20
#8

Re: (More ?) Useful Tags in User Message Manager

Only
{X_ADMINMAIL}, {X_SITENAME}, {X_SITEURL} work on the user message manager.

Others, no.
May be cause contextual variables.


alain01

Quite a regular
Posted on: 1/26 22:42
alain01
alain01 (Show more)
Quite a regular
Posts: 328
Since: 2003/6/20
#9

Re: (More ?) Useful Tags in User Message Manager

Thank you , i will try !
In which files can I find theses informations ?


Mamba

Moderator
Posted on: 1/26 22:27
Mamba
Mamba (Show more)
Moderator
Posts: 10828
Since: 2004/4/23
#10

Re: (More ?) Useful Tags in User Message Manager

There are also few extra:
{X_ADMINMAIL}
{
X_SITENAME}
{
X_SITEURL}
{
X_ITEM_TYPE
{
X_ITEM_NAME}
{
X_ITEM_TITLE}
{
X_ITEM_URL}
{
X_MODULE}
{
X_COMMENT_URL}
{
X_UNSUBSCRIBE_URL}
{
X_NOTIFY_EVENT}
{
CATEGORY_TITLE}
{
EVENT_SUMMARY}
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs



TopTop
(1) 2 3 4 ... 29267 »