dsmupo6631
dsmupo6631
2014-03-24 22:06

PHP BETWEEN子句用法

已采纳

PHP

I have having problem with my case, statements. I am trying to search books between 2 years but i am having trouble i can search one year using this code perfectly but trying for two is not working. I do understand i am more than likely going about this the wrong way to get desired result but any help would be greatly appreciated. Also i am getting ERROR Notice: Undefined variable: Year1 for the else part of the last case. Thanks.

If Year and Year1 have a value it should look bettwen the two years if Year just has a value just find books in that year.

<?php
include 'header.php';
include 'searchscript.php';

$sql =  "SELECT DISTINCT bk.title AS Title, bk.bookid AS BookID, bk.year AS Year, bk.publisher AS Publisher, aut.authorname AS Author 
         FROM book bk 

         JOIN book_category bk_cat 
         ON bk_cat.book_id = bk.bookid

         JOIN categories cat 
         ON cat.id = bk_cat.category_id

         JOIN books_authors bk_aut 
         ON bk_aut.book_id = bk.bookid

         JOIN authors aut
         ON aut.id = bk_aut.author_id";

if(isset($_GET['searchInput'])){
$input = $_GET['searchInput'];
$input = preg_replace('/[^A-Za-z0-9]/', '', $input);
}
if (isset($input)){

    $getters = array();
    $queries = array();

    foreach ($_GET as $key => $value) {
        $temp = is_array($value) ? $value : trim($value);
        if (!empty($temp)){
        if (!in_array($key, $getters)){
            $getters[$key] = $value;
            }
        }
    }

    if (!empty($getters)) {

        foreach($getters as $key => $value){
            ${$key} = $value;
            switch ($key) {
                case 'searchInput':
                    array_push($queries,"(bk.title LIKE '%$searchInput%' 
                    || bk.description LIKE '%$searchInput%' || bk.isbn LIKE '%$searchInput%' 
                    || bk.keywords LIKE '%$searchInput%' || aut.authorname LIKE '%$searchInput%')");
                break;
                case 'srch_publisher':
                    array_push($queries, "(bk.publisher = '$srch_publisher')");
                break;
                case 'srch_author':
                    array_push($queries, "(bk_aut.author_id = '$srch_author')");
                break;
                case 'srch_category':
                    array_push($queries, "(bk_cat.category_id = '$srch_category')");
                break;
                **case 'Year' && 'Year1':   
                    if("$Year1" ==""){
                        array_push($queries, "(bk.year = '$Year')");
                    } else {
                        array_push($queries, "(bk.year BETWEEN '$Year' AND '$Year1')");
                    }
                break;**
        }
    }
}

if(!empty($queries)){
    $sql .= " WHERE ";
    $i = 1;
    foreach ($queries as $query) {
        if($i < count($queries)){
            $sql .= $query." AND ";
        } else {
            $sql .= $query;
        }   
        $i++;
    }
}
$sql .= " GROUP BY bk.title ORDER BY bk.title ASC";

}else{
    $sql .= " GROUP BY bk.title ORDER BY bk.title ASC";
}


$rs = mysql_query($sql) or die(mysql_error());
$rows = mysql_fetch_assoc($rs);
$tot_rows = mysql_num_rows($rs);
?>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doumu1873 doumu1873 7年前

    Your code:

    foreach($getters as $key => $value)
        switch ($key) {
            case 'Year' && 'Year1':
                if("$Year1" ==""){
                    array_push($queries, "(bk.year = '$Year')");
                } else {
                    array_push($queries, "(bk.year BETWEEN '$Year' AND '$Year1')");
                }
            break;
        }
    }
    

    shows two issues:

    1. case statements don't work this way. You can't use boolean operators the same way here like when using an if() statement. (see manual)
    2. You cannot expect the iterator variable $key in foreach($getters as $key=>$value) hold both values at the same time, which you imply by saying 'Year' && 'Year1'!

    To solve those issues, you could do something like:

    foreach($getters as $key => $value)
        switch ($key) {
            case 'Year':
                if($getters["Year1"] ==""){
                    array_push($queries, "(bk.year = '{$value}')");
                } else {
                    array_push($queries, "(bk.year BETWEEN '{$value}' AND '{$getters['Year1']}')");
                }
            break;
        }
    }
    

    In this case the block is executed when the foreach($getters) hits the key 'Year'. The if statement now handles 'Year1' correctly by accessing the value in the array directly instead of looking at the iterator variables.

    点赞 评论 复制链接分享
  • dongtan3395 dongtan3395 7年前

    Adding as a seperate answer

    Your code shows severe security flaws which should be fixed!

    Excerpt:

    // 1. happily copies all GET variables into an array
    foreach ($_GET as $key => $value) {
        $getters[$key] = $value;
    }
    
    if (!empty($getters)) {
        foreach($getters as $key => $value) {
            // 2. happily assings any PHP variable in the current scope to almost 
            //    unfiltered input from a malicious user
            ${$key} = $value;
        }
    }
    
    // any variable read after this point can not be trusted because
    // the value might be manipulated by a malicious user!
    

    Also, SQL injection all over the place! i won't repeat that SQL injection story again. See related questions!

    点赞 评论 复制链接分享

为你推荐