首页 文章

Php postgresql搜索查询

提问于
浏览 次
1

我不确定我是否正在进行查询，但我只是PHP和postgresql的初学者，因此我的代码 .

我想要完成的是使用日期选择器进行搜索，该日期选择器将提供日期数据：

<?php
$output = '';
if(isset($_POST['search'])) {
    $searchq = $_POST['search'];

    $query = ("SELECT trees.tree_type,tree_solds.transaction_id,tree_solds.actual_height,tree_solds.selling_height,tree_solds.sub_total,transactions.date_purchased FROM tree_solds
                                                    left join trees on tree_solds.tree_id = trees.id
                                                    left join transactions on transactions.id = tree_solds.transaction_id
                                                    WHERE date_purchased LIKE $searchq ");


                                        $result = pg_query($query); 
                                        if (!$result) { 
                                            echo "Problem with query " . $query . "
"; 
                                            echo pg_last_error(); 
                                            exit(); 
                                        } 


                        $count = pg_num_rows($result);
                        if ($count == 0) {
                            $output = 'No Data on that date!';
                        } else {
                            while ($row = pg_fetch_array($result)) {
                               $output .= printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", htmlspecialchars($myrow['transaction_id']), htmlspecialchars($myrow['date_purchased']), htmlspecialchars($myrow['tree_type']), htmlspecialchars($myrow['actual_height']), htmlspecialchars($myrow['selling_height']), number_format($myrow['sub_total'], 2));
                            }
                        }
                    }
                ?>

HTML表单

<form action="location4.php" method="post">
 <input type="text" class="span2" name="search" value="" data-date-format="yyyy-mm-dd" id="dp2">

 <button type="submit" class="btn btn-default">Submit</button>
 </form>
 <?php print("$output");?>

继续得到此错误，没有结果 .

警告：pg_query（）：查询失败：ERROR：运算符不存在：没有时区的时间戳~~整数第4行：... WHERE date_purchased LIKE 2014 -... ^提示：没有运算符匹配给定的名称和参数类型（S） . 您可能需要添加显式类型转换 . 在第50行的/Applications/MAMP/htdocs/xmastool/location4.php中查询问题SELECT.tree_type，tree_solds.transaction_id，tree_solds.actual_height，tree_solds.selling_height，tree_solds.sub_total，transactions.date_purchased FROM tree_solds在tree_solds上的左连接树.tree_id = trees.id在Transactions上保留连接事务.id = tree_solds.transaction_id WHERE date_purchased LIKE 2014-12-07错误：运算符不存在：没有时区的时间戳~~整数第4行：... WHERE date_purchased LIKE 2014- ... ^提示：没有运算符匹配给定的名称和参数类型 . 您可能需要添加显式类型转换 .

php postgresql search

3 回答

  • 0

    试试这个

    pg_query_params('SELECT ... WHERE DATE_TRUNC('day', date_purchased) = $1', array($searchq))

    有关详细信息，请参阅[文档 . 您应该 always 确保正确转义查询参数（即不与查询逐字连接）以防止SQL注入 .

    回复于
  • 0

    由于polka_bolka的提示解决了这个问题 .

    WHERE TO_CHAR(date_purchased, 'yyyy-mm-dd') LIKE '%$searchq%' ")

    将date_purchased转换为char .

    <?php
$output = '';
if(isset($_POST['search'])) {
    $searchq = $_POST['search'];

    $query = ("SELECT trees.tree_type,tree_solds.transaction_id,tree_solds.actual_height,tree_solds.selling_height,tree_solds.sub_total,transactions.date_purchased FROM tree_solds
                                                    left join trees on tree_solds.tree_id = trees.id
                                                    left join transactions on transactions.id = tree_solds.transaction_id
                                                    WHERE TO_CHAR(date_purchased, 'yyyy-mm-dd') LIKE '%$searchq%' ");


                                        $result = pg_query($query); 
                                        if (!$result) { 
                                            echo "Problem with query " . $query . "
    "; 
                                            echo pg_last_error(); 
                                            exit(); 
                                        } 


                        $count = pg_num_rows($result);
                        if ($count == 0) {
                            $output = 'No Data on that date!';
                        } else {
                            while ($row = pg_fetch_array($result)) {
                               $output .= printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", htmlspecialchars($row['transaction_id']), htmlspecialchars($row['date_purchased']), htmlspecialchars($row['tree_type']), htmlspecialchars($row['actual_height']), htmlspecialchars($row['selling_height']), number_format($row['sub_total'], 2));
                            }
                        }
                    }
                ?>
    回复于
  • 0

    使用如下： WHERE date_purchased LIKE '%$searchq%' 如果你只想要它的一部分 .

    如果你想要完全匹配，那么 WHERE date_purchased = '$searchq'

    NOTE: % 是 LIKE 中的小丑角色，请查看手册如何使用 .

    回复于

相关问题