Jump to content

multiple table search


cheryl

Recommended Posts

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?
Link to comment
Share on other sites

[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 :)
Link to comment
Share on other sites


$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?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

%$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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.