Hello,

Need kind of guidence/ help relating to the mentioned topic.

I got three tables , which are interlinked through foreign key a single id from table one.
All are needed to be searched through.
The solution I implemented were :

1) Separatly searched the table and used union All to display the result all together but error occured, even changed selected item instead of *.
" The used SELECT statements have a different number of columns "


2) Same error came when applied join on two & two table combination search.

3) Combined all tables through two joins , error occured
"error on limit 30"

Need a guidence about that ,Thank you

Without seeing the queries, it's impossible to say. But if you draw a union query, you'll see what the error message is telling you. The same number of columns have to come out of each select statement for the database to put them together.

my query is


$query = " select * from job_seeker LEFT JOIN jobseeker_skill ON job_seeker.JobSeeker_Id = jobseeker_skill.JobSeeker_Id LEFT JOIN job_seeker_education ON job_seeker.JobSeeker_Id = job_seeker_education.JobSeeker_Id where

(Sex='".$array['sex']."' AND NOT Sex=' ') OR
(Address like '%".$array['location']."%' AND NOT Address=' ') OR
(Linguistics like '%".$array['linguistic']."%' AND NOT Linguistics =' ') OR
(YEAR(DOB) $age_limit '".$year."' AND NOT DOB ='0000-00-00') OR
(Current_Salary $cSalary_limit '".$array['current_salary']."' AND NOT Current_Salary='') OR
(Expected_Salary $salary_limit '".$array['salary']."' AND NOT Expected_Salary='' ) OR
(Position_Type ='".$array['position_type']."' AND NOT Position_Type ='') OR
(Title like '%".$array['education']."%' AND NOT Title ='') OR
(Skill like '%".$array['skill']."%' AND NOT Skill = '' ) ; " ;



Its kind of complicated.
I guess I have to use union because join refer to combining two search,which might not be required as, tables have to be searched separatly.Iam rite?

Imagine 2 squares. Each of them is a table. It could be 20 squares instead, or any number of them. But 2 is an easy example. If you draw those 2 squares, with one on top of another one, that's a union query. If you draw them side by side, with 1 on the left and 1 on the right, that's a join.

Looking through your PHP code, I see a lot of AND NOT X = Y. I suppose that works, but it's confusing! If you're able to make sense of it, more power to you, but many people would prefer using a not equals operator (which could be expressed as != or <> depending on your system). Sometimes it's a good idea to do that, so that if you show this to a PHP+MySQL expert (I'm more of an ASP.NET+SQL Server guy) it will be easier for them to understand, and hence give you good advice.

Why are you saying Sex = "Male" And NOT SEX = "undefined"? (I'm taking a space to mean undefined.) Each of your columns is set up this way, but if one of your columns matches an exact value, that means by definition it isn't blank.

PS - You should really use a stored procedure with input parameters, instead of nasty, multi lingual dynamic SQL. That approach would have avoided this problem all together.

Thanks for guidence.

Adding AND NOT table = "" ensuring that no empty value is returned in search.

But saying AND TABLE = "ABC+XYZ" also makes sure an empty value isn't returned. If you think about it, any value that isn't empty, is not an empty value.

Could you elaborate little bit
AND Table = "ABC +XYZ" ;
In this sense
AND Column = "any input value + what ??? " ;

I meant with empty value ,is that,if user dont enter any value, and like keyword returns even empty value record saved in database.


Thanks