随机言php,数据库格式,提取随机言数据库

2016 年 04 月 13 日

3725 次浏览

61字数

hitokoto的自制api,写的时候没规定好,比较乱,导致条件查询的时候会慢

php部分

<?php
header("Content-Type: text/html;charset=utf-8");
$con = mysqli_connect("127.0.0.1", "root", "pwd","rdsays_db"); 
if (!$con) {
    die('Could not connect: ' . mysqli_error());
}
//select * FROM rdsay order by rand() limit 1
$result = mysqli_query($con,"SELECT * FROM rdsay B JOIN (SELECT CEIL(MAX(p_id)*RAND()) AS ID FROM rdsay) AS m ON B.p_ID >= m.ID LIMIT 1;");
while ($row = mysqli_fetch_assoc($result)) {
    $say     = $row['say'];
    $id      = $row['id'];
    $cat     = $row['cat'];
    $catname = $row['catname'];
    $source  = $row['source'];
}

if (is_array($_GET) && count($_GET) > 0) {
    if (isset($_GET["gogogo"])) {
        $paraa = $_GET["gogogo"]; //存在
        $result2 = mysqli_query($con,"UPDATE gogogo SET gogogo = '".$paraa."' WHERE id = '1' ");
    }
    if (isset($_GET["encode"])) {
        $para = $_GET["encode"]; //存在
        $result3 = mysqli_query($con,"SELECT * from gogogo WHERE id = '1' ");
        while ($row2 = mysqli_fetch_assoc($result3)) {
            $go=$row2['gogogo'];
            $gourl=$row2['url'];
              $cat_res=$row2['cat'];
        }
          
        if($go=="0" || $go=="2"){
            if($go=="0"){
                //select * FROM rdsay order by rand() limit 1
                $result = mysqli_query($con,"SELECT * FROM rdsay B JOIN (SELECT CEIL(MAX(p_id)*RAND()) AS ID FROM rdsay) AS m ON B.p_ID >= m.ID LIMIT 1;");              
              if (isset($_GET["cat"])) {
                  $cat_res = $_GET["cat"]; //存在
                  //$result = mysqli_query($con,"SELECT * FROM (select * from rdsay WHERE cat='". $cat_res ."') B JOIN (SELECT CEIL(MAX(p_id)*RAND()) AS ID FROM rdsay ) AS m ON B.p_ID >= m.ID LIMIT 1 ;");
                  $result = mysqli_query($con,"select * FROM (select * from rdsay WHERE cat='". $cat_res ."' ) aa order by rand()  limit 1 ");                
                  
              }
              
            }else if($go=="2"){
                //select * FROM rdsay order by rand() limit 1
                $result = mysqli_query($con,"SELECT * FROM rdsay1 B JOIN (SELECT CEIL(MAX(p_id)*RAND()) AS ID FROM rdsay1) AS m ON B.p_ID >= m.ID LIMIT 1;");
            }    
            
            while ($row = mysqli_fetch_assoc($result)) {
                $say     = $row['say'];
                $id      = $row['id'];
                $cat     = $row['cat'];
                $catname = $row['catname'];
                $source  = $row['source'];
            }
            if ($para == "json") {
                echo "<p>{\"say\":\"" . $say . "\",\"cat\":\"" . $cat . "\",\"catname\":\"" . $catname . "\",\"source\":\"" . $source . "\",\"id\":\"" . $id . "\"}</p>";
            }
            if ($para == "js") {
                echo "function randsay(){document.write(\"<span class='randsay'<p>".$say."</p></span>\");}" ;
            }
            if ($para == "say") {
                echo $say;
            }
            if ($para == "jsonp") {
               echo "callback({\"say\":\"" . $say . "\",\"cat\":\"" . $cat . "\",\"catname\":\"" . $catname . "\",\"source\":\"" . $source . "\",\"id\":\"" . $id . "\"})";
            }
        }else if($go=="1"){
            echo "callback({\"say\":\"1\",\"cat\":\"gogogo\",\"catname\":\"gogogo\",\"source\":\"" .$gourl. "\",\"id\":\"gogogo\"})";
        }
    }
} else {
    print '<title>Hitokoto 一言 台词</title>';
      print'<meta name="keywords" content="Hitokoto,一言,Api,免费Api,一言网,感动,动漫,二次元,台词,语句">';
      
    echo "参数有:</br>";
    echo "encode=js,json,jsonp,say</br>";
      echo "&cat=a,b,c,d,e,f</br>";
    echo "参数encode=js使用方法</br>";
    echo "脚本地址:https://lil.cx?encode=js</br>";
    echo "使用方法</br>";
    print '&lt;script type="text/javascript" src="https://lil.cx?encode=js"&gt;&lt;/script&gt;</br>';
    echo "放入展示位置</br>";
    echo '&lt;div id="randsay"&gt;&lt;script&gt;randsay()&lt;/script&gt;&lt;/div&gt;</br>';
    print "JsonP调用方法</br>";
    echo "<pre>";
    print '
&lt;script type="text/javascript" src="jquery.js"&gt;&lt;/script&gt;
&lt;script&gt;
  function getsay() {
  $.ajax({
            url: "https://lil.cx?encode=jsonp",
            dataType: "jsonp",
            async: true,
        jsonp: "callback",
        jsonpCallback: "callback",
            success: function(result) {
    $(".say").html("&lt;p&gt;" + result.say + "=="+result.source + "&lt;/p&gt;")
            },
            error: function() {
    $(".say").html("&lt;p&gt;读取失败&lt;/p&gt;")
            }
             });
   }
getsay();
&lt;/script&gt;
&lt;div class="say"&gt;&lt;/div&gt;
';

}
                        
?>

mysql字段格式(rdsay,rdsay1表)

CREATE TABLE IF NOT EXISTS `rdsay` (
  `say` char(255) NOT NULL,
  `cat` char(255) NOT NULL,
  `catname` char(255) NOT NULL,
  `source` char(255) NOT NULL,
  `id` char(20) NOT NULL,
  `p_id` bigint(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql插入格式(rdsay,rdsay1表)

INSERT into rdsay(p_id,id,say,catname,cat,source) select max(p_id)+1 ,1234523,"aaa","bbb","ccc","ddd" from rdsay;

mysql格式(gogogo表)//主要用于两张表切换//可以无视掉这个表

--
-- 表的结构 `gogogo`
--

CREATE TABLE IF NOT EXISTS `gogogo` (
  `id` varchar(255) NOT NULL,
  `gogogo` varchar(255) DEFAULT NULL,
  `url` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- 转存表中的数据 `gogogo`
--

INSERT INTO `gogogo` (`id`, `gogogo`, `url`) VALUES
('1', '0', '备注');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `gogogo`
--
ALTER TABLE `gogogo`
  ADD PRIMARY KEY (`id`);