0

Hi, this is what i've got so far:

$sql = "SELECT * FROM ".$_SESSION[dbprefix]."users, user_skills WHERE user_skills.userid=users.id AND ((users.username LIKE '%" . $queryString . "%') OR (users.email LIKE '%" . $queryString . "%') OR (users.firstname LIKE '%" .$queryString . "%') OR (users.lastname LIKE '%" . $queryString . "%') OR (user_skills.skill LIKE '%" . $queryString . "%')) AND enabled='1'  ORDER BY id DESC LIMIT 3 ELSE ";

This is meant to be a search query and it works fine, the problem is that results without user_skills entries don't come up.

Basically, i have two tables, one called users and another called user_skills. I want to be able to search any of these tables for anything, and it works but only for users with an entry in the skills table also. But users in the users table but no rows in the user_skills table do not show.

I've tried experimenting with MySQL Case, IFNULL, IF. How can i fix this please?

Edited by ade92uk: n/a

2
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by ade92uk
1

Your select is forcing the must be in both tables issue. Try a left join instead.

$sql = "SELECT * 
FROM ".$_SESSION[dbprefix]."users
Left join user_skills ON user_skills.userid=users.id 
WHERE ((users.username LIKE '%" . $queryString . "%') OR (users.email LIKE '%" . $queryString . "%') OR (users.firstname LIKE '%" .$queryString . "%') OR (users.lastname LIKE '%" . $queryString . "%') OR (user_skills.skill LIKE '%" . $queryString . "%')) AND enabled='1'  ORDER BY id DESC LIMIT 3 ELSE ";

and you may need to list the fields from each table that you want to display.

0

Your select is forcing the must be in both tables issue. Try a left join instead.

$sql = "SELECT * 
FROM ".$_SESSION[dbprefix]."users
Left join user_skills ON user_skills.userid=users.id 
WHERE ((users.username LIKE '%" . $queryString . "%') OR (users.email LIKE '%" . $queryString . "%') OR (users.firstname LIKE '%" .$queryString . "%') OR (users.lastname LIKE '%" . $queryString . "%') OR (user_skills.skill LIKE '%" . $queryString . "%')) AND enabled='1'  ORDER BY id DESC LIMIT 3 ELSE ";

and you may need to list the fields from each table that you want to display.

Wonderful, Thanks a lot, it's perfect.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.