08CMS之数据库操作

数据库类：\libs\classes\database\mysqlquery.php


$row = $db->select('ms.,m.')->from('#__msession ms, #__members m') ->where('ms.mid = m.mid') ->_and(array('ms.msid'=> 'dzIB3C')) ->_and(array('m.password'=>'c3284d0f94606de1fd2af172aba15bf3')) ->_and(array('m.checked'=>1)) ->exec()->fetch(); var_dump($row);


$row = $db->select('m.*,s.*')->from('#__members m')
          ->innerJoin('#__members_sub s')->_on('s.mid=m.mid')
          ->where(array('mname' => 'admin'))
          ->exec()->fetch();
var_dump($row);


$row = $db->select()->from('#__members m')
          ->where('m.mid')->_in('1')
          ->limit(1)
          ->exec()->fetch();
var_dump($row);


$db->select()->from('#__members AS m')
   ->where('m.mid')
   ->_in('1, 2, 6, 7')
   ->exec();
while($row = $db->fetch())
{
    var_dump($row);
}


$row = $db->select()->from('#__members m')
          ->where('m.mid = 1')
          ->_and("checked=1")
          ->having('COUNT(*) > 0')
          ->exec()->fetch();
var_dump($row);


$row = $db->select()->from('#__members AS m')
          ->leftJoin('#__members_1 AS m1')->_on('m.mid=m1.mid')
          ->where('m.mid = 1')
          ->exec()->fetch();
var_dump($row);


$row = $db->select('COUNT(*)')->from('#__archives16')
          ->where(array('chid'=>3))
          ->_and(array('createdate'=>'1370270040'))
          ->exec()->fetch();
var_dump($row);


$row = $db->getTableList();
var_dump($row);


$db->getTableList(true)->like('cms_', '_%')->exec();
while($row = $db->fetch())
{
    var_dump($row);
}


$value = 'admin@admin.com'; $opmode = 'edit'; $mid = 2;
$db->select('mid')->from('#__members')->where(array('email' => $value));
if( $mid && ($opmode == 'edit') )
{
$db->_and("mid != {$mid}");
}
$uid = $db->exec()->fetch();
var_dump($uid);


$userInfo['username'] = 'admin';
$row = $db->select('mid, password')->from('#__members')
          ->where(array('mname' => $userInfo['username']))
          ->_and('checked = 1')
          ->exec()->fetch();
var_dump($row);


$db->insert( '#__pms', 
    array(
        'fromuser' => 'test', 
        'fromid' => 1, 
        'toid' => 12, 
        'title' => 'te"st', 
        'content' => 'test', 
        'pmdate' => time()
    )
)->exec();


$db->insert( '#__pms', 'fromuser, fromid, toid, title, content, pmdate',
    array(
        array('test', 1, 12, 'te"dddddst', 'test', time()),
        array('test', 1, 12, 'te"dddddst_', 'test', time())
    )
)->exec();


$db->insert( '#__pms', 'fromuser, fromid, toid, title, content, pmdate',
    array('test', 1, 12, 'te"ddddds__t', 'test', time())
)->exec();


$db->delete('#__pms')->where('pmid = 9')->exec();


$db->update('#__pms', array('title' => 'sdd', 'viewed' => 1))->where('pmid = 14')->exec();
$db->update('#__pms', 'title, viewed', array('sss_st"t_dddd', 0))->where('pmid = 14')->exec();
$db->update('#__pms', 'title')->where('pmid = 14')->exec(); # 设置title字段为空值
（循环）： $db->select('fc.title, fc.fcaid')->from('#__fcatalogs AS fc')->exec(); while($row = $db->fetch()) { echo "<div>{$row['fcaid']}---------{$row['title']}</div>"; }


（循环嵌套查询）：
$query = $db->select('mid')->from("#__members")->limit(3)->getQuery();
while($row = $db->fetch($query))
{
    $row2 = $db->select()->from('#__archives1')->where(array('mid' => $row['mid']))->limit(1)->exec()->fetch();
    var_dump($row, $row2);
}








 * 查询信息
 * 调用方法示例一（循环）：
 * $db->select('fc.title, fc.fcaid')->from('#__fcatalogs AS fc')->exec();
 * while($row = $db->fetch()) {
 *     echo "<div>{$row['fcaid']}---------{$row['title']}</div>";
 * }
 *
 * 调用方法示例二（循环嵌套查询）：
 * $query = $db->select('mid')->from("#__members")->limit(3)->getQuery();
   while($row = $db->fetch($query))
   {
       $row2 = $db->select()->from('#__archives1')->where(array('mid' => $row['mid']))->limit(1)->exec()->fetch();
       var_dump($row, $row2);
   }
 *
 * 调用方法示例三（查询单条）：
 * $row = $db->select('fc.title, fc.pid')->from('#__fcatalogs AS fc')->exec()->fetch();
如果该类无法满足需求可直接使用$db->query之类的原生方法查询，参看\libs\classes\database\mysql.cls.php