Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Coder
    Join Date
    Aug 2009
    Posts
    25
    Thanks
    4
    Thanked 0 Times in 0 Posts

    How to make exact search?

    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:

    Code:
    <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>

  • #2
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,555
    Thanks
    57
    Thanked 148 Times in 147 Posts
    PHP Code:
    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 

  • #3
    New Coder
    Join Date
    Aug 2009
    Posts
    25
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi Phil Jackson,
    first of all thanx for your advice;

    I inserted your advice into
    Code:
    ("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.

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    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() 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

  • #5
    New Coder
    Join Date
    Aug 2009
    Posts
    25
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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?

  • #6
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    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() 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() or 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:
    PHP Code:
    $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:
    Code:
    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.
    Last edited by SKDevelopment; 09-01-2009 at 02:04 PM.

  • #7
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    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.

  • #8
    New Coder
    Join Date
    Aug 2009
    Posts
    25
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Code:
    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".
    Last edited by SKDevelopment; 09-01-2009 at 03:51 PM. Reason: corrected code a little bit - though it did not affect the functionality

  • Users who have thanked SKDevelopment for this post:

    may_bailey (09-02-2009)

  • #10
    New Coder
    Join Date
    Aug 2009
    Posts
    25
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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.

  • #11
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    You a welcome.

    Does not work ? It is really strange. I have made copy/paste of this code to HediSQL 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.

  • #12
    New Coder
    Join Date
    Aug 2009
    Posts
    25
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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 ; )

  • #13
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    For the answer please see the MySQL Manual here. It is operating system dependent.
    Quote Originally Posted by MySQL Manual
    The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits.

  • #14
    New Coder
    Join Date
    Aug 2009
    Posts
    25
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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

    Code:
    <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>

  • #15
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Quote Originally Posted by may_bailey View Post
    Code:
    <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
    PHP Code:
    $result mysql_query("SELECT * FROM dictionary WHERE it LIKE '$search' OR tr LIKE '$search'") or die(mysql_error()); 
    instead of
    PHP Code:
    $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.
    Last edited by SKDevelopment; 09-03-2009 at 05:08 PM.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •