PHP MySQL select all matching rows and echo another table's rows

Question

I have 2 table message and answer. where message id = answer id. I want select using message. when message = hi then expected answer hello, hey.

Message Table:
|  id  |  message |
|   1  |    hi    |
|   2  |    hi    |
|   3  |    Hi    |

and answer table:

| id | answer |
| 1  | hello  |
| 2  | hey    |
| 3  | Hello  |

I already tried: $inp = "hi";

SELECT * FROM message JOIN answer ON message.message = answer.answer WHERE message='$inp'"

Please share your latest code attempt; this would help us in pointing you to right direction. — Madhur Bhaiya, Nov 23 '18 at 05:18
```SELECT * FROM answer JOIN message ON message.id= answer.id WHERE message='$inp'" ``` Any luck with this. — Prashant Deshmukh....., Nov 23 '18 at 05:25
Always join using the appropriate relationships. As specified in previous comment, you should using `id` not `message/answer` — Madhur Bhaiya, Nov 23 '18 at 05:28
Also, Your code is open to [SQL injection](https://stackoverflow.com/q/332365/2469308) related attacks. Please learn to use [Prepared Statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Madhur Bhaiya, Nov 23 '18 at 05:28

score 3 · Accepted Answer · answered Nov 23 '18 at 06:48

3

Try this code :

"SELECT * FROM message JOIN answer ON message.id= answer.id WHERE message.message='".$inp."'";

answered Nov 23 '18 at 06:48

Bhoomi Patel

Akhilesh · Answer 2 · 2018-11-23T06:47:50.740

use common b/w each table in your case id so query should be..

SELECT * FROM message JOIN answer ON answer.id = message.id WHERE message = 'hi';

rest in php you should use different ways to run query like prepared statements in order to avoid sql injection or concatenate the variables

like

'SELECT * FROM message JOIN answer ON answer.id = message.id WHERE message = "'.$input.'"';

or mysqli_prepare() bind_params()

or learn using PDO which is good to avoid sql injections as well as supports multiple db drivers which make your work more dynamic and flexible.

2 Answers2