0

so i am doing a AJAX search bar. For now i only know how to search for exact words. Example for my table

ID|Skillset|Description|
1|game|i love game
2|photography|i love to take photo
3|game|game is my forte

For now i have this query to search for exact word in the database

PHP

$sql = "SELECT userdatafiles.UserID,Name,Link,Title FROM userdatafiles JOIN users ON userdatafiles.UserID = users.UserID WHERE Skillsets = '$searchBar' GROUP BY UserID ORDER BY RAND()";

So if the user typed game, it will display all skillsets that matches game.

What i want to improve is that, instead of just searching into the Skill set column, i want to search for both Skillset and Description column. Instead of searching for exact matching words, i wan users to be able to type something like 'gam' and it will search in both Skillset & Description as long as there are words that contain 'gam' it will display out, example gam is a part of 'game', or a part of 'i love game'. Any ideas how i can do that?

jquery nerd
  • 45
  • 7

1 Answers1

0

You should use LIKE to compare and CONCAT to select your field search : 

$sql = "SELECT userdatafiles.UserID, Name, Link, Title 
FROM userdatafiles ud JOIN users u ON userdatafiles.UserID = users.UserID 
WHERE concat(ud.Skillsets, ud.description) LIKE '%$searchBar%' 
GROUP BY UserID ORDER BY RAND()";
Bang
  • 919
  • 4
  • 11
  • hey thanks for answering. so far 50% is working, when i use WHERE concat(Skillsets, description) LIKE '%$searchBar%' it doesn't work but if i use WHERE Skillsets LIKE '%$searchBar%' it works. any idea why? – jquery nerd Feb 16 '15 at 11:27
  • That should work... whitch error are you getting when you use concat ? – Bang Feb 16 '15 at 12:32
  • no errors, i found out the problem, i have merged 2 tables, and both table contains 'description', that why the error occurs. Is there any way i can refer to the 'description' on a certain table? – jquery nerd Feb 16 '15 at 17:06
  • Yes by using alias, i'm updating the query – Bang Feb 16 '15 at 22:15
  • hi thanks for helping, can u briefly talk about how alias work? – jquery nerd Feb 17 '15 at 16:10
  • For example : Select u.id, u.name from user u. I create an alias "u" for the table user then I can prefix the attributs "id" and "name" to refer to the table user. However you can prefix with the name of the table without using alias. – Bang Feb 17 '15 at 16:29