Search from multiple tables ?? - Page 2

[tulipputih]

Hi djjozsi.
Thanks for the complete example you gave me. I will try it.
here is the code that I have modified from an earlier respond to this thread. however, there is an error at the last line which do not contains any code. Could you please have a look at my code?

Thanks a lot.

<?php
  $con = mysql_connect("localhost","root","rootroot");
  mysql_select_db("permis");
 
  // Get the search variable from URL
  $searchstring = $_GET['general'] ;  
 
 //get all the tables from the database test
      $q = "show tables";
      $r = mysql_query($q);
      $searchresult = array();
 
      while($row = mysql_fetch_array($r)) {
            $table = $row[0]; // get the tablename
     	
		
            $searchtables = "select * from ".$table." where ".$column." like '%".$searchstring."%'"; //search the table column for the search string
            $result = mysql_query($searchtables);
              while($row = mysql_fetch_array($result)) {
                    $searchresult = $row['.$column.'];
                   }
// displayresult echo $searchtables;          

echo $searchresult ; 

      ?>

[djjjozsi]

yep,
here it is, after some modifications:

the column names you did not inserted,

You need to build search conditions on evry field names in your table, separated with an OR OPERATOR.

lets see that one:
http://phpcode.hu/dl/?f=1029

if you use a variable, and you re-use it in the next cycle, you need to reset its value to avoid overwrite.

always check if a search word cause a result.

i've added a clicnt side ORDER class to make the results readable.

learn the foreach technique to build conditions from an array. its suggested to use mysql_real_escape_string to protect your database from the SQL injections from your visitors.

if a user entered a keyword then run the query then,
to make sure a user entered a word use isset or !empty functions to check that.

if you use this on a live server, you need to limit which user can search in which tables, ergo you need to make an "allowed fields and tables list" to block searches in password fields or in private informations.

[tulipputih]

djjjozsi,
million thanks.
So many things I have learnt from you :)

[djjjozsi]

you're welcome :)

[digital-ether]

SQL allows you to SELECT from multiple tables in a single query with JOIN or join multiple selects together into a single query with UNION.

Using JOIN:

SELECT * FROM  table1 WHERE username = 'bob'
JOIN
SELECT * FROM  table2 WHERE username = 'bob'
LIMIT 0 , 10

Note that the limit applies to the results from both tables. This way you can ORDER, GROUP BY, LIMIT the results as one single set making pagination, and relevancy easy to implement.

With the current code you guys have, you could use JOIN to merge the queries into one, and execute it against the MySQL database in one go.

eg:

<?php
  $con = mysql_connect("localhost","root","rootroot");
  mysql_select_db("permis");
 
  // Get the search variable from URL
  $searchstring = $_GET['general'] ;  
 
 //get all the tables from the database test
      $q = "show tables";
      $r = mysql_query($q);
      $searchresult = array();
 
 	  $searchtables = array();
      while($row = mysql_fetch_array($r)) {
            $table = $row[0]; // get the tablename
     	
			// save query for later
            $searchtables[] = "select * from ".$table." where ".$column." like '%".$searchstring."%'"; //search the table column for the search string
            
	}
	
	// do the search in one go
	$search_all = implode(' JOIN ', $searchtables)." LIMIT 10";
	$result = mysql_query($search_all);
	$searchresult = '';
	while($row = mysql_fetch_array($result)) {
	    $searchresult = $row['.$column.'];
	}
	
// displayresult echo $searchtables;          
echo $searchresult ; 

?>