php mysql搜索框实例_php mysql搜索类(附实例)

1，php\mysql搜索类

/**

* mysql搜索类，可自定义查询条件

* by www.jbxue.com

*/

class search {

var $table;

var $field1;

var $field2;

function queryRow($query){

//定义数据库配置信息

define("host", "localhost");

define("login", "root");

define("senha", "");

//定义数据库名称

define("data", "teste");

//连接数据库

try{

$host = host;

$data = data;

$connection = new PDO("mysql:host=$host;dbname=$data", login, senha);

//$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$result = $connection->prepare($query);

$result->execute();

return $result;

$this->connection = $connection;

}catch(PDOException $e){

echo $e->getMessage();

}

}

function close($connection){

$connection = null;

}

function query($query){

$host = host;

$result = $this->queryRow($query);

$row = $result->fetch(PDO::FETCH_ASSOC);

$this->close($this->connection);

$this->query = $query;

return $row;

}

//结束连接

//显示数据字段内容

function fieldSelect(){

$query = $this->queryRow('SHOW FULL COLUMNS FROM '.$this->table);

$retorno = "\n";

foreach ($query as $collums){

if ($_POST['fieldselect'] == $collums['Field']){

$selected = " selected=\"selected\" ";

}else{

$selected = "";

}

$retorno .= "$collums[Field]\n";

}

$retorno .= "\n";

return $retorno;

}

//构造查询条件

function whereSelect(){

$wheres = array();

$wheres[] = 'equal';

$wheres[] = 'diferent';

$wheres[] = 'minor';

$wheres[] = 'more';

$wheres[] = 'minororequal';

$wheres[] = 'moreorequal';

$wheres[] = 'content';

$wheres[] = 'notcontent';

$wheres[] = 'between';

$wheres[] = 'notbetween';

$label[] = 'Equal';

$label[] = 'Diferent';

$label[] = 'Minor';

$label[] = 'More';

$label[] = 'Minor or Equal';

$label[] = 'More or Equal';

$label[] = 'Content';

$label[] = 'Not Content';

$label[] = 'Between';

$label[] = 'Not Between';

$retorno = "\n";

$i=0;

do{

if ($_POST['select'] == $wheres[$i]){

$selected = " selected=\"selected\" ";

}else{

$selected = "";

}

$retorno .= "$label[$i]\n";

$i++;

}while($i < count($wheres));

$retorno .= "\n";

return $retorno;

}

function fieldText($size, $max){

$retorno .= "\n";

return $retorno;

}

/构造条件与变量查询

function wheres($value){

$retorno = "";

//parei aqui

$this->field2 = explode(' OR ',$this->field2);

//var_dump($this->field2);

$i = 0;

switch($value){

case 'equal':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 = '$field2' ";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'diferent':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 != '$field2'";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'minor':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 < '$field2'";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'more':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 > '$field2'";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'minororequal':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 <= '$field2'";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'moreorequal':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 >= '$field2'";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'content':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 LIKE '%$field2%'";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'notcontent':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 NOT LIKE '%$field2%'";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'between':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 BETWEEN $field2";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

case 'notbetween':

foreach ($this->field2 as $field2){

$retorno .= "$this->field1 NOT BETWEEN $field2";

$i = ++$i;

if ($i != 0 && $i != count($this->field2)){

$retorno .= " OR ";

}

}

break;

}

return $retorno;

}

//输出查询结果

function result($fields){

if (isset($_POST['submit'])){

$this->field1 = $_POST['fieldselect'];

$this->field2 = $_POST['fieldtext'];

$resultfields = "";

if(is_array($fields)){

$i = 0;

foreach($fields as $collums){

if($i< count($fields)-1){

$resultfields .= $collums.', ';

}else{

$resultfields .= $collums;

}

$i = ++$i;

}

}else{

$resultfields = $fields;

}

$query = $this->queryRow("SELECT $resultfields FROM $this->table WHERE ".$this->wheres($_POST['select']));

$retorno = "

foreach($query as $querycollum){

$retorno .= "

";

if(is_array($fields)){

foreach($fields as $collumstable){

$retorno .= "

$querycollum[$collumstable]";

}

$retorno .= "

\n";

}

}

$retorno .= "

\n";

return $retorno;

}

}

}

?>

2，调用示例：

include('search.class.php');

$search = new search;

//数据表

$search->table = 'cidades';

//数组形式的结果

$result = array('id', 'Regiao');

?>

插入一条数据用于查询测试。

可以测试：between, not between, AND 等操作符。

=$search->fieldSelect()?>

=$search->whereSelect()?>

=$search->fieldText(10,20)?>

=$search->result($result)?>

3，附：sql代码：

CREATE TABLE IF NOT EXISTS `cidades` (

`id` int(11) NOT NULL auto_increment,

`Regiao` varchar(255) NOT NULL default '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--

-- Extraindo dados da tabela `cidades`

--

INSERT INTO `cidades` (`id`, `Regiao`) VALUES

(1, 'REGI?O METROPOLITANA'),

(2, 'MATA NORTE'),

(3, 'MATA SUL'),

(4, 'AGRESTE SETENTRIONAL'),

(5, 'AGRESTE CENTRAL'),

(6, 'AGRESTE MERIDIONAL'),

(7, 'MOXOT'),

(8, 'PAJE'),

(9, 'ITAPARICA'),

(10, 'SERT?O CENTRAL'),

(11, 'S?O FRANCISCO'),

(12, 'ARARIPE');