SmartFAQ is developed by The SmartFactory (http://www.smartfactory.ca), a division of InBox Solutions (http://www.inboxsolutions.net)

How do I query a database?
  Requested by Carnuke and Answered by Mithrandir on 2004/11/5 18:36:57     14322  reads 
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


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

 My list of DB queries


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
}

 
user

 $xoopsDB->queryF($sql)


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.

 
user

 Re: $xoopsDB->queryF($sql)


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

 
user

 update not workin please help


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

Lost Password? Register now!

Who's Online

62 user(s) are online (1 user(s) are browsing XOOPS FAQ)


Members: 0


Guests: 62


more...

Donat-O-Meter

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

Did you know ?

you can monitor all visitor activity through php-stats!

Random question

What is a template?