Support Forums - All Posts - XOOPS Web Application System

41

Re: a simple method to return the groups names of a user

2020/2/1 3:56
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

i tried it like this


global $xoopsUser;

//get current user id 

//$uid = is_object($xoopsUser) ? $xoopsUser->getVar('uid') : t; 

$uid=$row['uid'];

//get groups from this user, if any 

$member_handler =& xoops_gethandler('member'); 

$groups =& $member_handler->getGroupsByUser($uid, true); 



//the above method will give as an array of objects, lets get the name of each group  

$usergroups = array(); 

foreach ($groups as $group) { 

    $usergroups[] = $group->getVar('name'); 

}

$usergroupsArray = implode(', ', $usergroups);

echo $usergroupsArray;

i have more than 400 users ..only the group for 31 users is displayed before i get this error


Fatal error: Call to a member function getVar() on a non-object in custom.php on line 354

line 354 in my file custom.php refers to


$usergroups[] = $group->getVar('name');

is the code above working for XOOPS 2.5.10..help

XOOPS Version XOOPS 2.5.10
PHP Version 5.4.27
mySQL Version 5.5.36

42

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

43

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;

44

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

45

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

46

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 ?

47

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

48

Re: SQL error with Tag Module

2019/11/25 0:38
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

Quote:

zyspec wrote:
@mjoel,

Sent you a PM to have you try something out to see if it works for you... let me know what you find.

zyspec..thank you very much..your fix works...

finally my tag module is now working again

49

Re: SQL error with Tag Module

2019/11/17 23:22
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

thank you zyspec

i only use the tags only in my publisher module and another 4 clone publisher module..my current publisher is 1.07 final

XOOPS Version XOOPS 2.5.10
PHP Version 5.6.40
mySQL Version 5.7.28

System 2.14
Publisher 1.07 Final (4 clones)
Tag 2.34 RC2
MyLinks 3.11 RC4
xoopstube 1.06RC1
TDMDownloads 1.66 Beta 1
User Profile 1.9
PM 1.14 Final
XSitemap 1.52RC1
Sitemap 1.3
extgallery 1.13RC1
extcal 2.39 Final
xoopspoll 1.4 Beta 1
xoopsfaq 1.01
isearch 1.9
logcounterx 2.73
protector 3.61 Final
indexscan 2.03
xlanguage 3.11
defacer 1.12 Beta1
myMenu manager 1.3

currently the table xxx_tag_tag have 524 entries and the xxx tag_link have 1648 entries

50

Re: SQL error with Tag Module

2019/11/15 0:11
mjoel
Quite a regular
Posts: 325
Since: 2006/12/9

thank you zyspec..but after i edited the block.php file i received blank page 500 error..
in debug message the sql error is now gone but i received blank page..

disable the block..the 500 error is gone

i double check the index page where my tags are not listed

i have the same sql error


0.000370 - SELECT DISTINCT(o.tag_id), o.tag_term, o.tag_status, SUM(l.tag_count) AS count, l.tag_modid FROM tag_tag AS o LEFT JOIN tag_stats AS l ON l.tag_id = o.tag_id GROUP BY o.tag_id ORDER BY count DESC LIMIT 0, 100

Error number: 1055

Error message: Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxxx.l.tag_modid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Stats
Goal:	$100.00
Due Date:	Apr 30
Gross Amount:	$0.00
Net Balance:	$0.00
Left to go:	$100.00

Forum Index

Re: a simple method to return the groups names of a user

Re: help with mysql xoops query and msaccess database

Re: help with mysql xoops query and msaccess database

Re: help with mysql xoops query and msaccess database

Re: help with mysql xoops query and msaccess database

Re: help with mysql xoops query and msaccess database

help with mysql xoops query and msaccess database

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Re: SQL error with Tag Module

Login

Search

Recent Posts

Re: Rebuilding and Updating a 20 years old Xoops Website from 2.0.18? to 2.5.11

Rebuilding and Updating a 20 years old Xoops Website from 2.0.18? to 2.5.11

Re: NewBB v 5.1.0 b 6

Re: NewBB v 5.1.0 b 6

NewBB v 5.1.0 b 6

Re: XOOPS 2.5.11 search user is not working

Re: oledrion

Re: oledrion

Re: oledrion

Re: oledrion

Who's Online

Donat-O-Meter

Latest GitHub Commits

{{ record.sha.slice(0, 7) }} - {{ record.commit.message | truncate }}