CODEWITHSUNDEEP
phpmysql
Pinkie
Pinkie

Reputation: 10246

mysql join not working

I have 2 database tables that looks like this

Subscriptions table

member_id    active
5            1 
7            0
8            1

Plans Table

member_id    disk
5            2000
7            5000
8            3000

Ii want to show disk for the currently logged in user where active = 1. So if currently logged in user is has member_id 8 and his active = 1 we will show 3000. We get the currently logged in user by doing $this->EE->session->userdata('member_id')

I have the following SELECT statement, but it's not working. it's throwing 500 internal server error. do you see anything wrong with it. 

$results = $this->EE->db->query("SELECT disk FROM exp_membrr_plans p JOIN exp_membrr_subscriptions s ON p.member_id = s.member_id WHERE p.member_id = $this->EE->session->userdata('member_id') AND s.active=1 LIMIT 1");
$results->row('p.disk'); // the problem seems to be here. If i comment out this line i get no error

Upvotes: 1

Views: 71

Answers (2)

Marc B
Marc B

Reputation: 360652

PHP's parser is kind of stupid and not "greedy". You're embedding a multi-level object call inside a string, which in PHP is a no-no:

... p.member_id = $this->EE->session->userdata('member_id') AND ...

PHP's parser will see that as $this->EE as an object call, followed by some plain text ->session->userdata(..).

To force the whole call hierarchy to be parsed as a single unit, you have to surround it with {}:

... p.member_id = {$this->EE->session->userdata('member_id')} AND ...

Upvotes: 1

ariefbayu
ariefbayu

Reputation: 21979

Change it into something like this:

$results = $this->EE->db->query("SELECT disk FROM exp_membrr_plans p JOIN exp_membrr_subscriptions s ON p.member_id = s.member_id WHERE p.member_id = " . $this->EE->session->userdata('member_id') . " AND s.active=1 LIMIT 1");

Upvotes: 1

Related Questions