PHP getListBySql函数代码示例

<?php

require_once "action/checkAamsLogin.php";
require_once "action/smarty_inc.php";
require_once "action/mysql.class.php";
$prodlist = getListBySql("select id,prodid from prod order by prodid", $db);
$smarty->assign("prodlist", $prodlist);
$joblist = getListBySql("select id,jobname from job order by id", $db);
$smarty->assign("joblist", $joblist);
$smarty->display("addjobprice.html");

    $smarty->assign("key", $_GET[key]);
}
if (isset($_GET[sdate]) && !empty($_GET[sdate])) {
    $case = $case . " and DATE_FORMAT(pddate,'%Y-%m-%d') = '" . $_GET[sdate] . "'";
    $smarty->assign("sdate", $_GET[sdate]);
}
$pagesize = 10;
//一页显示多少条
//分页
$queryTotal = $db->query("SELECT * FROM orderitem AS oi LEFT JOIN customer AS c ON oi.custid = c.id " . $case);
$total = $db->db_num_rows($queryTotal);
pageft($total, $pagesize);
if ($firstcount < 0) {
    $firstcount = 0;
}
$sql = "SELECT\r\nc.custname,\r\noi.id,\r\noi.orderid,\r\nDATE_FORMAT(oi.pddate,'%Y-%m-%d') as pddate,\r\nDATE_FORMAT(oi.jcdate,'%Y-%m-%d') as jcdate,\r\noi.ccdate,\r\noi.custid,\r\noi.orderprice,\r\noi.create_date,\r\noi.create_user,\r\noi.edit_date,\r\noi.edit_user,\r\noi.edit_reason\r\nFROM\r\norderitem AS oi\r\nLEFT JOIN customer AS c ON oi.custid = c.id " . $case . " order by id desc limit {$firstcount}, {$displaypg}";
$orderRow = getListBySql($sql, $db);
$smarty->assign("orderlist", $orderRow);
//print_r($sql);
$in = "";
for ($i = 0; $i < count($orderRow); $i++) {
    $in = $in . "'" . $orderRow[$i][id] . "',";
}
$in = substr($in, 0, strlen($in) - 1);
if (!empty($in)) {
    $orderlistRow = getListBySql("select ol.*,(select p.prodid from prod p where p.id = ol.prodid)as prodname from orderlist ol where ol.orderid in(" . $in . ")", $db);
}
$smarty->assign("orderlistlist", $orderlistRow);
//显示分页的内容
$smarty->assign("page", $pagenav);
$smarty->display("orderlist.html");

        exit;
    } else {
        include_once "action/smarty_inc.php";
        include_once "action/public_info.php";
        $db->query("select id from type where pid='" . $_GET[type] . "'");
        $cnt = $db->db_num_rows();
        $newslist = null;
        if ($cnt > 0) {
            $newslist = getListBySql("select * from type where pid='" . $_GET[type] . "'", $db);
        } else {
            require_once "action/page.class.php";
            //获取类型
            $case = 'where type_id=' . $_GET[type];
            $pagesize = 20;
            //一页显示多少条
            //分页
            $queryTotal = $db->query("select id from news " . $case);
            $total = $db->db_num_rows($queryTotal);
            pageft($total, $pagesize);
            if ($firstcount < 0) {
                $firstcount = 0;
            }
            //显示分页的内容
            $smarty->assign("page", $pagenav);
            $newslist = getListBySql("select id,title,DATE_FORMAT(create_date,'%Y-%m-%d') as create_date,click from news " . $case . " order by create_date desc limit {$firstcount}, {$displaypg} ", $db);
        }
        $smarty->assign("newslist", $newslist);
        $display = "news.htm";
        $smarty->display($display);
    }
}

    $sql = "SELECT c.type_id,c.class_name,c.course_name,ru.course,ru.id,ru.username,ru.gender,ru.college,ru.work_unit,ru.mobile,\r\n\tru.telephone,ru.qq,ru.email,ru.cnt,ru.create_date,ru.money,ru.pay_date,ru.remark,ru.class_no,t.type_name,au.adminname,ru.input_user FROM\r\n\tboya_reg_user AS ru LEFT JOIN boya_course AS c ON c.id = ru.course LEFT JOIN boya_type AS t ON c.type_id = t.id\r\n\tLEFT JOIN boya_admin_user AS au ON ru.input_user = au.id where 1=1 ";
    if ("1" == $_GET[isfee]) {
        $sql = $sql . " and pay_date is not null ";
    } else {
        if ("0" == $_GET[isfee]) {
            $sql = $sql . " and pay_date is null ";
        }
    }
    if (isset($_GET[opentime]) && !empty($_GET[opentime])) {
        //echo "<script>alert()</script>";
        $sql = $sql . " and ru.open_time ='{$_GET['opentime']}' ";
    }
    if ("-1" != $_GET[classname]) {
        $sql = $sql . " and ru.course='{$_GET['classname']}' ";
    }
    $sql = $sql . " order by ru.create_date desc";
    //print_r($sql);
    $smarty->assign("opentime", $_GET[opentime]);
    $smarty->assign("isfee", $_GET[isfee]);
    $smarty->assign("classname", $_GET[classname]);
    //δÉóºË»áÔ±·ÖÀà
    $adminRow = getListBySql($sql, $db);
    $smarty->assign("adminRow", $adminRow);
    $smarty->assign("excelname", $adminRow[0][type_name] . "-" . $adminRow[0][class_name] . "-" . $adminRow[0][course_name]);
    pageft(0, 1);
    $smarty->assign("page", $pagenav);
} else {
    pageft(0, 1);
    $smarty->assign("page", $pagenav);
}
$smarty->display("queryclass.html");

<?php

require_once "action/checkAamsLogin.php";
require_once "action/smarty_inc.php";
require_once "action/mysql.class.php";
$list = getListBySql("select * from boya_config where comm_type='ContactUs'", $db);
$smarty->assign("ContactUsInfo", $list[0]);
$conlist = getListBySql("select * from boya_config where comm_type='Contact'", $db);
$smarty->assign("ContactInfo", $conlist[0]);
$smarty->display("contactus.html");

                    if ($ms == 4) {
                        $case = $case . " and m_sal in('10','11','12') ";
                    }
                }
            }
        }
        $smarty->assign("ms", $ms);
    }
    if (isset($_GET['mon']) && !empty($_GET['mon'])) {
        $m = $_GET['mon'];
        $case = $case . " and m_sal ='" . $m . "' ";
        $smarty->assign("mon", $m);
    }
    if (isset($_GET['staffid']) && !empty($_GET['staffid'])) {
        $staffid = $_GET['staffid'];
        $case = $case . " and staffid ='" . $staffid . "' ";
        $smarty->assign("staffid", $staffid);
        $staffinfo = getListBySql("select * from staff where id='" . $staffid . "'", $db);
        $smarty->assign("staffinfo", $staffinfo[0]);
    }
    //工资总数
    //未支付
    $nopayquery = $db->query("select sum(sal) as cnt from sal where ispay='0' " . $case . " ");
    $nopayinfo = $db->fetch_array($nopayquery);
    $smarty->assign("nopaysal", $nopayinfo);
    //已支付
    $payquery = $db->query("select sum(sal) as cnt from sal where ispay='1' " . $case . " ");
    $payinfo = $db->fetch_array($payquery);
    $smarty->assign("paysal", $payinfo);
}
$smarty->display("salrpt.html");

<?php

require_once "action/checkAamsLogin.php";
require_once "action/smarty_inc.php";
require_once "action/mysql.class.php";
$list = $service = getListBySql("select id,comm_code,remark,comm_value from comm_code where comm_type='OnlineService'", $db);
$online = array();
for ($i = 0; $i < sizeof($service); $i++) {
    $tmp = array();
    if ($service[$i][remark] == "Time") {
        $tmp["id"] = $service[$i][id];
        $tmp["text"] = $service[$i][comm_code];
        $tmp["value"] = $service[$i][comm_value];
        $tmp["key"] = 'Time';
    }
    if ($service[$i][remark] == "Tell") {
        $tmp["id"] = $service[$i][id];
        $tmp["text"] = $service[$i][comm_code];
        $tmp["key"] = 'Tell';
    }
    if ($service[$i][remark] == "QQ") {
        $tmp["id"] = $service[$i][id];
        $tmp["text"] = $service[$i][comm_code];
        $tmp["value"] = $service[$i][comm_value];
        $tmp["key"] = 'QQ';
    }
    $online[$i] = $tmp;
}
$smarty->assign("online", $online);
$smarty->display("imconfig.html");

<?php

if (isset($_GET[clid]) && !empty($_GET[clid])) {
    require_once "action/checkAamsLogin.php";
    require_once "action/smarty_inc.php";
    require_once "action/mysql.class.php";
    $info = getListBySql("select * from cl where id = {$_GET['clid']}", $db);
    $smarty->assign("clinfo", $info[0]);
    $smarty->display("updatecl.html");
} else {
    echo "<script>window.history.back();</script>";
    exit;
}

<?php

if (isset($_GET[salid]) && !empty($_GET[salid])) {
    require_once "action/checkAamsLogin.php";
    require_once "action/smarty_inc.php";
    require_once "action/mysql.class.php";
    //工资总单
    $query = $db->query("select s.*,sf.id as sfid,sf.staffid,sf.staffname from sal s left join staff sf on s.staffid = sf.id where s.id = '{$_GET['salid']}'");
    $info = $db->fetch_array($query);
    $smarty->assign("salinfo", $info);
    //预支
    $m = date("m");
    $y = date("Y");
    if ($m - 1 == 0) {
        $y = $y - 1;
        $m = 12;
    } else {
        $m = $m - 1;
    }
    $yzinfo = getListBySql("select sum(money) as money from yz where m_yz={$m} and y_yz={$y} and staffid = '" . $info[sfid] . "'", $db);
    $smarty->assign("yzmoney", $yzinfo[0]);
    //print_r($info[sfid]);
    //作业清单
    $staffjob = getListBySql("SELECT oi.orderid,sj.amount,j.jobname,p.prodid,p.picname,\r\n\t\tsj.jobpriceid FROM \tstaffjob AS sj LEFT JOIN orderitem AS oi ON sj.orderid = oi.id\r\n\t\tLEFT JOIN job AS j ON j.id = sj.jobid LEFT JOIN prod AS p ON sj.prodid = p.id where sj.issal='{$_GET['salid']}' and sj.staffid='" . $info[sfid] . "'", $db);
    $smarty->assign("staffjob", $staffjob);
    $smarty->display("print.html");
} else {
    echo "<script>window.history.back();</script>";
    exit;
}

<?php

require_once "action/checkAamsLogin.php";
require_once "action/smarty_inc.php";
require_once 'action/mysql.class.php';
$type = getListBySql("select * from boya_type where pid='2' order by seq", $db);
$smarty->assign("type", $type);
$smarty->display("addclass.html");

    $case = $case . " and prodid like '%" . $_GET[key] . "%'";
    $smarty->assign("key", $_GET[key]);
}
$pagesize = 10;
//一页显示多少条
//分页
$queryTotal = $db->query("select id from prod " . $case);
$total = $db->db_num_rows($queryTotal);
pageft($total, $pagesize);
if ($firstcount < 0) {
    $firstcount = 0;
}
//未审核会员分类
$prodlist = getListBySql("select * from prod " . $case . " order by id desc limit {$firstcount}, {$displaypg}", $db);
$smarty->assign("prodRow", $prodlist);
if (sizeof($prodlist) > 0) {
    $in = "";
    for ($i = 0; $i < sizeof($prodlist); $i++) {
        $in = $in . "'" . $prodlist[$i][id] . "',";
    }
    $in = substr($in, 0, strlen($in) - 1);
    $prodlistlist = getListBySql("SELECT pl.prodid,pl.amount,pl.sumprice,cl.clid,pl.id,cl.clname,cl.clgg,\r\n\t\tcl.unit,cl.price FROM prodlist AS pl LEFT JOIN cl ON pl.clid = cl.id where pl.prodid in(" . $in . ") order by pl.prodid", $db);
    $smarty->assign("prodList", $prodlistlist);
    $joblist = getListBySql("SELECT pj.prodid,pj.id,j.jobname FROM prodjob AS pj LEFT JOIN job j ON pj.jobid = j.id where pj.prodid in(" . $in . ") order by pj.prodid", $db);
    $smarty->assign("jobList", $joblist);
}
//print_r($prodlist);
//print_r($prodlistlist);
//显示分页的内容
$smarty->assign("page", $pagenav);
$smarty->display("prodlist.html");

$trainslist = getListBySql("select id,type_name as menu_name,pid,url,seq  from boya_type where id='2'", $db);
$smarty->assign("trainsinfo", $trainslist[0]);
$trainsslist = getListBySql("select id,type_name as menu_name,pid,url,seq  from boya_type where pid = '2'  order by seq", $db);
$smarty->assign("trainsslist", $trainsslist);
$register = getListBySql("select id,type_name as menu_name,pid,url,seq  from boya_type where id='3' ", $db);
$smarty->assign("registerInfo", $register[0]);
$registers = getListBySql("select id,type_name as menu_name,pid,url,seq  from boya_type where pid = '3' order by seq", $db);
$smarty->assign("registers", $registers);
$contact = getListBySql("select id,type_name as menu_name,pid,url,seq  from  boya_type where  id='4'", $db);
$smarty->assign("contactinfo", $contact[0]);
//联系我们
$contactUs = getListBySql("select * from boya_config where comm_type='ContactUs'", $db);
$smarty->assign("contactUs", $contactUs[0]);
//SEO_Title
$SEO_Title = getInfo("boya_config where comm_type='SEO_Title'", $db);
$smarty->assign("SEO_Title", $SEO_Title);
//SEO_Keyword
$SEO_Keyword = getInfo("boya_config where comm_type='SEO_Keyword'", $db);
$smarty->assign("SEO_Keyword", $SEO_Keyword);
//SEO_Desc
$SEO_Desc = getInfo("boya_config where comm_type='SEO_Desc'", $db);
$smarty->assign("SEO_Desc", $SEO_Desc);
//底部
$Buttom = getListBySql("select * from boya_config where comm_type='Buttom'", $db);
$smarty->assign("Buttom", $Buttom[0]);
//底部
$map = getListBySql("select * from boya_config where comm_type='Map'", $db);
$smarty->assign("map", $map[0]);
//底部
$imlist = getListBySql("select * from boya_config where comm_type='Im_Account' order by id", $db);
$smarty->assign("imlist", $imlist);

<?php

if (isset($_GET[upd_id]) && !empty($_GET[upd_id])) {
    require_once "action/checkAamsLogin.php";
    require_once "action/smarty_inc.php";
    require_once "action/mysql.class.php";
    $query = $db->query("select * from staff where id = '{$_GET['upd_id']}'");
    $info = $db->fetch_array($query);
    $smarty->assign("staffinfo", $info);
    $gwlist = getListBySql("select * from gw order by gwname", $db);
    $smarty->assign("gwlist", $gwlist);
    $smarty->display("updstaff.html");
} else {
    echo "<script>window.history.back();</script>";
    exit;
}

<?php

if (isset($_GET[task]) && "updatePower" == $_GET[task]) {
    require_once "action/checkAamsLogin.php";
    require_once "action/smarty_inc.php";
    require_once 'action/mysql.class.php';
    $powerlist = getListBySql("select * from boya_admin_menu where pid='-1' order by seq", $db);
    $smarty->assign("power", $powerlist);
    $powerinfo = getListBySql("select * from boya_admin_menu where id='{$_GET['menuid']}'", $db);
    $smarty->assign("powerinfo", $powerinfo[0]);
    $smarty->display("updatepower.html");
} else {
    echo "<script>window.history.back();</script>";
}

$smarty->assign('selflist', $selfList);
////成人高考3
$crList = getListBySql("select id,title from news where type_id in(select id from type where id='3' or pid='3') order by create_date desc limit 0,8", $db);
$smarty->assign('crlist', $crList);
//远程教育4
$remoteList = getListBySql("select id,title from news where type_id in(select id from type where id='4' or pid='4') order by create_date desc limit 0,8", $db);
$smarty->assign('remotelist', $remoteList);
//民办教育5
$otherList = getListBySql("select id,title from news where type_id in(select id from type where id='5' or pid='5') order by create_date desc limit 0,8", $db);
$smarty->assign('otherlist', $otherList);
//在职研究生6
$kyList = getListBySql("select id,title from news where type_id in(select id from type where id='6' or pid='6') order by create_date desc limit 0,8", $db);
$smarty->assign('kylist', $kyList);
//思科认证8
$kjList = getListBySql("select id,type_name,isnewslist from type where pid='8' or pid in(select id from type where pid='8')", $db);
$smarty->assign('kjlist', $kjList);
$jsjList = getListBySql("select id,title from news where type_id in(select id from type where id='10' or pid='10') order by create_date desc limit 0,8", $db);
$smarty->assign('jsjlist', $jsjList);
//资料下载9
$downloadList = getListBySql("select id,title from news where type_id in(select id from type where id='9' or pid='9') order by create_date desc limit 0,8", $db);
$smarty->assign('downloadlist', $downloadList);
//就业专区7
$xlList = getListBySql("select id,title from news where type_id in(select id from type where id='7' or pid='7') order by create_date desc limit 0,8", $db);
$smarty->assign('xllist', $xlList);
//合作院校12
$zcpsList = getListBySql("select id,title from news where type_id in(select id from type where id='12' or pid='12') order by create_date desc limit 0,8", $db);
$smarty->assign('zcpslist', $zcpsList);
//友情链接
$links = getListBySql("select * from link order by remark", $db);
$smarty->assign('linklist', $links);
$smarty->display("index.htm");