Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Feb 2006
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cool inserting more queries.

    this code here works fine. it was coded by someone from another forum. but it only searches on 1 member table. i want to select and print the similar data from the nonmember & non_ind_member table as well. can u help?! the fields for nonmember table are - clientCode,nmCode, nmName, (& relevant contact details), non_ind_member - clientCode, nimCode, nimName, regNo, (& relevant contact details). non_ind_member is also referred to as coporate companies.

    thankyou... can email me at cheryl_cheerful@yahoo.com.sg

    Code:
    <?php 
    
    if (isset($_POST['submit']))
    {
      search_query();
      
    }else{
      search_form();
    } 
    
    function search_form()
    {
    ?>
    	<html> 
    	<style type="text/css"> 
    	<!-- 
    	.style1 { 
    	color: #FF6600; 
    	font-weight: bold; 
    	font-family: Verdana; 
    	} 
    	.style4 {font-family: Arial, Helvetica, sans-serif} 
    	--> 
    	</style> 
    	<bodY> 
    	<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>"> 
    	 <table width="430" border="0" cellpadding="0"> 
    	   <tr> 
    	     <td width="350"><span class="style1"><u>Search</u></span></td> 
    	     <td width="74">&nbsp;</td> 
    	   </tr> 
    	   <tr> 
    	     <td><span class="style4">Member Code: 
    	       <input type="text" name="Code"> 
    	       <br> 
    	     </span></td> 
    	   </tr> 
    	   <tr> 
    	     <td><span class="style4">Member Name: 
    	       <input type="text" name="Name">        
    	       </span></td> 
    	   </tr> 
    	   <tr> 
    	     <td><span class="style4">Organisation Name: 
    	       <input type="text" name="orgName"> 
    	     </span></td> 
    	     
    	   </tr>
    	   <tr><td colspan='2' align='center'><input type="SUBMIT" name='submit' value="Search"></td> </tr>
    	 </table> 
    	</form> 
    	</bodY> 
    	</html> 
    
    <?php
    }//end function 
    function search_query()
    {
    // PHP Search Script 
    include("db.php"); 
    
    // connect to db 
    $db = mysql_connect($dbhost, $dbusername, $dbpassword); 
    mysql_select_db($dbname,$db); 
    
    //initialize vars
    $Code = '';
    $Name = '';
    $orgName = '';
    
    if (!empty($_POST['Code'])) {$Code =$_POST['Code'];    }
    if (!empty($_POST['Name'])) {$Name =$_POST['Name'];    }
    if (!empty($_POST['orgName'])) {$orgName =$_POST['orgName'];    }
    
    if(($Code != '') || ($Name != '') || ($orgName != ''))
    { 
      //build the query with the requested info 
      $search_query = "select m.memCode, m.memName, c.clientName
                       from member as m left outer join  client as c
    				   on m.clientCode = c.clientCode
    				   where 1=1 ";
      				   
      if($Code != ''){    $search_query .= " and m.memCode like '%$Code%' ";  }
      if($Name != ''){    $search_query .= " and m.memName like '%$Name%' ";  }
      if($orgName != ''){    $search_query .= " and c.clientName  like '%$orgName%' ";  }
      
      //run the query
      $search = mysql_query($search_query,$db) or die (mysql_error());
        
      if (mysql_num_rows($search)>0)
      {
      	while(list ($CodeResult, $NameResult, $orgNameResult)=mysql_fetch_array($search))
    	{ 
          echo "<font color= #ff6600><u>Search Results</u>";
    	  echo "<table width=500 border=1 cellpadding=0>"; 
    	  echo "</tr bgcolor=#ff9900>";
    	  echo "</tr>";
    	  echo "<td><b>Member Code</b>";
    	  echo "<td><b>Name</b>";
    	  echo "<td><b>Organisation</b>";
    	  echo "</tr>";
    	  echo "<td> $CodeResult";
    	  echo "<td> $NameResult";
    	  echo "<td> $orgNameResult";
    	  echo "</table>";
    	  echo "<br><br>";
      	  
    	} 
      }else{
        //no results from search
        echo "<font color=red>*No results found matching your criteria of [ member code = $memCode";
    	if (!empty($Code)) { echo ",member code = $Code ";}
    	if (!empty($Name)) { echo ", member name = $Name "; }
    	if (!empty($orgName)) { echo ", org name = $orgName";  }
    	echo "]</font>";
      } 
    
    }else{
      //call the search form again and pop up an alert box to tell the user to choose an option
      search_form();
      die("<script language='javascript'>alert('Enter Member Code at the very least!');</script>");
    }
    }//end function
    ?>

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    welcome here!

    the only part in your code that needs changing, is the query
    $search_query = "select m.memCode, m.memName, c.clientName
    from member as m left outer join client as c
    on m.clientCode = c.clientCode
    where 1=1 ";

    you need to replace it with a union.

    the best sollution would be to merge the 3 tables.
    second best sollution would be to create a view as the union of the three tables and join that view with the client-table
    easiest sollution would be to have a query like this
    PHP Code:
    $search_query "
    (select m.memCode, m.memName, c.clientName
    from member as m left outer join  client as c on m.clientCode = c.clientCode) UNION 
    (select n.memCode, n.memName, c.clientName
    from nonmember as n left outer join  client as c on n.clientCode = c.clientCode) 
    UNION 
    (select ni.memCode, ni.memName, c.clientName
    from non_ind_member as ni left outer join  client as c on ni.clientCode = c.clientCode)"

    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Feb 2006
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i've copied and pasted the first union query and tested it. but i got this error :You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and m.memCode like '%123798%'' at line 1

    123798 was a nonmember code i keyed in. what's the problem with the m.memCode anyway?

    Quote Originally Posted by raf
    welcome here!

    the only part in your code that needs changing, is the query
    $search_query = "select m.memCode, m.memName, c.clientName
    from member as m left outer join client as c
    on m.clientCode = c.clientCode
    where 1=1 ";

    you need to replace it with a union.

    the best sollution would be to merge the 3 tables.
    second best sollution would be to create a view as the union of the three tables and join that view with the client-table
    easiest sollution would be to have a query like this
    PHP Code:
    $search_query "
    (select m.memCode, m.memName, c.clientName
    from member as m left outer join  client as c on m.clientCode = c.clientCode) UNION 
    (select n.memCode, n.memName, c.clientName
    from nonmember as n left outer join  client as c on n.clientCode = c.clientCode) 
    UNION 
    (select ni.memCode, ni.memName, c.clientName
    from non_ind_member as ni left outer join  client as c on ni.clientCode = c.clientCode)"


  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i see.
    i didn't realy read through the code so i didn't see the dynamic building of the where clause.

    you'll need to dynamically build the 3 selects, but it would realy be a lott easier to just merge the 3 tables.

    dynamically building the selects (assuming that ll three tables have the same columnnames
    PHP Code:
    $search_query1 "select m.memCode, m.memName, c.clientName
                       from member as m left outer join  client as c
                       on m.clientCode = c.clientCode
                       where 1=1 "
    ;
    $search_query2 "select n.memCode, n.memName, c.clientName 
    from nonmember as n left outer join  client as c on n.clientCode = c.clientCode
                       where 1=1 "
    ;
    $search_query3 "select ni.memCode, ni.memName, c.clientName 
    from non_ind_member as ni left outer join  client as c on ni.clientCode = c.clientCode
                       where 1=1 "
    ;
                         
      if(
    $Code != ''){    
           
    $search_query1 .= " and m.memCode like '%$Code%' ";  
           
    $search_query2 .= " and n.memCode like '%$Code%' ";
           
    $search_query3 .= " and ni.memCode like '%$Code%' ";  
      }
      
      if(
    $Name != ''){
           
    $search_query1 .= " and m.memName like '%$Name%' ";
           
    $search_query2 .= " and n.memName like '%$Name%' ";
           
    $search_query3 .= " and ni.memName like '%$Name%' ";  
      }
      if(
    $orgName != ''){
           
    $search_query1 .= " and c.clientName  like '%$orgName%' ";
           
    $search_query2 .= " and c.clientName  like '%$orgName%' "
           
    $search_query3 .= " and c.clientName  like '%$orgName%' ";   
      }
                         
    $search_query '(' $search_query1 ') UNION ('$search_query2 ') UNION (' $search_query3 ')'
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    New Coder
    Join Date
    Feb 2006
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts
    it's okie. i think i just got the problem solved. but will keep this set of codes too.. thanks!!!

    Quote Originally Posted by raf
    i see.
    i didn't realy read through the code so i didn't see the dynamic building of the where clause.

    you'll need to dynamically build the 3 selects, but it would realy be a lott easier to just merge the 3 tables.

    dynamically building the selects (assuming that ll three tables have the same columnnames
    PHP Code:
    $search_query1 "select m.memCode, m.memName, c.clientName
                       from member as m left outer join  client as c
                       on m.clientCode = c.clientCode
                       where 1=1 "
    ;
    $search_query2 "select n.memCode, n.memName, c.clientName 
    from nonmember as n left outer join  client as c on n.clientCode = c.clientCode
                       where 1=1 "
    ;
    $search_query3 "select ni.memCode, ni.memName, c.clientName 
    from non_ind_member as ni left outer join  client as c on ni.clientCode = c.clientCode
                       where 1=1 "
    ;
                         
      if(
    $Code != ''){    
           
    $search_query1 .= " and m.memCode like '%$Code%' ";  
           
    $search_query2 .= " and n.memCode like '%$Code%' ";
           
    $search_query3 .= " and ni.memCode like '%$Code%' ";  
      }
      
      if(
    $Name != ''){
           
    $search_query1 .= " and m.memName like '%$Name%' ";
           
    $search_query2 .= " and n.memName like '%$Name%' ";
           
    $search_query3 .= " and ni.memName like '%$Name%' ";  
      }
      if(
    $orgName != ''){
           
    $search_query1 .= " and c.clientName  like '%$orgName%' ";
           
    $search_query2 .= " and c.clientName  like '%$orgName%' "
           
    $search_query3 .= " and c.clientName  like '%$orgName%' ";   
      }
                         
    $search_query '(' $search_query1 ') UNION ('$search_query2 ') UNION (' $search_query3 ')'


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •