...

View Full Version : How to make exact search?



may_bailey
09-01-2009, 11:23 AM
Hi all,

I have been working on creating a dictionary search for a self project. Let me explain my problem with example:

step 1- I insert a word and its meaning into phpmyadmin = to fall --> dusmek, yikilmak, cokmek, dokulmek

step 2- I make the search. I write the word "to fall" and the result for Turkish = dusmek, yikilmak, cokmek, dokulmek
it shows like this :
SEARCH BOX and under search box
ENG word equal (=) TURKISH EXPLANATION

but when I do my search from Turkish to English with a word among you see above, for example let me search "yikilmak"; it shows like

dusmek, yikilmak, cokmek, dokulmek = to fall

As you see, it doesnt pick only "yikilmak" from the explanations. it desnt do yikilmak = to fall. It shows everything of it.

How can I solve that?

My php codes are below:



<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-9">
<title>Turkish Italian - Italian Turkis dictionary</title>
</head>

<BODY
BGCOLOR="#2a2a2a"
TEXT="#000000"
LINK="#0000FF"
VLINK="#FF66FF"
ALINK="#FF0000"
>
<br><br><br><br>
<center>
<p><font face="verdana" size="2" color="#ffffff"> <b>Turkish - Italian // Italian Turkish </b> </p>
<form method="post" action="show_result.php">
<input type="text" name="search" size=45 maxlength=45>
<input type="Submit" name="Ara" value="Search">
</form>

<br><br><br><br>


<center>

<?
//MySQL Connection.
mysql_connect("localhost","my_username","my_password");
//Database name.
mysql_select_db("my_db_name");


if(isset($_POST["search"])) //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< EDIT
{
$search = $_POST["search"];
if(!empty($search))
{
$result = $result = mysql_query("SELECT * FROM dictionary WHERE tr LIKE '%$search%' OR it LIKE '%$search%'")or die(mysql_error());
if(mysql_num_rows($result)!=0)
{
while($r=mysql_fetch_array($result))
{
$tr=$r["tr"];
$it=$r["it"];
$id=$r["id"];
//the result goes here.
if ($search==$it)
{
echo "$it = $tr";
}
else
{
echo "$tr = $it";
}
echo "<a href=".$url.">".$url."</a><br>";
}
}
else
{
echo "No result for that search.";
}
}
else
{
echo "Please type any word to look for.";
}
}

?>
<br><br>
<table width="800px" border="1" cellspacing="0" cellpadding="2">
<tr>
<th bgcolor="#2a2a2a" width=5%>
<font face="verdana" size="2" color="#ffffff">

<br><br>



</center>
</font>
</th>
</tr>
</table>

</body>
</html>

Phil Jackson
09-01-2009, 11:37 AM
i believe your problem lays here %$search%

% being wild card - currently matches any string with $search in it.

%$search - would match anything search at the end.
$search% - would match anything with $search at the begining

may_bailey
09-01-2009, 11:49 AM
Hi Phil Jackson,
first of all thanx for your advice;

I inserted your advice into
("SELECT * FROM dictionary WHERE tr LIKE '%$search%' OR it LIKE '%$search%'")

between the ' ' (I think no error here) but I couldnt get any result from my turkish to english search.

SKDevelopment
09-01-2009, 12:11 PM
1) You have 2 fields:
1st contains "to fall"
2nd cotains the comma separated values "dusmek, yikilmak, cokmek, dokulmek"

If on selection it outputs

dusmek, yikilmak, cokmek, dokulmek = to fall

it is not the wrong query. If you output $_POST["search"] on the left instead of the field extracted from the DB, I think it should show correctly.

2) Do not use $_POST["search"] in your query directly. Or someone could attack your script using SQL injection. Escape the value before using in the query. Please see mysql_real_escape_string() (http://php.net/mysql_real_escape_string) for reference.

3) If this is English to Turkish and Turkish to English dictionalry, I think it is badly planned. Probably you need many-to-many relationship... 3 tables:

english
-------
english_id
english_word

turkish
-------
turkish_id
turkish_word

english2turkish
--------------
english_id
turkish_id

may_bailey
09-01-2009, 01:38 PM
Hi SKDevelopment,
great help, thanks a lot.
But as a newbie on PHP I couldnt get much $_POST["search"] thing. But I got the value = query thing. It is dangerous ;) right?

And my question for you.

If I create 3 tables like you said. How can I match an English word with a Turkish word which belongs to another table?

SKDevelopment
09-01-2009, 02:00 PM
Using $_POST["search"] directly in the query is dangerous. This is right. The same about using variables from array $_GET, $_COOKIE or any other potential user input. Any such variables must be escaped/validated. For strings mysql_real_escape_string() (http://php.net/mysql_real_escape_string) is normally used for escaping. If you are sure some variable should contain integer or float value you could convert to integer or float with intval() (http://php.net/intval) or floatval() (http://php.net/floatval) correspondingly. Using any input which could be faked by a hacker to run an SQL injection attack on your script is very dangerous. You could end up by someone stealing some your (or your clients) sensitive data or e.g. erasing all the data from your database.

In your case escaping $_POST["search"] should be something like this:


$search = trim($_POST["search"]);
if(get_magic_quotes_gpc())
{
$search = stripcslashes($search);
}
$search = mysql_real_escape_string($search);


As to the query, you would need to use joins. It would be something like this:


SELECT english_word
FROM english AS e
INNER
JOIN english2turkish AS e2t
ON e.english_id=e2t.english_id
INNER
JOIN turkish AS t
ON t.turkish_id=e2t.turkish_id
WHERE turkish_word='$search'

This query would select all English words corresponding to the Turkish word $search.

SKDevelopment
09-01-2009, 02:26 PM
With your original table, it is fine for English to Turkish dictionary. For Turkish to English it is not really suitable from my point of view.

You would need to either follow the DB design I have suggested or use 2 separate dictionaries. One would be English to Turkish and the other would be Turkish to English. Each one would have its own table for translation.

The decision which strategy to follow is completely up to you. I could only point out some possible ways.

Also please notice that keeping comma-separated words in a table field often creates problems like this. Because by doing this you are violating the 1st Normal Form. Which says that no arrays or similar data should be kept in a table cell.

This does not mean you should always keep your database normalized. Sometimes people intentionally create tables which are not normalized but speed up searches. With multiple site visitors it could be important.

The 2 paragraphs above do not give any particular advice. I would only advise you to read some articles on database normalization (I would Google for it) before continuing to plan the system. Even if you do not use this right now, it would be definitely useful reading.

may_bailey
09-01-2009, 03:18 PM
Hi again and thnx a lot.

I tried your codes that you gave me and there was no error on the page. But on the other hand, there was no searching result =(

I always got the same result saying : No record for that search.

SKDevelopment
09-01-2009, 03:49 PM
CREATE TABLE english (
english_id INT NOT NULL,
english_word VARCHAR(255) NOT NULL,
PRIMARY KEY (english_id),
UNIQUE KEY english_word (english_word)
);

CREATE TABLE turkish (
turkish_id INT NOT NULL,
turkish_word VARCHAR(255) NOT NULL,
PRIMARY KEY (turkish_id),
UNIQUE KEY turkish_word (turkish_word)
);

CREATE TABLE english2turkish (
english_id INT NOT NULL,
turkish_id INT NOT NULL,
PRIMARY KEY (english_id,turkish_id)
);

INSERT INTO english VALUES (1,'fall');

INSERT INTO turkish VALUES (1,'dusmek');
INSERT INTO turkish VALUES (2,'yikilmak');
INSERT INTO turkish VALUES (3,'cokmek');
INSERT INTO turkish VALUES (4,'dokulmek ');

INSERT INTO english2turkish VALUES (1,1);
INSERT INTO english2turkish VALUES (1,2);
INSERT INTO english2turkish VALUES (1,3);
INSERT INTO english2turkish VALUES (1,4);

SELECT english_word
FROM english AS e
INNER
JOIN english2turkish AS e2t
ON e.english_id=e2t.english_id
INNER
JOIN turkish AS t
ON t.turkish_id=e2t.turkish_id
WHERE turkish_word='dusmek';

Result: "fall".

may_bailey
09-01-2009, 08:47 PM
I did what you said but couldnt get any result =(

Whatelse maybe its my inexperience but no result.

Dont worry about that. Anyway It would be difficult to insert words firstly to English then to Turkish part and then match them with each other.

All I can say is just thanks.

SKDevelopment
09-02-2009, 08:10 AM
You a welcome.

Does not work ? It is really strange. I have made copy/paste of this code to HediSQL (http://www.heidisql.com) right from this forum. And it returned "fall" just fine ...

As to the dictionaries, yes, probably you would need to keep 2 separate dictionaries after all, if this structure is inconvenient.

may_bailey
09-02-2009, 10:46 AM
Yes SK, unfortunaley couldnt get any result.

I want to ask another thing: If I insert all the Turkish explanations one by one for one english word then it will display all explanations as a list like many dictionary websites. But how many items (or rows) can store phpmyadmin? 50.000 ? or more??

I will insert like:

fall --> dusmek
fall --> yikilmak
fall --> cokmek
fall --> dokulmek

so it wil display like this ; )

SKDevelopment
09-02-2009, 12:28 PM
For the answer please see the MySQL Manual here (http://dev.mysql.com/doc/refman/5.0/en/full-table.html). It is operating system dependent.

The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

may_bailey
09-03-2009, 10:55 AM
Thnx for your deep informations SK,

Now with your help, my php code is that you see below. I want to ask if it seems sth secure or has some vulnerable points? If yes how can I prevent them?

regards




<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-9">
<title>Turkish - Italian Online dictionary</title>
</head>

<BODY
BGCOLOR="#2a2a2a"
TEXT="#000000"
LINK="#0000FF"
VLINK="#FF66FF"
ALINK="#FF0000"
>
<br><br><br><br>
<center>
<p><font face="verdana" size="2" color="#ffffff"> <b>Turkish - Italian // Italian Turkish </b> </p>
<form method="post" action="ara_result.php">
<input type="text" name="search" size=45 maxlength=45>
<input type="Submit" name="Ara" value="Ara">
</form>

<br><br><br><br>


<center>


<?
//MySQL Connected.
mysql_connect("localhost","my_username","my_password");
//Database name.
mysql_select_db("my_db_name");


$search = trim($_POST["search"]);
if(get_magic_quotes_gpc())
{
$search = stripcslashes($search);
}
$search = mysql_real_escape_string($search);
if(!empty($search))
{
$result = $result = mysql_query("SELECT * FROM dictionary WHERE it LIKE '$search' OR tr LIKE '$search'") or die(mysql_error());
if(mysql_num_rows($result)!=0)
{
while($r=mysql_fetch_array($result))
{
$tr=$r["tr"];
$it=$r["it"];
$id=$r["id"];
//the result goes here.
if ($search==$it)
{
echo "$it = $tr";
}
else
{
echo "$tr = $it";
}
echo "<a href=".$url.">".$url."</a><br>";
}
}
else
{
echo "No result for that search<br> Or you mispelled.";
}
}
else
{
echo "Please type a word to look for.";
}


?>


<br><br>
<table width="800px" border="1" cellspacing="0" cellpadding="2">
<tr>
<th bgcolor="#2a2a2a" width=5%>
<font face="verdana" size="2" color="#ffffff">

//My information area.

<br><br>



</center>
</font>
</th>
</tr>
</table>

</body>
</html>

SKDevelopment
09-03-2009, 05:06 PM
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-9">
<title>Turkish - Italian Online dictionary</title>
</head>

<BODY
BGCOLOR="#2a2a2a"
TEXT="#000000"
LINK="#0000FF"
VLINK="#FF66FF"
ALINK="#FF0000"
>
<br><br><br><br>
<center>
<p><font face="verdana" size="2" color="#ffffff"> <b>Turkish - Italian // Italian Turkish </b> </p>
<form method="post" action="ara_result.php">
<input type="text" name="search" size=45 maxlength=45>
<input type="Submit" name="Ara" value="Ara">
</form>

<br><br><br><br>


<center>


<?
//MySQL Connected.
mysql_connect("localhost","my_username","my_password");
//Database name.
mysql_select_db("my_db_name");


$search = trim($_POST["search"]);
if(get_magic_quotes_gpc())
{
$search = stripcslashes($search);
}
$search = mysql_real_escape_string($search);
if(!empty($search))
{
$result = $result = mysql_query("SELECT * FROM dictionary WHERE it LIKE '$search' OR tr LIKE '$search'") or die(mysql_error());
if(mysql_num_rows($result)!=0)
{
while($r=mysql_fetch_array($result))
{
$tr=$r["tr"];
$it=$r["it"];
$id=$r["id"];
//the result goes here.
if ($search==$it)
{
echo "$it = $tr";
}
else
{
echo "$tr = $it";
}
echo "<a href=".$url.">".$url."</a><br>";
}
}
else
{
echo "No result for that search<br> Or you mispelled.";
}
}
else
{
echo "Please type a word to look for.";
}


?>


<br><br>
<table width="800px" border="1" cellspacing="0" cellpadding="2">
<tr>
<th bgcolor="#2a2a2a" width=5%>
<font face="verdana" size="2" color="#ffffff">

//My information area.

<br><br>



</center>
</font>
</th>
</tr>
</table>

</body>
</html>


In this snippet of code personally I fail to see really big security problems.

I see that the variable $url is not initialized anywhere. It means if set globals is turned on, it is quite possible to attack the script sending $url e.g. by GET, POST os some other methods. You must initialize $url. Otherwise in your case XSS attack is quite possible.

Of course there could be some small recommendations. E.g. it is usually recommended not to use the "evil start" (I mean "*") in the queries and list all the fields you need explicitly.

If I do not see more security problems, it does not mean they could not be there of course. They could be. It is quite possible that it is me who simply fails to see them.

From the general web development recommendations, you could use the line


$result = mysql_query("SELECT * FROM dictionary WHERE it LIKE '$search' OR tr LIKE '$search'") or die(mysql_error());

instead of


$result = $result = mysql_query("SELECT * FROM dictionary WHERE it LIKE '$search' OR tr LIKE '$search'") or die(mysql_error());

$result = $result is probably a typo. It is redundant of course.

Generally I would advise to make it a habit to surround all attributes with quotes, e.g. size="45" instead of size=45. Or you could have a problem when one day you decide to to change HTML at your site to XHTML.

I would also advise to use lower case for HTML tags and attribute names. In HTML probably it does not really matter, but in XHTML lower case for tag and attribute names (I mean names, values could be in any case) is is used.

You could browse through very nice tutorials on HTML and XHTML at http://w3schools.com.

Also I repeat, it is possible that I fail to see more general or security problems. They could be there of course.

may_bailey
09-03-2009, 08:39 PM
thanks for your precious advices.

I think the variable $url you said is that line


}
echo "<a href=".$url.">".$url."</a><br>";
}
}


I use this because it makes my result to be seem as a list. How can I do it in another way??



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum