CODEWITHSUNDEEP
phphtmlmysqlunicode
golden_boy615
golden_boy615

Reputation: 394

Can not search arabic in mysql Quran Database with php

I am newbie in web programming so I do apologize if I say something wrong. I am writing a simple php script to search in an Arabic mysql DB (Quran database from this website http://qurandatabase.org/Database.aspx - mysql query file format - Arabic(Original)) but I have a very simple problem, which is what I receive by GET method in my php script is not what I entered to search and something add some unwanted Arabic character to my searched string. my codes and out puts are as follows: my html code is:

 <!DOCTYPE html>
 <html xmlns="http://www.w3.org/1999/xhtml" lang="ar" xml:lang="ar">
     <meta http-equiv="content-type" content="text/html; charset=UTF-8">
     <body>
         <form action="p.php" method="get" accept-charset="UTF-8">
             Word Search: <input type="text" name="word"/>
             <input type="submit"/>
         </form>
     </body>
 </html>

my php code is:

<?php
 header('Content-type: text/html; charset=utf-8');
 $DBServer="localhost";
 $DBUser="root";
 $DBPass="1";
 $DB="Quran";
 $DBTable="Quran";
 //echo "Welcome".$_GET["fname"];
 $Word=$_GET['word'];
 echo $Word;
 echo "<br />";
 echo "<br />";
 $con=mysql_connect($DBServer,$DBUser,$DBPass);
 if(!$con)
 {
     die("DB Connection error: ".mysql_error());
 }
 mysql_select_db($DB,$con);
 echo "<br />\n";

 echo "<br />\n";
 echo "<br />\n";
 echo "<br />\n";
 echo "select * from $DBTable where AyahText like '%".$Word."%'";
 echo "<br />\n";
 $select="select * from $DBTable where AyahText like '%ﻢِﻧَ ﺎﻠْﺠِﻧَّﺓِ ﻭَﺎﻠﻧَّﺎﺳِ%'";
 echo $select;
 echo "<br />\n";
 echo "<br />\n";
 $select="select * from $DBTable where AyahText like '%".urldecode($Word)."%'";
 echo $select;
 echo "<br />";
 $result=mysql_query($select);
 //$result=mysql_query("select * from $DBTable where VerseID=$Word");
 $LastSura=1;
 echo $LastSura;
 echo "<br />";
 while($row=mysql_fetch_array($result))
 {
     echo "salam";
     echo $row['SuraID']."\t".$row['VerseID']."\t".$row['AyahText'];
     echo "<br />";
     if($row['SuraID']!=$LastSura)
     {
         echo "<br />";
         echo "<br />";
     }
     $LastSura=$row['SuraID'];

 }
 mysql_close($con);
 ?>

and I enter this string in my html text box:

مِنَ الْجِنَّةِ وَالنَّاسِ

but I get this string with few difference in output which you have to test it yourself:

مِنَ الْجِنَّةِ وَالنَّاسِ

select * from Quran where AyahText like '%مِنَ الْجِنَّةِ وَالنَّاسِ%'

select * from Quran where AyahText like '%مِنَ الْجِنَّةِ وَالنَّاسِ%'

select * from Quran where AyahText like '%مِنَ الْجِنَّةِ وَالنَّاسِ%' 1

and it cause my search to fail .

I don't know if this copy and paste for questioning keeps my correct output to show you correctly or not but in my browser in الْجِنَّةِ and النَّاسِ up on ن I have unwanted ِ or َ in my output. so much thanks for any help.

Upvotes: 6

Views: 4016

Answers (3)

Joni
Joni

Reputation: 111219

The difference between مِنَ الْجِنَّةِ وَالنَّاسِ and مِنَ الْجِنَّةِ وَالنَّاسِ is that a non-printable character is in a different place. Depending on the collation you are using this makes the strings equal or different in MySQL, affecting the query results:

mysql> select 'مِنَ الْجِنَّةِ وَالنَّاسِ' = 'مِنَ الْجِنَّةِ وَالنَّاسِ' collate utf8_general_ci general;
+---------+
| general |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select 'مِنَ الْجِنَّةِ وَالنَّاسِ' = 'مِنَ الْجِنَّةِ وَالنَّاسِ' collate utf8_unicode_ci unicode;
+---------+
| unicode |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Change the collation defined for the column to get the results that you want, for example:

alter MyTable modify MyColumn text collate utf8_general_ci;

Upvotes: 3

Levent Esen
Levent Esen

Reputation: 579

Here is a sample code that I tested with your words and it printed the suraNo/ayahNo/ayah that contains the word.

<?php
header('Content-type: text/html; charset=utf-8');
$server="localhost";
$user="root";
$pass="root";
$database="quran";
$table="quran";
$word=$_GET['word'];
$connection=mysql_connect($server,$user,$pass);
if(!$connection)
{
 die("DB Connection error: ".mysql_error());
}
mysql_select_db($database,$connection);
mysql_query("SET NAMES utf8"); //IMPORTANT
?>
<!DOCTYPE html>
 <html xmlns="http://www.w3.org/1999/xhtml" lang="ar" xml:lang="ar">
     <meta http-equiv="content-type" content="text/html; charset=UTF-8">
     <body>
         <form method="get" accept-charset="UTF-8">
             Word Search: <input type="text" name="word"/>
             <input type="submit"/>
         </form>
         <div>
         <?php
            if (!empty($word))
            {
                $i = 0;
                $str = "SELECT * FROM $table WHERE AyahText LIKE '%$word%'";
                $query = mysql_query($str);
                while ($row = mysql_fetch_assoc($query))
                {
                    $i++;
                    echo "<br/>$i: ($row[SuraID], $row[VerseID]): $row[AyahText]";
                }
            }
         ?>
         </div>
     </body>
 </html>

You must call this after choosing database. When I removed this line I couldn't get any result.

mysql_query("SET NAMES utf8");

Upvotes: 0

Marc B
Marc B

Reputation: 360562

You do not need to do a urldecode(). PHP has already done that for you when it was populating $_GET as part of its startup sequence.

Note that you are vulnerable to SQL injectiona attacks with your code. A proper search sequence would be:

$word = $_GET['word'];
$safeword = mysql_real_escape_string($word);  
$sql = "SELECT ... WHERE AyahText LIKE '%$safeword%'";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($rresult)) {
   ...
}

Of course, you'll get a ton of comments here from people about not using the mysql library, since it's deprecated.

Upvotes: 0

Related Questions