Page 1 of 1

search multiple table

Posted: Wed Jan 13, 2010 6:05 am
by kalp1200
{
$query = "SELECT * FROM asset_notebookmaster " ."WHERE asset_notebookmaster.notebook_user LIKE '%".$keywords[$i]."%'"." OR asset_notebookmaster.notebook_description LIKE '%".$keywords[$i]."%'"." UNION
"SELECT * FROM asset_cablelock " ."WHERE asset_cablelock.staffname LIKE '%".$keywords[$i]."%'"." ORDER BY notebook_assetid";
}
//Store the results in a variable or die if query fails
$result = mysql_query($query) or die(mysql_error());
} if ($search == NULL or $search == '%'){
} else {
//Count the rows retrived
$count = mysql_num_rows($result);
}

Hi, can anyone show me how to search two table. The thing is, both asset_notebookmaster and asset_cablelock has a user field. Not all users are identical though. I am having a T string error. is this way to search two tables

Re: search multiple table

Posted: Wed Jan 13, 2010 6:21 am
by aravona
Just clarifying, your wanting to search two tables - based on the same or different attributes?

You can easily search two tables using a join, if your searching based on your users.

By saying not all users are identical do you mean -> table 1 has users a, b, c, d table 2 has users a, b, f, g ?

If so you could use a full join

Code: Select all

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
The FULL JOIN keyword return rows when there is a match in one of the tables.
IF your basing it on one attribute a join works fine.

But your union should work as well, have you tried your SQL code without php ? I mean on your console or with your myphpadmin or whatever you use to see if you get a result that way ?

Re: search multiple table

Posted: Wed Jan 13, 2010 10:52 pm
by kalp1200
Hi thanks, yeah, its the exact scenario. I am creating a search page that will list items that user of table 1 and 2 have, say for example, A( user ) has a notebook in table 1 and A(user ) has a desktop in table 2. The search code will check through these two tables and retrieve the items that A have. This query works fine in SQL

Code: Select all

SELECT staffname,notebook_user
FROM asset_cablelock
FULL JOIN asset_notebookmaster
ON staffname=notebook_user;
but on a php page, it returns parser error. I am attaching the php page as well.

Code: Select all

<?php
include("php/connect.php");
include("php/auth.php");
if(isset($_GET['search']))
{
$search = $_GET['search'];
}
 
//trim whitespace from variable
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $search);
 
//seperate multiple keywords into array space delimited
$keywords = explode(" ", $search);
 
//Clean empty arrays so they don't get every row as result
$keywords = array_diff($keywords, array(""));
 
//Set the MySQL query
if ($search == NULL or $search == '%'){
} else {
for ($i=0; $i<count($keywords); $i++) 
{
$query = "SELECT staffname,notebook_user FROM asset_cablelock FULL JOIN asset_notebookmaster" ."WHERE asset_cablelock.staffname=asset_notebookmaster.notebook_user LIKE '%".$keywords[$i]."%'"."";
}
$result = mysql_query($query) or die(mysql_error());
}
if ($search == NULL or $search == '%')
{
} 
else 
{
//Count the rows retrived
$count = mysql_num_rows($result);
}
echo "<html>";
echo "<head>";
echo "<title>Search for new models here</title>";
echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />";
echo "</head>";
echo "<body topmargin=\"0\" onLoad=\"self.focus();document.searchform.search.focus()\">";
echo "<table style=\"height: 156\" width=\"900\" height=\"350\" id=\"table3\" align=\"center\">";
echo "<tr>";
echo "<td style=\"height: 100px;\" valign=\"top\" align=\"center\">";
echo "<p align=\"center\">";
include 'header.php';
include 'menu.php';
echo "</td>";
echo "</tr>";
echo "</table>";
 
echo "<center>";
echo "<table class=\"style44\" bgcolor=\"#FFFFFF\" style=\"height: 56\" width=\"900\" height=\"350\" id=\"greeny\" align=\"center\">";
echo "<tr>";
echo "<td style=\"height: 100px;\" class=\"style3\" valign=\"top\" align=\"center\">";
echo "<br /><form name=\"searchform\" method=\"GET\" action=\"usersearch.php\">";
echo "<input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" />";
echo " <input type=\"submit\" value=\"Search\" />";
echo "</form>";
 
//If search variable is null do nothing, else print it.
if ($search == NULL) 
{
 
} 
else 
{
echo "You searched for <b><FONT COLOR=\"blue\">";
foreach($keywords as $value) {
   print "$value ";
}
echo "</font></b>";
}
echo "<p> </p><br />";
echo "</center>";
 
//If users doesn't enter anything into search box tell them to.
if ($search == NULL)
{
    echo "<center><b><FONT COLOR=\"black\">Please enter a search parameter to continue</font></b><br /></center>";
} 
elseif ($search == '%')
{
    echo "<center><b><FONT COLOR=\"black\">Please enter a search parameter to continue.</font></b><br /></center>";
//If no results are returned print it
} 
elseif ($count <= 0)
{
    echo "<center><b><FONT COLOR=\"black\">Your query returned no results from the database.</font></b><br /></center>";
    echo "<br><center><font face='Verdana' size= '2'><a href=\"addmasterdesktop.php\"> Did not find what you are looking for.Click here to add a new master model </font></a></center>";
 
    //ELSE print the data in a table
} 
else 
{
//Table header
echo "<center><table border=\"0\" style=\"width:100%\" id=\"tablesearch\" bgcolor=\"000000\">";
echo "<tr>";
echo "<td style=\"width: 150px\"><b><font face='Verdana' COLOR=\"white\" size= '1'>Notebook Model</font></b></td>";
echo "<td style=\"width: 100px\"><b><font face='Verdana' COLOR=\"white\" size= '1'>Service Tag</font></b></td>";
echo "<td style=\"width: 50px\"><b><font face='Verdana' COLOR=\"white\" size= '1'>User</font></b></td>";
echo "<td style=\"width: 50px\"><b><font face='Verdana' COLOR=\"white\" size= '1'>VIEW</font></b></td>";
echo "</tr>";
echo "</table></center>";
 
//Colors for alternation of row color on results table
$color1 = "FFFF66";
$color2 = "99CC33";
//While there are rows, print it.
while($row = mysql_fetch_array($result))
{
$row_color = ($row_count % 2) ? $color1 : $color2;
//table background color = row_color variable
echo "<center><table border=\"0\" width=\"100%\" id=\"tablesearch\" bgcolor=".$row_color.">";
echo "<tr>";
echo "<td style=\"width: 150px\"><font face='Verdana' size= '2'>".$row['notebook_description']."</td>";
echo "<td style=\"width: 100px\"><font face='Verdana' size= '2'>".$row['notebook_servicetag']."</td>";
echo "<td style=\"width: 50px\"><font face='Verdana' size= '2'>".$row['notebook_user']."</td>";
echo "<td style=\"width: 50px\"><font face='Verdana' size= '2'><a href=\"./viewdesktopinline2.php?id=".$row['id']."\" target=\"_blank\">VIEW</font></a></td>";
echo "</tr>";
echo "</table></center>";
$row_count++;
}
echo "<br><center><font face='Verdana' size= '2'><a href=\"addmasterdesktop.php\"> Did not find what you are looking for.Click here to add a new master model </font></a></center>";
}
echo "</body>";
echo "</html>";
if ($search == NULL or $search == '%') {
} else {
 
//clear memory
mysql_free_result($result);
}
include ('footer.php');
?>

Re: search multiple table

Posted: Thu Jan 14, 2010 3:50 am
by aravona

Code: Select all

$query = "SELECT staffname,notebook_user FROM asset_cablelock FULL JOIN asset_notebookmaster" ."WHERE asset_cablelock.staffname=asset_notebookmaster.notebook_user LIKE '%".$keywords[$i]."%'"."";
You put WHERE, try putting ON

Code: Select all

$query = "SELECT staffname,notebook_user FROM asset_cablelock FULL JOIN asset_notebookmaster" ."ON asset_cablelock.staffname=asset_notebookmaster.notebook_user";
But that wont get your Where working.... but it shouldnt give you a parse error.

Code: Select all

SELECT asset_cablelock.staffname, asset_cablelock.notebook_user, asset_notebookmaster.notebook_user
FROM asset_cablelock, asset_notebookmaster
WHERE asset_cablelock.notebook_user = asset_notebookmaster.notebook_user
 
This should also give you a result

Re: search multiple table

Posted: Thu Jan 14, 2010 3:57 am
by vincentfon

Code: Select all

$query = "SELECT staffname,notebook_user FROM asset_cablelock FULL JOIN asset_notebookmaster ON staffname=notebook_user WHERE notebook_user LIKE '%$keywords[$i]%'";
i think $query should be like this.

Re: search multiple table

Posted: Thu Jan 14, 2010 6:49 am
by kalp1200

Code: Select all

$query = "SELECT * FROM asset_cablelock FULL JOIN asset_notebookmaster ON staffname=notebook_user WHERE notebook_user OR staffname LIKE '%$keywords[$i]%'";
 
The above worked and thanks a bundle. Thanks.. FULL JOIN is used to get values of the same value from the two tables. Is there a way to get values from one table only after entering the search keyword. For example, right now if I enter A and A exists in table 1 and table 2, than the results appear. I want to search for B which only exists in table 1 ( I want B to appear when I enter B in the search key ). Can I modify the query to include this..

Basically, what I want is ..
1) When I search for A, A has a notebook and desktop
2) When I search for B, B has a notebook
3) When I search for C, C has a desktop

Re: search multiple table

Posted: Thu Jan 14, 2010 6:53 am
by aravona
The FULL JOIN keyword return rows when there is a match in one of the tables.
Your code should work for this. A full join only needs a result in one table, not both.

Look at the last example.