doushou7169
doushou7169
2010-07-26 20:36

如何动态添加多个SQL查询到分页?

已采纳

I am trying to add or connect multiple sql queries dynamically.

I am creating a pagination script and I am trying to add some filter buttons or links in this case when a user clicks on the links, it will add the id to the sql query and then display the results.

To better understand what I am saying, I will show you the script I'm working on:

Below you will see that I have three links; Marketing, Automotive, and Sports. These links represent the filter selections, so when one is clicked like 'Marketing' the the sql query will insert marketing for 'category=$ids'. This works fine, except for when I want to add multiple selections like picking 'Marketing' and 'Sports' only and not have 'Automotive' display.

Is there an easy way to add multiple ids to the sql connection instead of just one?

I hope that makes sense, if you need any further explanation let me know. Thanks for any help on this.

<?php
  $ids=$_GET['id'];
  echo $ids;

  $pageN = $_GET['page'];
  echo $pageN;
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>SUPERMAN</title>
<link href="../style.css" rel="stylesheet" type="text/css" />


<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.1/jquery.min.js"></script>
<script type="text/javascript" >

$(document).ready(function(){

// Filter content.    
    $("a.category").click(function() {

        var this_id = $(this).attr('id');
        $("#pagination").load('index.php?&id='+ this_id+'&page=1' );

    });
});
</script>


<style>
.paginate {
font-family:Arial, Helvetica, sans-serif;
    padding: 3px;
    margin: 3px;
}

.paginate a {
    padding:2px 5px 2px 5px;
    margin:2px;
    border:1px solid #999;
    text-decoration:none;
    color: #666;
}
.paginate a:hover, .paginate a:active {
    border: 1px solid #999;
    color: #000;
}
.paginate span.current {
    margin: 2px;
    padding: 2px 5px 2px 5px;
        border: 1px solid #999;

        font-weight: bold;
        background-color: #999;
        color: #FFF;
    }
    .paginate span.disabled {
        padding:2px 5px 2px 5px;
        margin:2px;
        border:1px solid #eee;
        color:#DDD;
    }

    li{
        padding:4px;
        margin-bottom:3px;
        background-color:#BBBECB;
        list-style:none;}

    ul{margin:6px;
    padding:0px;}   

</style>
</head>

<body>
<p>Fitler Results</p>
<br />
<a href="#" class="category" id="marketing">Marketing</a>

<a href="#" class="category" id="automotive">Automotive</a>

<a href="#" class="category" id="sports">Sports</a>

<br />
<br />


<?php
    include('connect.php'); 

    $tableName="explore";       
    $targetpage = "index.php";  
    $limit = 2; 

    $query = "SELECT COUNT(*) as num FROM $tableName WHERE category='$ids'";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];

    $stages = 3;
    $page = mysql_escape_string($_GET['page']);
    if($page){
        $start = ($page - 1) * $limit; 
    }else{
        $start = 0; 
        }   

    // Get page data
    //$query1 = "SELECT * FROM $tableName LIMIT $start, $limit";
    $query1 = "SELECT * FROM explore WHERE category='$ids' ORDER BY category LIMIT $start, $limit";
    $result = mysql_query($query1);

    // Initial page num setup
    if ($page == 0){$page = 1;}
    $prev = $page - 1;  
    $next = $page + 1;                          
    $lastpage = ceil($total_pages/$limit);      
    $LastPagem1 = $lastpage - 1;                    


    $paginate = '';
    if($lastpage > 1)
    {   




        $paginate .= "<div class='paginate'>";
        // Previous
        if ($page > 1){
            $paginate.= "<a href='$targetpage?page=$prev'>previous</a>";
        }else{
            $paginate.= "<span class='disabled'>previous</span>";   }



        // Pages    
        if ($lastpage < 7 + ($stages * 2))  // Not enough pages to breaking it up
        {   
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page){
                    $paginate.= "<span class='current'>$counter</span>";
                }else{
                    $paginate.= "<a href='$targetpage?&id=$ids&page=$counter'>$counter</a>";}                   
            }
        }
        elseif($lastpage > 5 + ($stages * 2))   // Enough pages to hide a few?
        {
            // Beginning only hide later pages
            if($page < 1 + ($stages * 2))       
            {
                for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)
                {
                    if ($counter == $page){
                        $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                }
                $paginate.= "...";
                $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";       
            }
            // Middle hide some front and some back
            elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2))
            {
                $paginate.= "<a href='$targetpage?page=1'>1</a>";
                $paginate.= "<a href='$targetpage?page=2'>2</a>";
                $paginate.= "...";
                for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)
                {
                    if ($counter == $page){
                        $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                }
                $paginate.= "...";
                $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";       
            }
            // End only hide early pages
            else
            {
                $paginate.= "<a href='$targetpage?page=1'>1</a>";
                $paginate.= "<a href='$targetpage?page=2'>2</a>";
                $paginate.= "...";
                for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page){
                        $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                }
            }
        }

                // Next
        if ($page < $counter - 1){ 
            $paginate.= "<a href='$targetpage?page=$next'>next</a>";
        }else{
            $paginate.= "<span class='disabled'>next</span>";
            }

        $paginate.= "</div>";       


}
 echo $total_pages.' Results';
 // pagination
 echo $paginate;
?>

<ul id="pagination">

<?php 


        while($row = mysql_fetch_array($result))
        {

        echo '<li>'.$row['site_name'].'</li>';

        }

    ?>
</ul>


</body>
</html>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doushi6864 doushi6864 11年前

    I am not sure what $ids contain, but MySQL has an IN() operator which acts like an or statement IE:

    ...WHERE category IN(1,2,3,4,5)...

    So $ids would need to be a comma delimited list, whether it is an actual title or an id. Hopefully this helps you figure out what you need to do.

    点赞 评论 复制链接分享
  • doufeng2877 doufeng2877 11年前

    You can use IN:

    SELECT COUNT(*) as num FROM $tableName WHERE category IN ($id1, $id2, $id3)
    

    You also really, really need to look into the mysql_real_escape_string() function so someone doesn't destroy your database.

    点赞 评论 复制链接分享

相关推荐