PHP MySQL select all matching rows ans echo another table 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'"

any suggestions?

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

1

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.

edited Nov 23 '18 at 06:47

answered Nov 23 '18 at 06:42

Akhilesh

thank you for your answer. but my expected output single time 2 results hello and hey. how can do this? any suggestions? – Rabib Nov 23 '18 at 08:58
1

it is giving you two answers for the message 'hi' isn't it ?? so all you need to do is loop through it using php. – Akhilesh Nov 23 '18 at 09:06
haha my code is working. but I forgotten to add while loop. thank you again – Rabib Nov 23 '18 at 10:08

2 Answers2