Mysql query isn't working in laravel

Question

ive been trying to retrive some messages sent by ser from a chat table. and i came up with a query which is working quite well in phpmyadmin.

but when i use the same query in laravel it isn't working.

SELECT user2,message,created_at FROM ( SELECT user2,message,created_at FROM chats WHERE user1 ='Tiffany' UNION SELECT user1,message,created_at FROM chats WHERE user2 ='Tiffany' in ( SELECT DISTINCT user2 FROM chats WHERE user1 ='Tiffany' UNION SELECT distinct user1 FROM chats WHERE user2 ='Tiffany' ) )tb2 WHERE user2 !='Tiffany' GROUP BY user2

ive tried almost everything but could find a solution. please give me a small explanation why the above query isn't working. the fields of my chat table are

id,user1(varchar),user2(varchar),message,created_at(timestamp),updated_at(timestamp)

the whole code ive used

    if ($pmessage = \DB::select('select user2,message,created_at
  from (select user2,message,created_at from chats where user1 ='Tiffany'
  union select user1,message,created_at from chats where user2 ='Tiffany'
  in ( select distinct user2 from chats where user1 ='Tiffany'
  union select distinct user1 from chats where user2 ='Tiffany' ))
  where user2 != 'Tiffany'
  group by user2')) {
      return response()->json(['pmessage' => $pmessage, 'status' => 200], 200);
  } else {
      return response()->json(['status' => 505], 505);
  }

Are you using DB:: or Eloquent? Post your code please.

Alexey Mezenin
– Alexey Mezenin

2016-02-27 18:16:07 +00:00
Commented Feb 27, 2016 at 18:16 — Alexey Mezenin
– Alexey Mezenin, Commented Feb 27, 2016 at 18:16
@AlexeyMezenin im using DB:: check the edited version

TRomesh
– TRomesh

2016-02-27 18:25:05 +00:00
Commented Feb 27, 2016 at 18:25 — TRomesh
– TRomesh, Commented Feb 27, 2016 at 18:25

miln · Accepted Answer · 2016-02-28 19:11:31Z

4

I think you need to use DB::raw(). Something like:

$pmessage = \DB::select(\DB::raw("SELECT ..."))

Also, you need to mix your quotes. So use double-quotes around your query, so that you can use single-quotes within your query. So you should be using something like this:

$pmessage = \DB::select( \DB::raw("Select user2,message,created_at
from (select user2,message,created_at from chats where user1 ='Tiffany'
union select user1,message,created_at from chats where user2 ='Tiffany'
in ( select distinct user2 from chats where user1 ='Tiffany'
union select distinct user1 from chats where user2 ='Tiffany' ))
where user2 != 'Tiffany'
group by user2')"));

edited Feb 28, 2016 at 19:11

answered Feb 27, 2016 at 18:58

miln

1356 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TRomesh Over a year ago

i did that but its still not working ive used postman to check it and its giving an error

miln Over a year ago

Can you edit your question with your current code? Maybe include the error you are getting from postman?

TRomesh Over a year ago

ive its already edited and the error im getting on postman is Status 500 Internal Server Error 500 Internal Server Error

Vahid Alvandi · Accepted Answer · 2016-02-27 18:30:02Z

1

i root folder you have file .env after open with notepad you shold have command like this

**

APP_ENV=local
APP_DEBUG=true
APP_KEY=VTRcD5ZoIabuY9QR5Ih1QeCTTgCHQZrR

DB_HOST=localhost
DB_DATABASE=angulara
DB_USERNAME=root
DB_PASSWORD=

CACHE_DRIVER=file
SESSION_DRIVER=file
QUEUE_DRIVER=sync

REDIS_HOST=localhost
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_DRIVER=smtp
MAIL_HOST=mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null

also you must corect config DB in config/database.php

answered Feb 27, 2016 at 18:30

Vahid Alvandi

61210 silver badges18 bronze badges

1 Comment

TRomesh Over a year ago

there's nothing wrong with the configurations ive just typed a simple select query and it works. so im pretty sure that configurations are done properly.

Collectives™ on Stack Overflow

Mysql query isn't working in laravel

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related