Problema com PHP MySQL - consulta

[alanmosilva]

Olá pessoal,

Meu script que utiliza o plugin jplist (jquery) para buscar um campo no banco de dados não está funcionando com o campo select.

Ele funciona com outros campos (tipo texto, etc), mas com select ele não vai.

<?php

//added standard php/mysql config file with host, user and password info

require 'conexao.php';

function getSelectFilterQuery($keyword, $caminho, &$preparedParams){

$path = "";

$length = 1;

$query = "";

for($i=0; $i<$length; $i++){

//get path

$path = $caminho;

//replace dot

$path = str_replace(array("."), "", $path);

if($i !== 0){

$query .= " or ";

}

$query .= " " . $keyword . " like ? ";

array_push($preparedParams, "$path");

}

return $query;

}

function getSortQuery($status, &$preparedParams){

$query = "";

$data = $status->data;

$order = "asc";

if(isset($data) && isset($data->path) && $data->path){

switch($data->path){

case ".title":{

$query = "order by title";

break;

}

case ".desc":{

$query = "order by description";

break;

}

case ".like":{

$query = "order by likes";

break;

}

}

if(isset($data->order)){

$order = strtolower($data->order);

}

$order = ($order == "desc") ? "desc" : "asc";

if($query){

$query = $query . " " . $order;

}

}

return $query;

}

function getFilterQuery($status, $prevQuery, &$preparedParams){

$query = "";

$name = $status->name;

$data = $status->data;

if(isset($name) && isset($data)){

switch($name){

case "category-filter":{

if(isset($data->caminho)){

$prevQueryNotEmpty = strrpos($prevQuery, "where");

$query = "";

$filter = getSelectFilterQuery("keyword1", $data->caminho, $preparedParams);

if($filter){

if($prevQueryNotEmpty === false){

$query = "where " . $filter;

echo "

<script type= \"text/javascript\">

alert(\"".$query."\");

</script>";

}

else{

$query = " and (" . $filter . ")";

}

}

}

break;

}

}

}

return $query;

}

function getPagingQuery($status, $count, &$preparedParams){

$query = "";

$data = $status->data;

$currentPage = 0;

$number = 0;

if(isset($data)){

if(is_numeric($data->currentPage)){

$currentPage = intval($data->currentPage);

}

if(is_numeric($data->number)){

$number = intval($data->number);

}

if($count > $data->number){

$query = "LIMIT " . $currentPage * $number . ", " . $number;

}

}

return $query;

}

function getHTML($item){

$html = "";

$html .= "<div class='list-item box'>";

$html .= " <div class='img left'>";

$html .= " <a href='http://www.google.com.br'><img src='" . $item['image'] . "' alt='' title=''/></a>";

$html .= " </div>";

$html .= " <div class='block right'>";

$html .= " <p class='title'><a href='http://www.google.com.br'>" . $item['title'] . "</a></p>";

$html .= " <p class='desc'>" . $item['description'] . "</p>";

$html .= " <p class='like'>" . $item['likes'] . " Likes</p>";

$html .= " <p class='theme'>" . $item['keyword1'] . ", " . $item['keyword2'] . "</p>";

$html .= " </div>";

$html .= "</div>";

return $html;

}

function getHTMLWrapper($itemsHtml, $count){

$html = "";

$html .= "<div data-type='jplist-dataitem' data-count='" . $count . "' class='box'>";

$html .= $itemsHtml;

$html .= "</div>";

return $html;

}

/**

* entry point

*/

function init(){

try{

//connect to database

$db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);

$preparedParams = array();

$statuses = $_POST["statuses"];

$html = "";

$pagingStatus = null;

$filter = "";

$sort = "";

$query = "";

if(isset($statuses)){

//statuses => array

$statuses = json_decode(urldecode($statuses));

foreach($statuses as $key => $value){

switch($value->action){

case "paging":{

$pagingStatus = $value;

break;

}

case "filter":{

$filter .= getFilterQuery($value, $filter, $preparedParams);

break;

}

case "sort":{

$sort = getSortQuery($value, $preparedParams);

break;

}

}

}

}

//count database items for pagination

$query = "SELECT count(*) FROM Item " . $filter . " " . $sort;

if(count($preparedParams) > 0){

$stmt = $db->prepare($query);

//error_log(print_r($preparedParams, true));

$stmt->execute($preparedParams);

$count = $stmt->fetchColumn();

}

else{

$count = $db->query($query)->fetchColumn();

}

//init pagination query

if($pagingStatus){

$paging = getPagingQuery($pagingStatus, $count, $preparedParams);

}

//init query with sort and filter

$query = "SELECT title, description, image, likes, keyword1, keyword2 FROM Item " . $filter . " " . $sort . " " . $paging;

if(count($preparedParams) > 0){

$stmt = $db->prepare($query);

$stmt->execute($preparedParams);

$items = $stmt->fetchAll();

}

else{

$items = $db->query($query);

}

foreach($items as $item){

$html .= getHTML($item);

}

//print html

echo(getHTMLWrapper($html, $count));

//close the database connection

$db = NULL;

}

catch(PDOException $ex){

print 'Exception: ' . $ex->getMessage();

}

}

//start here...

init();

?>