I created a search facility with ExtJS and PHP. The PHP file is the server side script with communicates with the database. On my Linux Environment, its working flawlessly. However, since I need to install this application on a Windows Server, I tried to copy it to my personal Windows XP.
I installed Apache, PHP with the necessary extensions, and MySQL. The logon screen loads and the authentication works. When you are authenticated, the application loads the data successfully (the data is information about people; idcard, etc...).
The problem occurs whenever I try to search. When I searched on the Linux envirnoment, it was working, now Firebug shows me the following error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'am' in 'where clause'
I don't have any column 'am'. The most similar to this is the NAME column. I tried removing chunks of code from the php file to isolate the problem but all was in vain.
Do you have any idea why the search is not working on Windowos but its working on Linux?
Here is the code for the php file:
function regexp($search, $value) {
// case insensitive hard coded
return @preg_match("/$search/i", $value);
}
function concat_ws() {
$args = func_get_args();
$sep = array_shift($args);
return implode($sep, $args);
} // eo function concat_ws
function quote_array(&$val, $key, $quot = '"') {
$quot_right = array_key_exists(1, (array) $quot) ? $quot[1] : $quot[0];
$val = is_null($val) ? "null" : $quot[0] . preg_replace("/'/", "''", $val) . $quot_right;
}
class csql {
public function getLastTable() {
$sql = "SELECT table_name, create_time FROM information_schema.TABLES WHERE table_schema = 'mydbname' ORDER BY CREATE_TIME desc LIMIT 1";
$ostmt = $this->odb->query($sql);
$tableArray = $ostmt->fetchAll(PDO::FETCH_OBJ);
$table = $tableArray[0]->table_name;
return $table;
}
// protected functions
protected function getOdb($engine) {
switch($engine) {
case "sqlite":
if("/" !== $file[0]) {
$file = realpath(".") . "/$file";
}
$odb = new PDO("sqlite:$file");
$odb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$odb->sqliteCreateFunction("regexp", "regexp", 2);
$odb->sqliteCreateFunction("concat_ws", "concat_ws");
break;
case "mysql";
$hostname = 'localhost';
$username = 'myuser';
$password = 'mypass';
$odb = new PDO("mysql:host=$hostname;dbname=mydbname", $username, $password);
$odb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
break;
}
return $odb;
}
protected function getWhere($params) {
$where = isset($where) ? "where $where" : "";
$query = "";
if($query && is_array($search) && sizeof($search)) {
$a = array();
foreach($search as $f) {
$a[] = "$f regexp '$query'";
}
$where .= $where ? " and(" : "where (";
$where .= implode(" or ", $a) . ")";
}
return $where;
}
public function __construct($engine = "mysql") {
$this->odb = $this->getOdb($engine);
}
public function getCount($params) {
// params to variables
extract($params);
$selectedFields = $_POST['fields'];
$selectedFields = explode(",", $selectedFields);
$count = 0;
foreach ($selectedFields as $selectedField)
{
if ($count == 0)
{
$selectedField = substr_replace($selectedField, "", 0, 3);
$selectedField= substr_replace($selectedField, "", -2);
$selectedFields[0] = $selectedField;
}
else if ($count == count($selectedFields)-1)
{
$selectedField = substr_replace($selectedField, "", 0, 2);
$selectedField = substr_replace($selectedField, "", -3);
$selectedFields[count($selectedFields)-1] = $selectedField;
}
else
{
$selectedField = substr_replace($selectedField, "", 0, 2);
$selectedField = substr_replace($selectedField, "", -2);
$selectedFields[$count] = $selectedField;
}
$count++;
}
if ($count == 1)
{
$selectedFields[0] = substr_replace($selectedFields[0], "", -1);
}
if ($_POST['fields'] == "")
{
$like = "";
foreach ($fields as $field)
{
$like = $like . $field . " LIKE '%" . $query . "%' or ";
}
$like = substr_replace($like, "", -3);
}
else
{
$queryExploded = explode(" ", $query);
$fullLike = "";
foreach ($queryExploded as $explode)
{
foreach ($selectedFields as $selectedField)
{
$fullLike = $fullLike . " " . $selectedField . " LIKE '%" . $explode . "%' OR ";
}
$fullLike = substr_replace($fullLike, ") AND (", -3);
}
$fullLike = substr_replace($fullLike, "", -5);
}
$count = null;
if ($_POST['fields'] == "")
{
$ostmt = $this->odb->prepare("select count(*) from {$params['table']} " . $this->getWhere($params));
}
else
{
$ostmt = $this->odb->prepare("select count(*) from {$params['table']} where (" . $fullLike . $this->getWhere($params));
}
$ostmt->bindColumn(1, $count);
$ostmt->execute();
$ostmt->fetch();
return (int) $count;
}
public function getData($params) {
$fullLike = "";
// params to variables
extract($params);
$selectedFields = $_POST['fields'];
$selectedFields = explode(",", $selectedFields);
$count = 0;
foreach ($selectedFields as $selectedField)
{
if ($count == 0)
{
$selectedField = substr_replace($selectedField, "", 0, 3);
$selectedField= substr_replace($selectedField, "", -2);
$selectedFields[0] = $selectedField;
}
else if ($count == count($selectedFields)-1)
{
$selectedField = substr_replace($selectedField, "", 0, 2);
$selectedField = substr_replace($selectedField, "", -3);
$selectedFields[count($selectedFields)-1] = $selectedField;
}
else
{
$selectedField = substr_replace($selectedField, "", 0, 2);
$selectedField = substr_replace($selectedField, "", -2);
$selectedFields[$count] = $selectedField;
}
$count++;
}
if ($count == 1)
{
$selectedFields[0] = substr_replace($selectedFields[0], "", -1);
}
if ($_POST['fields'] == "")
{
$like = "";
foreach ($fields as $field)
{
$like = $like . $field . " LIKE '%" . $query . "%' or ";
}
$like = substr_replace($like, "", -3);
}
else
{
$queryExploded = explode(" ", $query);
$fullLike = "";
foreach ($queryExploded as $explode)
{
foreach ($selectedFields as $selectedField)
{
$fullLike = $fullLike . " " . $selectedField . " LIKE '%" . $explode . "%' OR ";
}
$fullLike = substr_replace($fullLike, ") AND (", -3);
}
$fullLike = substr_replace($fullLike, "", -5);
}
$sql = "select * ";
//$sql .= implode(",", $fields);
if ($fullLike == "")
{
$sql .= " from $table";
}
else
{
$sql .= " from $table where (" . $fullLike . $this->getWhere($params);
}
$sql .= isset($groupBy) && $groupBy ? " group by $groupBy" : "";
if(!is_null($sort)) {
$sql .= " order by $sort";
$sql .= is_null($dir) ? "" : " $dir";
}
if(!is_null($start) && !is_null($limit)) {
$sql .= " limit $start,$limit";
}
$ostmt = $this->odb->query($sql);
return $ostmt->fetchAll(PDO::FETCH_OBJ);
}
This code was a sample from ExtJS. I modified it a lot. However, I though the source of the problem was from the getWhere function. However, I tried removing it and the code still remained there.
I have been struggling for the last 24 hours with this problem. The only 'solution' I found was to create the database with duplicate fields. Here is the reason:
When the ExtJS form first loads, the columns are correct, i.e. name, idcard, etc... Whenever I start searching, the first and last letter of the column name disappears, which means they become like this: am, dcar, etc...
What I've done is to create duplication columns for name, idcard, etc... naming them am, dcar, etc...
I know this is a lot of duplication of data but I cant understand why this is happening only on Windows! It sucks!
Many Thanks