0

I am new to PHP. I have done the following and it works, but I just think there has to be a better way of doing this and what I have done is very dirty hack.

So, I have a dictionary, with each word having a reference corresponding another word in another table. I want the Ar word searched for to search through the AR table to see if there is a match. If so, then return the Reference of that word, then go to the EN table and search if that reference exists there. Then return all the words with that reference number. The following does the job, but I was wondering if there is a better way of doing it. 

<?php 
    if (!empty($_POST)){
    $word = $_POST["word"];
        $result = mysql_query("SELECT ref FROM en_a_ar WHERE ar = '$word'");
            while($row = mysql_fetch_array($result)) {
              $searchRef = $row['ref'];
              $searchResult = mysql_query("SELECT en FROM en_a WHERE ref = '$searchRef'");
              while($row = mysql_fetch_array($searchResult)) {
              echo  $row['en'];
              echo "<br>";
              }

            } 
        }
    ?>
Hemn Baker
  • 31
  • 3
  • Please, [don't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. – Jay Blanchard Oct 01 '14 at 19:51
  • 2
    You are vulnerable to [sql injection attacks](http://bobby-tables.com), so yes, there IS a better way to do this. And you're running your inner query based on the results of the outer query - this is almost always a bad design - a single `JOIN`ed query could this. – Marc B Oct 01 '14 at 19:53

1 Answers1

1

Use an INNER JOIN

SELECT
  en_a.en
FROM
  en_a
JOIN
  en_a_ar
ON
  en_a_ar.ref = en_a.ref
WHERE
  en_a_ar.ar = '$word'

As others have said, you need to sanitize your input $word since it comes from the client and creates an SQL Injection Vulnerability.

Preston S
  • 2,751
  • 24
  • 37
  • Thank you very much, this is exactly what I needed. I need to learn more about the INNER JOIN. Very much appreciated. – Hemn Baker Oct 01 '14 at 20:05