XOOPS FAQ - The Database and Site Migration. - How do I query a database?

XOOPS has a database abstraction layer for you to use, when accessing the database.

The database object can be retrieved in two ways:
a) using the $xoopsDB instance - if you are in a function or class method, you will need to declare it global first with

global $xoopsDB;

b) calling a static getInstance() method on the Database class

$xoopsDB =& XoopsDatabaseFactory::getDatabaseConnection();

After that is done, you can use the database object to query the database

 //Any statement goes in the query - SELECT, UPDATE, INSERT etc. 
$result = $xoopsDB->query('SELECT * FROM [...] '); 
// if it is a SELECT statement, $result will now be a resultset so let's loop through it 
while ($row = $xoopsDB->fetchArray($result)) { 
    $variable = $row['index']; 
    $another_variable = $row['another_index']; 
}

If the query is not a SELECT statement, $result will be true or false, depending on whether the SQL query encountered errors or not.

See also this FAQ here

4 Comment(s)

The comments are owned by the author. We aren't responsible for their content.

My list of DB queries

kaotik Published 12/05/2004 11:14 Just can't stay away Joined 02/19/2004 Comments 861

Here are some of my db queries to access the XOOPS database.

 // To delete rom from table 
$query = "Delete from ".$xoopsDB->prefix("xoops_table")." where id='$id'"; 
 
// To insert a row into the table 
$sql = "INSERT INTO ".$xoopsDB->prefix('xoops_table'); 
$sql .= " ( xuser, emailname ) VALUES "; 
$sql .= " ( '$user', '$userwebname' )"; 
if ( ! $xoopsDB->query($sql) ) 
{ 
echo( $xoopsDB->error." : ".$xoopsDB->errno ); 
}  
 
//To update a row 
$query = "Update ".$xoopsDB->prefix("xoops_table")." smtpuname = '$smtpuname', smtppasswd = '$smtppasswd' where id='$id' "; 
 
//Select from row 
$query = 'SELECT field1, field2 FROM ' . $xoopsDB->prefix('tablename') . ' WHERE searchfield1 =1'; 
 
//Query database 
$query = "select * FROM ".$xoopsDB->prefix("xoops_table")." where uid = $userid"; 
$results=$xoopsDB->query($query,$options[0],0); 
 
//Compare 2 groups through a user uid. 1 user stored in db, other is current. I have to thank Mithrandir for giving me this code. 
$db_uid = $row['uid']; 
$userid = $xoopsUser->uid(); 
if ($userid != $db_uid) { 
$member_handler =& xoops_gethandler('member'); 
$db_groups = $member_handler->getGroupsByUser($db_uid); 
$current_groups = $xoopsUser->getGroups(); 
$common_groups = array_intersect($current_groups, $db_groups); 
} 
if ($userid==$db_uid || !empty($common_groups)){ 
//do something 
}

$xoopsDB->queryF($sql)

sinus Published 11/03/2005 3:17 Just popping in Joined 06/11/2005 Comments 27

i tried using: $xoopsDB->query($sql) to insert values into the database but it did not work. but i found out that: $xoopsDB->queryF($sql) works.

this was posted in the forums some time ago.

Re: $xoopsDB->queryF($sql)

m0nty Published 11/03/2005 3:58 Updated 10/17/2021 19:58 XOOPS is my life! Joined 10/24/2003 Comments 3337

@sinus

you should always use $xoopsDB->query($sql) as it is more secure.

if it doesn't work you have done something wrong somewhere.

using queryF() is insecure as it doesn't distinguish between _POST and _GET and will likely lead to security exploitation via XSS or CSRF

queryF() kind of deals direct avoiding http referer checks and I think bypassing the textsanitizing functions..

using query() should work from select statements, if it's not done through a select statement then the method has to be done through a $_POST method.. using $_GET method will not work with query() neither will it work if http referral is blocked.

hope this helps you somewhat.

update not workin please help

chetan Published 04/06/2008 21:50 Just popping in Joined 02/25/2008 Comments 6

//global $xoopsDB;
//$xoopsDB =& Database::getInstance();

$noavatar="noavatar.gif";
$query = "UPDATE ".$xoopsDB->prefix("users")." SET user_avatar ='$noavatar'";
$res=$xoopsDB->query($query);
echo $res;
if(!$res) {
//$xoopsTpl->assign('msg', "error: $query");
echo "error";
} else {
//$xoopsTpl->assign('msg', "Data was correctly inserted into DB!");
echo "success";
}

tell me what is wrong in this its always showing error and never success

Login

Username

Password

Remember me

Search

Advanced Search

Donat-O-Meter

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

Did you know ?

The basic structure of a XOOPS theme is similar to that of any other static website template. The primary difference is the addition of Smarty variables that interact with XOOPS to output your dynamic content.

More details...

Random question

How do I modify content?

Answer here!

How do I query a database?