multiple table search

[cheryl]

i have clients' members tables which are created "dynamically".

for example, i have client ABC and their members table would be ABCm, client XYZ will have XYZm and will go the same for all the other clients members' table. by the way, the fields in all of these tables are the same.

i would like to know how is it possible to do a select statement where i can combine all the clients' members tables to do a search? cuz i do not want/have to add table name if there is a new client.

how how?? can anybody assist?

[shocker-z]

[code]$i=1;
$query=mysql_query("SELECT member_name FROM members");
while ($member=mysql_fetch_array($query)) {
if ($i=1) {
    $tables=($member[member_name].'m');
    $searchfields=($member[member_name]."m.search_field LIKE '$search_item'");
    $i++;
} else {
    $tables=($tables.', '.$member['member_name'].'m');
    $searchfields=($searchfields.'OR '.$member['member_name']."m.search_field LIKE '$search_item');
}
}
$results=mysql_query("SELECT * FROM $tables WHERE $searchfields");
while ($row=mysql_fetch_array($results)) {
echo("$row['fieldname1'], $row['fieldname2'], $row['fieldname3'].....");
}[/code]

I *think* (not tested dont blame me) that may do it it should list all members in your members table and check to see if $search_item (what ever variable your searching for) is in search_field (field name in your table your searching) and then should return all the values which matched the search


Dont know if this is exacly the fastest way in the world but im at work listening to cmr's complaint about SW problems saying they want engineers so this is the way i came up with :)

[cheryl]

hi. could you like explain line by line what the code does? and what are the variables for? i'm a little bit confuse.

oh! i do have a textfield and button to submit the search though.

thank you

[shocker-z]

$i=1;
[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]//Query to select all members from you members table, you will need to change member_name to the field that has all your members names under it and change members to the name of your table with all the members in[!--colorc--][/span][!--/colorc--]
$query=mysql_query("SELECT member_name FROM members");
[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]While there are still users not selected keep going[!--colorc--][/span][!--/colorc--]
while ($member=mysql_fetch_array($query)) {
[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]If this is the first time the loop has gone through then we dont need ther 'OR' or the ',' infront[!--colorc--][/span][!--/colorc--]
if ($i=1) {
$tables=($member[member_name].'m');
$searchfields=($member[member_name]."m.search_field LIKE '$search_item'");
$i++;
}
[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]else we need to add the 'OR' and ','[!--colorc--][/span][!--/colorc--]
else {
[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]basicaly creating a line with ABCm and XYZm and making the query select all the tables[!--colorc--][/span][!--/colorc--]
$tables=($tables.', '.$member['member_name'].'m');
[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]beleow does simular but sets it up like this: ABCm.searchfield LIKE'$search_item... where searchfield is you need to rename that to the field in your table that you want to rename and where $search_item is you need to change to the variable that hold the value of your search[!--colorc--][/span][!--/colorc--]
$searchfields=($searchfields.'OR '.$member['member_name']."m.search_field LIKE '$search_item');
}
}
[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]below puts the whole wuery together so it will be along the lines of: SELECT * FROM ABCm, BCDm, CDEm WHERE ABCm LIKE 'searched word/phrase' OR BCDm LIKE 'searched word/phrase' OR CDEm LIKE 'searched word/phrase'[!--colorc--][/span][!--/colorc--]

$results=mysql_query("SELECT * FROM $tables WHERE $searchfields");
while ($row=mysql_fetch_array($results)) {
echo("$row['fieldname1'], $row['fieldname2'], $row['fieldname3'].....");
}


Does that explain it better?

[cheryl]

thank you very much. i think i understand it better with explanation.

[cheryl]

i seem to have a problem with the codes now.

[code]
<?php
session_save_path("../servertemp");
session_start();
if(empty($_SESSION[login]))
//check if the user has logged in, if not redirect the user to login page
{
    $_SESSION[error]="Access Denied! You need to login first.";
    header("Location: ../login.php");
}
else
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {
    font-family: Verdana;
    font-weight: bold;
    font-size: 12px;
}
-->
</style>
</head>

<body>
<form name="form1" method="post" action="<? $PHP_SELF?>">
  <table width="500" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td width="90"><span class="style1">Search:</span></td>
      <td width="223"><input type="text" name="name"></td>
      <td width="187"><input type="submit" name="Submit" value="Submit"></td>
    </tr>
  </table>
  <p>
    <?php
    include("../lib/db_connect.php");
    $link = @mysql_connect("localhost", $user, $pass);    

    if (!$link)
        die ("Couldn't connect to MySQL");
    mysql_select_db($db, $link)
        or die ("Couldn't open $db".mysql_error());
        
        
        $keyperson = "";
        
        if(!empty($_POST["name"]))
        {
        $mkeyperson = ["name"];
        }
        
        
        if($keyperson!="")
        {
            $i=1;
            //Query to select all members from you members table, you will need to change member_name
            //to the field that has all your members names under it and change members to the name of your
            //table with all the members in
            $query=mysql_query("SELECT clientCode FROM client");
            
            //While there are still users not selected keep going
            while ($clients=mysql_fetch_array($query))
            {
            
            //If this is the first time the loop has gone through then we dont need ther 'OR' or the ',' infront
            if ($i=1)
            {
            $tables=($clients["clientCode"].'m');
            $searchfields=($clients["clientCode"]."m.memName LIKE %$keyperson%");
            $i++;
            }//end if
            
            //else we need to add the 'OR' and ','
            else
            {
            
            //basically creating a line with ABCm and XYZm and making the query select all the tables
            $tables=($tables.', '.$clients["clientCode"].'m');
            
            //below does similar but sets it up like this: ABCm.searchfield LIKE'$search_item...
            //where searchfield is you need to rename that to the field in your table that you want to rename
            //and where $search_item is you need to change to the variable that hold the value of your search
            $searchfields=($searchfields.'OR '.$clients["clientCode"]."m.memName LIKE %$keyperson%");
            }//end else
            }//end while
            
            //below puts the whole query together so it will be along the lines of:
            //SELECT * FROM ABCm, BCDm, CDEm WHERE ABCm LIKE 'searched word/phrase' OR BCDm
            //LIKE 'searched word/phrase' OR CDEm LIKE 'searched word/phrase'
            $results=mysql_query("SELECT * FROM $tables WHERE $searchfields");
            
                print "<font face=Verdana size=1>";
                print "<table width=790 align=center border=1 bordercolor=orange cellspacing=0 cellpadding=5>";
                print "<tr>Member Results</tr>";
                print "<tr bgcolor=#ff9900>";
                print "<td align=left width=100><b>Member Code</b></td>";
                print "<td align=left width=100><b>NRIC</b></td>";
                print "<td align=left width=300><b>Name</b></td>";
                print "<td align=left width=140><b>Designation</b></td>";
                print "</tr>";
            
            while ($row=mysql_fetch_array($results))
            {
                print "<font face=Verdana size=1>";
                print "<table width=790 align=center border=1 bordercolor=orange cellspacing=0 cellpadding=5>";
                print "<td align=left width=100>".$row['memCode']"</td>";
                $nric = substr($row["nric"], -5);
                print "<td align=left width=100>Sxxx$nric</td>";
                print "<td align=left width=300>".$row['memSalute']." ".$row['memName']."</td>";
                print "</tr>";
            }
            
            
            $nmquery=mysql_query("SELECT * FROM nonmembers WHERE nmName LIKE %$keyperson%");
            
                print "<font face=Verdana size=1>";
                print "<table width=790 align=center border=1 bordercolor=orange cellspacing=0 cellpadding=5>";
                print "<tr>Non-member Results</tr>";
                print "<tr bgcolor=#ff9900>";
                print "<td align=left width=100><b>Member Code</b></td>";
                print "<td align=left width=300><b>Name</b></td>";
                print "<td align=left width=140><b>Designation</b></td>";
                print "<td width=100 align=center><b>Organisation</b></td>";
                print "</tr>";
            
            while ($row=mysql_fetch_array($nmquery))
            {
                print "<font face=Verdana size=1>";
                print "<table width=790 align=center border=1 bordercolor=orange cellspacing=0 cellpadding=5>";
                print "<td align=left width=100>".$row['nmCode']"</td>";
                print "<td align=left width=300>".$row['nmSalute']." ".$row['nmName']."</td>";
                print "<td align=left width=100>".$row['nmDes']"</td>";
                print "<td align=left width=200>".$row['nmOrgName']"</td>";
                print "</tr>";
            }
            
        }
?>                                                        
</body>
<?
}
?>
  </p>
</form>
</html>
[/code]


i'm getting the clientCode from the clients' table to match to .m for get all the tables with clientCode.m
from there, i will get all members from all the .m tables, right?? then will print out what i've searched for.

i have one other non-members table where it will output whatever i typed in to the textfield for the search, and then print the results.

my fren has seen the codes and think there's no problem with it. however there is an error when i have it run. i aint sure if it's my localhost that is giving the problem, or the code itself. but just want to check that what i'm doing, is correct right??

assist please.

[shocker-z]

%$keyperson%
needs to be
'%$keyperson%'


Give that a go.. also what is the error? might help using

$query=mysql_query("SELECT clientCode FROM client") OR die(mysql_error());

instead of just

$query=mysql_query("SELECT clientCode FROM client");


Do the same for any other querys and will tell you where the error is.. as i said i havn't been able to try this myself so not sure if i missed anything anywhere.

[cheryl]

i keep getting a parse error at line 44. it said something like " parse error '[' .... at line 44." but at line 44 there isn't any [].

[psyion]

i think the

$mkeyperson = ["name"];

in your link 44 should be

$mkeyperson = $_POST["name"];