Select Command & Join Issue

Question

I have two Tables

1: USERS

user_id | user_state | user_email

2: CONTACTS

contact_id | contact_name | contact_state | contact_email | contact_info

What I want to do is for the select to echo all "CONTACTS" data where the "user_state" (which is the logged in users state) is equal to contact_state.

"SELECT * FROM contacts
INNER JOIN users
ON contact_state = ".$row_userinfo['user_state']."
WHERE contact_state = ".$row_userinfo["user_state"]." 
ORDER BY contact_name $limit");
?>

What I'm having trouble with is its not only showing the data that matches the logged in users state (user_state)

and with the above code (which I'm not to sure if it is correct to work the way I want) is coming up with "Unknown column '...' in 'where clause'"

Hopefully someone can help me.

Check `ON` clause, u have to specify `field name` from db table — Suhel Meman, Apr 17 '13 at 06:04
dont pass parameters directly to the query, it makes it vulnerable to sql injections. read the manual [here](http://php.net/manual/en/security.database.sql-injection.php).. — abhij89, Apr 17 '13 at 06:05

score 1 · Answer 1 · answered Apr 17 '13 at 06:04

1

Try this query..

("SELECT contacts.*
FROM users, contacts
WHERE users.user_state = contacts.contact_state AND user_id = '".$_SESSION['id']."'");

here $_SESSION['id'] is your current users login ID.

answered Apr 17 '13 at 06:04

Sumit Bijvani

8,154
17
50
82

This one I thought would be the best option, but it wont bring any results back. Not sure why though. Just going over my session values and checking if I have missed something. – Rocsha Apr 17 '13 at 07:22
@Rocsha yes, you need to check the session id and then pass it to the query – Sumit Bijvani Apr 17 '13 at 07:32
what do you mean "pass it to the query"? – Rocsha Apr 17 '13 at 07:49
your session id.. pass it to query means, just change my variable `$_SESSION['id']` to yours `user_id = session_id` – Sumit Bijvani Apr 17 '13 at 08:11

score 0 · Answer 2 · answered Apr 17 '13 at 06:02

0

check the condition in JOIN Clause

("SELECT * FROM contacts
INNER JOIN users ON contact_state = user_state
WHERE contact_state = ".$row_userinfo["user_state"]." 
ORDER BY contact_name $limit");

answered Apr 17 '13 at 06:02

Suhel Meman

3,702
1
18
26

Fabio · Answer 3 · 2013-04-17T10:16:01.830

0

just change you ON clauses to

SELECT * FROM `users` a
JOIN `contacts` b
ON a.`user_state` = b.`contact_state`
WHERE a.`user_state` = '".$row_userinfo["user_state"]."' 
ORDER BY b.`contact_name` $limit"

this is because you are comparing two tables and need to declare what fields of two table are chosen for comparing

note you are not indicating whatever you want to order by ASC or DESC so i assume you already stored this in $limit variable

edited Apr 17 '13 at 10:16

answered Apr 17 '13 at 06:02

Fabio

23,183
12
55
64

This answer is simular to one I came up with but as the same with my one it kept echoing the results twice. I did some tests and figured out that if I change the state of other users it echos the results the same amount of times and there are that many users. IE: one user has one state, echos results once, three users have the same state echos the results three times. – Rocsha Apr 17 '13 at 07:03
This is because you need to do in the opposite way,let me fix it – Fabio Apr 17 '13 at 10:13

score 0 · Answer 4 · answered Apr 17 '13 at 06:04

0

Try this

"SELECT c.* FROM contacts c
 JOIN users u
ON c.contact_state = u.user_state
WHERE c.contact_state = ".$row_userinfo["user_state"]." 
ORDER BY c.contact_name $limit"

answered Apr 17 '13 at 06:04

Praveen kalal

2,148
4
19
33

Select Command & Join Issue

4 Answers4