-2

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 is enclosed herewith. Table Data Structure
The data stored in the table can be viewed from this picture. Table Data View

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>&nbsp;</td>
          <td><input name="id_sflag" type="hidden" id="id_sflag" value="1"></td>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td align="right"><label>
            <input type="submit" name="button5" id="button7" value="Submit">
          </label></td>
          <td>&nbsp;</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.

Any suggestions are welcome.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • The query which i have pasted here is the one which has been constructed. Observe the echo $query tag. I have echoed the query to check it and have pasted it here. – Amit Sharma Mar 30 '18 at 09:05
  • You must create a minimal complete verifiable example. A code that creates a table, inserts a value and then tries to select it back - so anyone could run it and confirm your problem – Your Common Sense Mar 30 '18 at 09:22
  • where i can send the sample code to you. It will have the complete code, the table structure and data along with the pages. – Amit Sharma Mar 30 '18 at 10:36
  • It should be a Minimal example. https://stackoverflow.com/help/mcve – Your Common Sense Mar 30 '18 at 11:02
  • The code example can be downloaded by clicking this link http://apply-online.co.in/minimal_example.rar You can recreate the scenario. Whenever you search English parameters the query executes. It does not work whenever the unicode field is searched. – Amit Sharma Mar 30 '18 at 11:23
  • Not meant to offend you. I am a newbee to this site. Could not find a way to upload it. Now I have edited the entire question with all relevant data structure and dumps, the search form and the result page. Requesting your help. – Amit Sharma Mar 30 '18 at 12:15

1 Answers1

0

I have found out the answer. The answer was not given on this site though.

I was missing out the following statement on both the form page and the result page

header('Content-type: text/html; charset=UTF-8');

I added this line and it worked like a charm.

  • Current browsers _usually_ default to that. – Rick James Mar 31 '18 at 22:46
  • Yes they do. Infact my code already had this – Amit Sharma Apr 03 '18 at 07:36
  • Perhaps the `` needed a `charset=UTF-8` attribute: `
    `, as mentioned in https://stackoverflow.com/questions/38363566/trouble-with-utf-8-characters-what-i-see-is-not-what-i-stored
    – Rick James Apr 03 '18 at 13:03