SQL Select statement

[kadriirdak]

Hi There,

I create an SQL statement to retrieve some data from DB. However I can not do the third if statement! my code;

$search = care_query("
																																	SELECT 
																																	contact_number, role, is_active
																																	FROM
																																	contact_roles
																																	WHERE
																																	contact_number='$mn' AND is_active='Y' AND role like 'S%';
	
																																"); 
																																	while($row = care_fetch_array($search))
																																	{
																																		$role= $row['role'];echo "<pre>";
																															
																																	}
																									
																																			if($role =='SAS' || $role =='SBS' || $role =='SPS'){
																																																						$status="student";
																																													
																																												                                	} else if($role !='SAS' || $role !='SBS' || $role !='SPS'){
																																																																							
																																																																									$status="specialist_workers";
																																													
																																																																									} 
																																																																								else	if (($role=='SAS' ||  $role=='SBS' || $role=='SPS') && ($role!='SAS' ||  $role!='SBS' || $role!='SPS' && $role!='') ){
																																																																																		
																																																																																	$status="both";
																																																																										
																																																																																		
																																													
																																		
																								
													}
							
			
if ($status=="student"){
																													// print related form to user and post the data to next page
	
																	echo "student"; 
			}
else if ($status=="specialist_workers"){
																	echo"specialist worker";
																		}

 else if($status=="both"){
																	echo"both";
								        }
						
										}

So basically what I want to do is:

1st select SAS or SBS or SPS only if exist in the row the status is student. -> WORKS
2nd select start with S but not SAS SBS SPS the status is specialist worker. ->WORKS
3rd select (SAS or SBS or SPS) AND (NOT SAS SBS SPS but start with S)the status is both. -> NOT WORKING

I hope I make myself clear enough

Thank You

[diafol]

I can't understand the last condition. How can role be either of SBS/SAS/SPS and not at the same time? How can the status be student AND specialist worker at the same time?

[kadriirdak]

For example i have 3 contact;

contact1; in role table -> SAS or SBS or SPS stored so this is student only

contact2; in role table -> example SPP stored but not these SAS SBS or SPS so this is specialist worker only

contact3; in role table -> SAS and SPP stored so this should be both (student and specialist worker)

How I categorise is;

if role start with S, I need to take it.
if role start with S and equal to SAS SBS or SPS, it is a student else specialist worker AND some contact has two different category values stored..

[diafol]

I didn't realise that the table could hold multiple values for the same person.

THis is really awkward to do in a single SQL (*I think*).

It would set up the table with bit values for your types.

1 = SAS
2 = SBS
4 = SPS
8 = SPP
(etc)

So if a person was an SAS and an SPP, they'd be stored with a value of 9 (1 + 8). Likewise a person who was both SBS and SPP would be stored with a value of 10 (2 + 8).

Then you just check the value with '&' for 1 or 2 or 4.

$int = 1 & $row['role'];

This returns either 0 (false) or the integer being compared (> 0).

This depends on the number of option for role though.

Have a table thus:

bit  role
1    SAS 
2    SBS
4    SPS
8    SPP

etc

These can then be assigned to checkboxes or multiselect dropdown values. When selected or checked - let $_POST add up the values and hey presto you have the bit value describing the various roles, all wrapped into one. The beauty of these is that you can use all sorts of operators like AND, OR, XOR etc.

Here's the manual link: http://php.net/manual/en/language.operators.bitwise.php

But, there are more accessible ones to be found through Google search.

[kadriirdak]

Thank You ardav

This will be a good knowledge for me.

However the project has changed so I just find out if student or not - no stress :)

Thank You for your replay!

[diafol]

Doh! Ok, could you mark this as solved please.