Array not returning values it should - 'OR logic' issue?

[Jool]

Hi all, many thanks for reading this post.

I have a basic php search form which returns results on a separate page. The results are in the form of language teachers. The search fields or parameters are Nationality, Teaching Language, Price, Type of Lesson Offered and Location.

The Type of Lesson Offered search allows users to search for online teachers, face to face teachers, and also teachers who offer both services. The search works so that if a user selects online teachers, they are presented in a results page with teachers who are listed as online teachers or teachers offering both services. The code I used for this is

     if ($lesson_type == 'f2f'){
    	 $whereClause[] = 'lesson_type = "f2f" OR lesson_type = "mix"';
    	}
    	else  if ($lesson_type == "online"){
    	 $whereClause[] = 'lesson_type = "online" OR lesson_type = "mix"';
    	}
    	elseif ($lesson_type == "mix"){
    	 $whereClause[] = 'lesson_type = "mix"';
    	}

The issue is with the selection of online teachers or Face to Face (f2f) teachers. When no other fields in the search box are selected, the search returns what I would expect it to return. However, if I use any other field (i.e. language) and select f2f or online as the the lesson type, the array seems to ignore the fields entered (which are not lesson_type). I assume it something to do with the OR statement impacting the returning value.

So to try to explain by example.

I created an array called $whereClause

To see what the array was returning I used…
PRINT_R($whereClause);

I submitted a search for French Online teachers (It should return French teachers who teach online lessons and French teachers who teach both online and face to face lessons - i.e. their record in the db table under lesson type would list ‘mix’ - i.e. both.

The following was displayed ->

Array ( [0] =>language = “French”[1] => lesson_type=“online” or lesson_type=“mix”[2] => active “1”)

However the Array didn’t filter according to language.

When I tried to search by teachers who offered both (mix) of online and face to face lessons, the expected results where returned.

Array ( [0] =>language = “French”[1] => lesson_type=“mix”[2] => active “1”)

From testing various searches, I’m assuming the OR statement is causing some issues.

Any ideas what I can do to resolve this problem? Any help much appreciated.

Thanks

Adam

[phdr]

If you want the language choice AND the lesson type, you should be ANDing the entries from the $whereClause array when you build the query and/or you need to add () around the multiple ORed expressions in one where clause to force operator precedence, or see the following point -

You can simplify the f2f/mix and online/mix logic by using an IN() comparison - lesson_type IN(‘f2f’,‘mix’). Similar for ‘online’,‘mix’

[Jool]

Hi phdr.

Many thanks for your reply. Sorry, I was using AND further down in the code but I didn’t post. So I create my array calling it whereclause

$whereClause = [];

Then set the values as summarised in my first post and then use ADD when I’m building the query:

$sql = " SELECT * FROM teachers_table WHERE ". join(' AND ', $whereClause) ." LIMIT $offset, $no_of_records_per_page ";

This works if I’m asking for for say language French and lesson_type mix to be returned. However it’s the OR statement which seems to be causing an issue. So, as you suggested I tried:

1. Adding the () around the multiple OR expression - I amended code as follows:

    else  if ($lesson_type == "online"){
    	 $whereClause[] = '(lesson_type = "online") OR (lesson_type = "mix")';
   

This didn’t seem to have any effect. I also tried
$whereClause[] = '(lesson_type = "online") OR lesson_type = "mix"';

but again, this didn’t seem to change anything (French results still not the only ones being displayed).

2. I tried the IN()comparison but not sure I am applying correctly. I tried
   $whereClause[] = 'lesson_type IN(‘f2f’,‘mix’)';

But this resulted in no values being returned…

I’m suspecting I haven’t carried out as you intended?
Many thanks.
Adam

[phdr]

around the multiple ORed expressions in one where clause

$whereClause[] = '(lesson_type = "online" OR lesson_type = "mix")';

I tried the IN()comparison but not sure I am applying correctly

Your posted trial contains smart/curly quotes, probably from copying what I wrote. This forum software beautifies the output. Be careful when using anything you copy. Delete and retype the quotes. You also cannot put (un-escaped) single-quotes inside of a single-quoted string.

[Jool]

Hi phdr,

Both of these solution now work for me.

Thanks so much for your time and help.

Because of your response, I will now have a more relaxed Saturday evening than otherwise.

Hope you have a great Saturday and brilliant weekend.

Adam