I have a table orders where in i have data stored in Unicode format. The other data is in unicode format i.e Hindi.
Screenshot of the table structure and data view is enclosed herewith.
The sql dump for the table is given below:
CREATE TABLE IF NOT EXISTS `orders` (`sno` varchar(11) NOT NULL,`CaseNO` varchar(255) NOT NULL,`Court` varchar(255) NOT NULL, `Yr` varchar(4) NOT NULL,`CaseType` varchar(50) NOT NULL,`Dt_Disposal` varchar(10) NOT NULL, `App_Name` longtext NOT NULL, `App_F_Name` longtext NOT NULL, `Res_Name` text NOT NULL, `Res_F_Name` text NOT NULL, `Pet_Cou_Name` varchar(255) NOT NULL, `Res_Cou_Name` varchar(255) NOT NULL, `Pro_Off_Name` varchar(255) NOT NULL,`District` varchar(255) NOT NULL, `Division` varchar(255) NOT NULL,`Tehsil` varchar(255) NOT NULL, `Path` longtext NOT NULL, `UName` varchar(255) NOT NULL, `CCY` varchar(255) NOT NULL, `Page_Cnt` int(11) NOT NULL,`Dis_year` varchar(4) NOT NULL, PRIMARY KEY (`sno`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
–
– Dumping data for table orders_bkp
INSERT INTO `orders` (`sno`, `CaseNO`, `Court`, `Yr`, `CaseType`, `Dt_Disposal`, `App_Name`, `App_F_Name`, `Res_Name`, `Res_F_Name`, `Pet_Cou_Name`, `Res_Cou_Name`, `Pro_Off_Name`, `District`, `Division`, `Tehsil`, `Path`, `UName`, `CCY`, `Page_Cnt`, `Dis_year`) VALUES ('1', '127', 'PBR', '76', 'अपील', '13/01/86', 'सनमान सिंह', 'मुंशीलाल', 'मध्य प्रदेश शासन', '', 'अरूण मिश्रा', 'आर०एस० बाजपेई', 'सुरेन्द्र नाथ', 'विदिशा', 'भोपाल संभाग', 'विदिशा', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000001.tif', 'Akash:1/2/2018 11:47:42 AM', '127-PBR/76', 8, '1986'), ('10', '39', 'III', '85', 'अपील', '17/01/86', 'अब्बास अहमद', 'अब्दुल बहबल', 'जिला सहकारी केन्द्रीय बैंक मर्यादित', '', 'आर०के० मेहता', 'वी०एम० निमगॉंवकर', 'सुरेन्द्र नाथ', 'उज्जैन', 'उज्जैन संभाग', 'उज्जैन', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000083.tif', 'Akash:3/14/2011 12:25:18 AM', '39-III/85', 4, '1986'),('19', '47', 'III', '85', 'अपील', '29/01/86', 'मेसर्स जयन्तीलाल एण्ड क०', '', 'विक्रय कर आयुक्त म०प्र०', '', 'जी०एल० यदु', 'आर०एन० झरिया', 'सुरेन्द्र नाथ', '', '', '', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000087.tif', 'Akash:1/3/2018 10:50:50 AM', '47-III/85', 3, '1986'),('2', '8', 'I', '85', 'अपील', '31/01/86', 'नटवर लाल', '', 'विक्रय कर आयुक्त म०प्र०', '', '', 'ए०सी० तिवारी', 'एम०बी०आर०', 'भोपाल', 'भोपाल संभाग', '', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000050.tif', 'Akash:1/2/2018 3:37:41 PM', '8-I/85', 5, '1986'),('2', '183', 'I', '83', 'अपील', '16/01/86', 'मेसर्स संजीव कुमार विकास कुमार', '', 'विक्रय कर आयुक्त म०प्र०', '', 'रामचरण लाल गुप्ता', 'ए०सी० तिवारी', 'स०च० जैन', 'भिण्ड', 'चम्बल संभाग', 'गोहद', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000009.tif', 'Akash:1/2/2018 11:53:31 AM', '183-I/83', 9, '1986'),('20', '48', 'III', '85', 'अपील', '20/01/86', 'जिला सहकारी केन्द्रीय बैंक मर्यादित', '', 'ब्रजनंदन खरे', '', 'एस०के० जैन', 'एस०के० अवस्थी', 'सुरेन्द्र नाथ', 'टीकमगढ़', 'सागर संभाग', 'टीकमगढ़', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000090.tif', 'Akash:1/3/2018 11:01:45 AM', '48-III/85', 2, '1986'),('21', '49', 'III', '85', 'अपील', '29/01/86', 'मेसर्स एस०के० ट्रेडिंग क०', '', 'विक्रय कर आयुक्त म०प्र०', '', 'के०सी० देशलेहरा', 'आर०एन० झरिया', 'सुरेन्द्र नाथ', '', '', '', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000092.tif', 'Akash:1/3/2018 11:10:39 AM', '49-III/85', 4, '1986'),('22', '50', 'III', '85', 'अपील', '29/01/86', 'मेसर्स अलाइड स्टील मर्यादित', '', 'विक्रय कर आयुक्त म०प्र०', '', 'एल०के० अहलूवालिया', 'आर०एन० झरिया', 'सुरेन्द्र नाथ', '', '', '', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000096.tif', 'Akash:1/3/2018 11:26:05 AM', '50-III/85', 4, '1986'),('23', '54', 'III', '85', 'अपील', '20/01/86', 'नाथूराम यादव', 'रामदास यादव', 'वृहत्ताकार सहकारी समिति मर्यादित ~सहायक पंजीयक, सहकारी समितियां ~छिद्दूलाल', 'कामता प्रसाद', 'एस०के० अवस्थी', 'एस०के० वाजपेई', 'सुरेन्द्र नाथ', 'टीकमगढ़', 'सागर संभाग', 'टीकमगढ़', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000100.tif', 'Akash:1/3/2018 11:39:17 AM', '54-III/85', 3, '1986'),('24', '58', 'III', '85', 'अपील', '24/01/86', 'सागर जिला सहकारी भूमि विकास बैंक मर्यादित', '', 'सागर सहकारी कृषक एवं विपणन संस्था मर्या०', '', 'एन०डी० सिंघल', '', 'सुरेन्द्र नाथ', 'सागर', 'सागर संभाग', 'सागर', 'A:\\Gwalior\\Appeal_complete\\1986\\Jan\\1000103.tif', 'Akash:1/3/2018 11:56:45 AM', '58-III/85', 2, '1986');
The code for the search parameters is given below
<form action="test2.php" method="post" name="form1">
<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td colspan="3" align="center" bgcolor="#000000" class="title3">Please select the search criteria</td>
</tr>
<tr>
<td width="45%" align="right" class="formtext">Case No</td>
<td width="2%" align="center"><strong class="formtext">:</strong></td>
<td width="53%"><input type="text" name="CaseNO" id="CaseNO">
<span class="text10pt">
</span></td>
</tr>
<tr align="right" class="formtext">
<td>Case Year</td>
<td align="center"><strong>:</strong></td>
<td align="left"><label>
<input type="text" name="Yr" id="Yr">
</label></td>
</tr>
<tr class="formtext">
<td align="right">Case Type</td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="CaseType" id="CaseType"></td>
</tr>
<tr class="formtext">
<td align="right">Court</td>
<td align="center">:</td>
<td><input type="text" name="Court" id="Court"></td>
</tr>
<tr class="formtext">
<td align="right">Date of Disposal</td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="Dt_Disposal" id="Dt_Disposal"></td>
</tr>
<tr>
<td align="right" class="formtext">Applicant Name</td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="App_Name" id="App_Name"></td>
</tr>
<tr>
<td align="right" class="formtext">Respondent Name</td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="Res_Name" id="Res_Name"></td>
</tr>
<tr>
<td align="right"><span class="formtext">Petitioner Counsel Name</span></td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="Pet_Cou_Name" id="Pet_Cou_Name"></td>
</tr>
<tr>
<td align="right"><span class="formtext">Respondent Counsel Name</span></td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="Res_Cou_Name" id="Res_Cou_Name"></td>
</tr>
<tr>
<td align="right"><span class="formtext">District</span></td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="District" id="District"></td>
</tr>
<tr>
<td align="right"><span class="formtext">Proceeding Officer Name</span></td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="Pro_Off_Name" id="Pro_Off_Name"></td>
</tr>
<tr>
<td align="right"><span class="formtext">Divison</span></td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="Division" id="Division"></td>
</tr>
<tr>
<td align="right"><span class="formtext">Tehsil</span></td>
<td align="center"><strong class="formtext">:</strong></td>
<td><input type="text" name="Tehsil" id="Tehsil"></td>
</tr>
<tr>
<td> </td>
<td><input name="id_sflag" type="hidden" id="id_sflag" value="1"></td>
<td> </td>
</tr>
<tr>
<td align="right"><label>
<input type="submit" name="button5" id="button7" value="Submit">
</label></td>
<td> </td>
<td><input type="reset" name="button5" id="button8" value="Reset"></td>
</tr>
</table>
</form>
My problem is that whenever i query english text from the table I get the results. But when i query the unicode data i do not get the results. The query works perfectly fine in phpmyadmin.
Sample of the query is given below
SELECT * from orders WHERE App_Name LIKE '%काशी%';
This query works perfectly fine when run in phpmyadmin where i get the results. But it does returns 0 records when run through a Php Page.
My code is constructing a dynamic query passed on from previous page which can have 13 search parameters.The code is given below
$mysqli_link= new mysqli("localhost", "root", "", "revenue");
mysqli_set_charset( $mysqli_link, 'utf8');
if($_POST['id_sflag']=='1')
{
// define the list of fields
$fields = array('CaseNO', 'Yr', 'CaseType', 'Court', 'Dt_Disposal', 'App_Name', 'Res_Name', 'Pet_Cou_Name', 'Res_Cou_Name', 'District', 'Pro_Off_Name', 'Division', 'Tehsil');
$conditions = array();
}
// builds the query
$query = "SELECT * from orders";
// loop through the defined fields
foreach($fields as $field){
// if the field is set and not empty
if(isset($_POST[$field]) && !empty($_POST[$field])) {
// create a new condition while escaping the value inputed by the user (SQL Injection)
$conditions[] = "$field LIKE '%" . mysqli_real_escape_string($mysqli_link, $_POST[$field]) . "%'";
}
}
// if there are conditions defined
if(count($conditions) > 0) {
// append the conditions
$query .= " WHERE " . implode (' AND ', $conditions); // you can change to 'OR', but I suggest to apply the filters cumulative
}
echo "$query<br>";
$result = mysqli_query($mysqli_link, $query);
//var_dump($result);
$num_rows=mysqli_num_rows($result);
mysqli_close($mysqli_link);
The retrieval code is :
while($row = mysqli_fetch_array($result))
{
$dis=$row['District'];
$div=$row['Division'];
$teh=$row['Tehsil'];
$cnt1=$row['Page_Cnt'];
$dyear=$row['Dis_year'];
$filen=$row['CCY'];
$newfile=$string = str_replace("/","_",$filen);
$newfile1="judgements/"."$dyear"."/"."$newfile".".pdf";
$Appname=$row['App_Name'];
$resname=$row['Res_Name'];
$disposal=$row['Dt_Disposal'];
$officer=$row['Pro_Off_Name'];
$pcnt=$row['Page_Cnt'];
printf("
<tr>
<td class=\"sub6\">%s</td>
<td class=\"sub6\">%s</td>
<td class=\"sub6\">%s</td>
<td class=\"sub6\">%s</td>
<td class=\"sub6\">%s</td>
<td class=\"sub6\">%s</td>
<td class=\"sub6\">%s / %s / %s</td>
<td class=\"sub6\">%s</td>
<td class=\"sub6\"><a href=\"%s\">View Order</a></td>
</tr>\n",$i,$filen,$Appname,$resname,$disposal,$officer,$dis,$div,$teh,$pcnt,$newfile1);
$tot=$tot+$cnt1;
$i=$i+1;
}
There is no problem is the query but somehow it does not fetch the records for the unicode data stored in the database.
I have tried every possible code but I am not able to query the unicode field values.
Any suggestions are welcome.