CODEWITHSUNDEEP
phpmysql
Sarmad Hussain
Sarmad Hussain

Reputation: 1

How to search for data within the entire database

I have created 3 tables tbl1, tbl2 and tbl3 in one database (my_db). My question is how can I search the data from those three tables in one search ?

My code.

<?php
    include('connect-db.php');
        include('sort.php');

$Search  = isset($_GET['Search'])  ? '%'.$_GET['Search' ].'%' : '' ;

$result = mysql_query("SELECT * FROM tbl1, tbl2, tbl3  where name  like '$Sea'");

?>

Upvotes: 0

Views: 80

Answers (2)

krowe
krowe

Reputation: 2280

Joins are used if each of those tables have a shared relationship with each other. For example, a user may have a profile and a list of documents published. This will output each table as additional columns in the result.

SELECT * FROM tbl1 
  JOIN tbl2 ON tbl1.id=tbl2.id 
  JOIN tbl3 ON tbl1.id=tbl3.id 
WHERE tbl1.name LIKE '$Sea'

If you want to add each table as extra rows in the result then you can use a union. You need to structure your results the same in all tables for this to work. For this reason, unions work best when your tables are very similar in structure. Keep in mind that union will remove duplicate entries from the two sets. If you can guarantee that the two sets are distinct, or you do not care about duplicates, then union all will perform better, especially as your result sets get larger.

SELECT * FROM tbl1 WHERE tbl1.name LIKE '$Sea'
UNION SELECT * FROM tbl2 WHERE tbl2.name LIKE '$Sea'
UNION SELECT * FROM tbl3 WHERE tbl3.name LIKE '$Sea'

UPDATE: Also, as others have mentioned, you are using deprecated mysql functions. Here is a sample of a better way to connect and use MYSQL:

<?php
/* Connect to an ODBC database using PDO (which is more secure than the 
   older mysql libraries because it supports query parameters). */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $conn = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

// Query example
$sql = 'SELECT * FROM table_name';
foreach($conn->query($sql) as $row) {
    echo $row['id'].', '.$row['anotherfield'].'<br>';
}

Upvotes: 1

Prafful Garg
Prafful Garg

Reputation: 214

Basically you need to implement JOINS, while querying Database, to use JOINS the tables must have some kind of relationship between them.

So once you use JOINS in your query it would easy for you to get the results.

Upvotes: 0

Related Questions