queries for a search

[rukshilag]

so there are 2 tables course participant and past participant - both have same fields.
i want to write a query that selects both from course participant and past participant if search option "find all" is called.
i out everypossible combination but it just doesnt get right - pls help

[pritaeas]

Show your query. You can use UNION to combine the results of two tables.

[rukshilag]

Show your query. You can use UNION to combine the results of two tables.

here are my queries - they are giving me a lot of trouble

//all possible input combinations

//name and id combinations

if($q != '') {
	if($id != '') {
		$sql = "select * from course_participant where name LIKE '%$q%' and nic = '$id'";
                $result = mysql_query($sql);
                $num_rows= mysql_num_rows($result);
	}else{
		$sql = "select * from course_participant where name LIKE '%$q%'";
                $result = mysql_query($sql);
                $num_rows= mysql_num_rows($result);
	}
}else {
	if($id != '') {
		$sql = "select * from course_participant where nic = '$id'";
                $result = mysql_query($sql);
                $num_rows= mysql_num_rows($result);
	}else if($sql<0){
		$sql = false;
                $result = mysql_query($sql);
                $num_rows= mysql_num_rows($result);
                echo "No Result Found!";
	}
}

//name and completed successfully
if($cs=='yes'){
     if($q!=''){
       $sql = "select * from past_participant where completion LIKE '%$cs%' and name LIKE '%$q%'";
          $result = mysql_query($sql);
          $num_rows= mysql_num_rows($result);
    }else if($cs=='current'){
       $sql = "select * from course_participant where completion LIKE '%$cs%' and name LIKE '%$q%'";
          $result = mysql_query($sql);
          $num_rows= mysql_num_rows($result);
    }
}


//only completed successfully
if($cs =='yes') {
       $sql = "select * from past_participant WHERE completion='$cs'";
       $result = mysql_query($sql);
       $num_rows= mysql_num_rows($result);
} //if no is selected it should compare with both tables
else if($cs =='no') {
       $sql = "select * from past_participant WHERE completion='$cs'";
       $result = mysql_query($sql);
       $num_rows= mysql_num_rows($result);
}else if($cs=='current') {
       $sql = "select * from course_participant WHERE completion='$cs'";
       $result = mysql_query($sql);
       $num_rows= mysql_num_rows($result);
}

//course and completed successfully
if($course=='CTHE'){
 if($cs=='yes'){
    $sql = "select * from past_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
    }else if($cs=='current'){
  $sql = "select * course_participant WHERE course ~*'$course' and completion='$cs'";
         $result = mysql_query($sql);
        $num_rows= mysql_num_rows($result);
 }else if($cs=='no'){
  $sql = "select * past_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
}
}

if($course=='ATHE'){
 if($cs=='yes'){
    $sql = "select * from past_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
    }else if($cs=='current'){
  $sql = "select * course_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
 }else if($cs=='no'){
  $sql = "select * past_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
}
}


//if only course is selected
if($course=='CTHE') {
$sql = "select * from course_participant,past_participant WHERE past_participant.course='CTHE' or course_participant.course='CTHE'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
    }
if($course=='ATHE') {
$sql = "select * from course_participant,past_participant WHERE past_participant.course='ATHE' or course_participant.course='ATHE'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
    }



//if date of course and course is selected and completion (yes/no/current)
if($cd!=''){
 if($course!=''){
   if($cs=='yes') {
    $sql="select * from past_participant where course_date='$cd'";
      $result = mysql_query($sql);
      $num_rows= mysql_num_rows($result);
    }else if($cs=='no') {
      $sql="select * from course_participant where course_date='$cd' and course LIKE '%$course%'";
      $result = mysql_query($sql);
      $num_rows= mysql_num_rows($result);
     }else if($cs=='current') {
      $sql="select * from course_participant where course_date='$cd' and course LIKE '%$course%'";
      $result = mysql_query($sql);
      $num_rows= mysql_num_rows($result);
    }
   }
  }

[rukshilag]

WHEN I USE UNION I GET THIS
warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\sdc\includes\common.inc(1695) : eval()'d code on line 212.

[pritaeas]

If your tables are indeed exactly the same, you can do something like this:

select * from course_participant where name LIKE '%$q%'
UNION
select * from past_participant where name LIKE '%$q%'

BTW, a lot of your queries are missing FROM...

[rukshilag]

what i mean is - all cthe and athe courses - alllll the records in both tables shud be shown if all is pressed.. how to do that??
and also another preob is this following query

if($course!='blank'){
if($cs=='yes'){
    $sql = "select * FROM past_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
}

else if($cs=='current'){
  $sql = "select * FROM course_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
}
else if($cs=='no'){
  $sql = "select * FROM past_participant WHERE course='$course' and completion='$cs'";
         $result = mysql_query($sql);
         $num_rows= mysql_num_rows($result);
}
}

in this as u can see when i click on a course and press a radio button yes - even the records with no and current come! why is this???

[drjohn]

Have you tried feeding these queries directly into the database, not generating them / choosing them via your php code? You should always start by checking the query itself is correct, then using the php to choose which of your now tried and tested queries to use.

[rukshilag]

u mean use phpmyadmin is it?

it seems that some of the queries are similar to eachother so some dont run. i changed the arrangement n the ones that ddnt work, work n working ones dont..