...

View Full Version : inserting more queries.



cheryl
02-13-2006, 12:59 PM
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



<?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
?>

raf
02-13-2006, 01:20 PM
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


$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)";

cheryl
02-13-2006, 01:36 PM
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?


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


$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)";

raf
02-13-2006, 02:03 PM
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


$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 . ')';

cheryl
02-13-2006, 02:07 PM
it's okie. i think i just got the problem solved. but will keep this set of codes too.. thanks!!!


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


$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 . ')';



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum