Member Avatar for ade92uk

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 because: n/a
  • 2 Contributors
  • 2 Replies
  • 75 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by ade92uk
Member Avatar for rch1231

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.

Member Avatar for ade92uk

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.