Mysql Select query to get data from three table

37
February 12, 2019, at 2:40 PM

I have three table like below
Table name : word

+----+----------+--------+------------+--------+
| id | word     | letter | origin     |literal |
+----+----------+--------+------------+------------+----+
| 7  | അകക്കരപ്പന്‍ | അ      | അകം1-കരപ്പന്‍ |bc  |
+----+----------+--------+-------------------------+----+
| 8  | അകക്കഴി   | അ      | അകം1-കഴി   |af    |
+----+----------+--------+-----------------------+-----+

Table name: relation

+----+---------+---------------+-------+
| id | id_word | id_definition | rtype |
+----+---------+---------------+-------+
| 6  | 4       | 6             | നാ.   |
+----+---------+---------------+-------+
| 7  | 5       | 7             | നാ.   |
+----+---------+---------------+-------+

Table name: definition

+----+------------+
| id | definition |
+----+------------+
| 1  | ചായ        |
+----+------------+
| 2  | ചായച്ചെടി   |
+----+------------+

I need literal, origin, rtype, definition from these table. I used following query,

SELECT
    literal,
    origin,
    rtype,
    word.id,
    definition,
    id_definition
from definition, relation, word
WHERE
    word = '$wpm' AND
    relation.id_word = word.id AND
    definition.id = id_definition

But don't get ant result. Please give me the correct query.

Answer 1

I see several problems with your query, most notable that you are using the now deprecated old school join syntax, and also that your join conditions are not well defined. I suggest the following:

SELECT
    w.literal,
    w.origin,
    r.rtype,
    w.id,
    d.definition,
    r.id_definition
FROM word w
INNER JOIN relation r
    ON w.id = r.id_word
INNER JOIN definition d
    ON r.id_definition = d.id
WHERE
    w.word = ?;

Note that I leave the value of word in the WHERE clause as a placeholder ?. You should read about prepared statements in PHP, which will explain what this means and how you should handle/implement it in your PHP code.

READ ALSO
How to fetch data from two join tables with same database column names in codeigniter?

How to fetch data from two join tables with same database column names in codeigniter?

There are two tables 'student' and 'parent'both tables have 'f_name' and 'l_name' columns

48
PHP empty session files generated

PHP empty session files generated

I have a site where I use session authorization for my users Details are:

70
Change PDO login script to MySQLi

Change PDO login script to MySQLi

I have a problem because I don't know how I should change the ready-made login script with PDO to MySQLi ? Rest of my website is coded in MySQLiBelow its my config file

45
Yii2's mongodb collection - get all content of nested arrays

Yii2's mongodb collection - get all content of nested arrays

I have a Yii2 mongodb's collection that looks like this:

68