Searching Multiple Tables

[jrotunda85]

I am in the process of trying to create a search option for my database; however, I need the user to be able to submit one search which looks at three tables in the database. I'm using the following right now and it's working for each individual one (I have to comment out two for it to work). So I have two questions:

(1) What's the best way to get a full search of these columns in the respective tables?
(2) As you can see, I generate a link at the bottom that echo's the results. The problem is that I need the variable to change based on what table it comes from. So, for example, if it comes from the retail table I need the link to be more like

<a href=\"retail?id=$Row[0]\">$Row[1]</a><br>

Full Processing Code

<?PHP

session_start();

//Open Database
include 'dbconnection.php';

//Create Variables
$Term = mysql_real_escape_string($_POST['Term']);
$UserID = $_SESSION['UserID'];

//Verify Password
$SQL = mysql_query("SELECT a.CigarID, b.Description, a.CName from cigar a 
LEFT JOIN cd_manufacturer b on a.Manufacturer = b.Code
				   WHERE a.CName LIKE '%$Term%' or b.Description LIKE '%$Term%'");

/*$SQL = mysql_query("SELECT c.RetailID, c.RName from retail c 
				   WHERE c.RName LIKE '%$Term%'");

$SQL = mysql_query("SELECT d.SpotID, d.SName from spot d 
				   WHERE d.SName LIKE '%$Term%'"); */
 
while ($Row = mysql_fetch_array($SQL)){
echo  "<a href=\"cigar.php?id=$Row[0]\">$Row[1] $Row[2]</a><br>";
    }


?>

[kekkaishi]

How about joining all the tables together using UNION and renaming columns using AS keyword so that you could identify from which table which result is from.

EDIT: link to MySQL UNION

[jrotunda85]

How about joining all the tables together using UNION and renaming columns using AS keyword so that you could identify from which table which result is from.

EDIT: link to MySQL UNION

Would UNION give me problems though with the JOIN I'm already using? The reason I ask is because I need to ensure I am displaying text as opposed to the Code which is being stored on the one table.

[kekkaishi]

UNION requires that all the tables have the exact same number of columns. i.e. If your first result set contains 3 columns (like the one you already have), then you need to have all 3 columned tables. It doesn't matter if the result table is created using joins.
In your code, the last two statement creates a result set containing tables of 2 columns. If b.Description in your first statement is really important, then why don't you include it in the other two statements also? Otherwise, eliminate it from the first.

EDIT:

.. displaying text as opposed to the Code which is being stored on the one table.

When you rename your columns using AS you could those name to refer to that column Say, for example:

$qry = "SELECT column_name AS c_name FROM sometable";
$res = mysql_query($qry);
while($row = mysql_fetch_array($res)){
  echo $row['c_name']; //echos the value stored in column_name
}

Hope this helps and good luck.

[jrotunda85]

UNION requires that all the tables have the exact same number of columns. i.e. If your first result set contains 3 columns (like the one you already have), then you need to have all 3 columned tables. It doesn't matter if the result table is created using joins.
In your code, the last two statement creates a result set containing tables of 2 columns. If b.Description in your first statement is really important, then why don't you include it in the other two statements also? Otherwise, eliminate it from the first.

EDIT:
When you rename your columns using AS you could those name to refer to that column Say, for example:

$qry = "SELECT column_name AS c_name FROM sometable";
$res = mysql_query($qry);
while($row = mysql_fetch_array($res)){
  echo $row['c_name']; //echos the value stored in column_name
}

Hope this helps and good luck.

Thanks for all the help. The issue I'm running into though is that that tables aren't going to have the same number of columns. The first has 3 columns (id, manufacturer, brand name) and the other two only have two columns(id, name). The first table needs to have those three distict columns as what's in both manufacturer and brand name is very important to the search.

[kekkaishi]

hmm.. well there is one (probably) a dirty solution I can think of. That is to create a result array and after each query push the result, if it returned any, into that array. While doing so also I would suggest you to use alias to rename the columns so that it'd be easier for you later on while manipulating the result array.

Here's an example of what I am suggesting.

$resultArray = array();
$qry = "SELECT ....";
if(mysql_num_rows($qry) > 0){
  while($row = mysql_fetch_array($qry)){
   array_push($resultArray, $row);
 }
}
//repeat this for the three queries. 
//Now result_array will be a two dimensional array. to manipulate it you could use something like
foreach($resultArray as $secArray){
 foreach($secArray as $key=>$value){
   //create anchor link maybe. 
  echo "Key:".$key.": Value".$value;
 }
}

Again, this probably is a dirty solution. So lets hope someone else would suggest a better one. Anyhow, good luck mate.

Also, here's one link I came across upon a google search.
http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql
Have a look at it, maybe it'll be of help...

[jrotunda85]

I decided, in the interim, to just use 3 different queries which doesn't seem to be bogging down the DB too much at the moment. It's a quick and dirty solution for the interim but surely there has to be some better answer out there???? :confused: