mySQL, PHP

Hey, is there an easy way to "search" for the same term in 5 different tables in a mysql database?

Umm... just get the values of all the items.

do something like this:


1. GET DATA FROM DATABASE, storing it as an array.
2. Compare to the 'term' for each item in the array.
3. Loop 1-2 for all tables of the database. OR copy/paste.

The only complex thing about what you said is different tables... just repeat the command 5 times.... just cut/paste. Or, if you feel fancy, make a while loop or something... whatever.

PHP Code:

---

mysql_connect("host","username","password");
mysql_select_db("dbname");
$tables = array("table1","table2","table3","table4","table5");
$search = "term";
$field = "name";
foreach($tables as $mysql){
$query1 = "SELECT * FROM `$mysql` WHERE `$field` LIKE '%$search%'";
} 


---

:)

Code:

---

$tables = array("table1","table2","table3","table4","table5");
$search = "term";
$field = "name";
foreach($tables as $mysql) {
  $query1 = "SELECT * FROM `$mysql` WHERE `$field` LIKE '%$search%';";
  mysql_query($query1);
}

---

The ending quote. Small, but oh so vital. :)

Thanks for that.

What i want to do with the results is to say which table the result was found in. and to "echo" the results, like this:

*Search term* Found in *Table 1*

and have the results seperated by a pipeline " | "

PHP Code:

---

foreach($tables as $mysql) {
  $query1 = "SELECT * FROM `$mysql` WHERE `$field` LIKE '%$search%';";
  $res = mysql_query($query1);
  $rs = mysql_fetch_array($res);
  if($rs) {
    echo($search . ' found in ' . $mysql . "\n\n");
    while($rs = mysql_fetch_array($res))
      foreach($res as $col)
        echo($col . "|");
  }
} 


---

Thanks tway!

Is it possible to have it to search for all the items in an other table? rather than searching for 1 specific term?

Does that make sence?!

Not really. Can you clarify?

ok, instead of having the search querie ($search), i want to use the contents from an other table (tbmain) in the database.

e.g.

xxx found in yyy | xxx found in yxy | xxx found in xyx | xxx found in xzx | xxx found in yzy
bbb found in yyy | bbb found in yxy | bbb found in xyx | bbb found in xzx | bbb found in yzy
ccc found in yyy | ccc found in yxy | ccc found in xyx | ccc found in xzx | ccc found in yzy

Where, xxx, bbb, ccc are located in the other table (tbmain)

Kinda hard to explain!

Quote:

---

Kinda hard to explain!

---

Evidently... you want to find a certain field, then, based on that field, display data from a row in another table that also contains the data in that field?

Quote:

---

Originally Posted by Twey

Evidently... you want to find a certain field, then, based on that field, display data from a row in another table that also contains the data in that field?

---

Woo, confusing, but yes, i think thats right!!

Let me explain again just to clarify...

Content in the main table:
aaa bbb ccc ddd eee fff

I want to see if these are available in the other tables (categories). If they are available echo:

aaa found in cat1|aaa found in cat2 etc...

Ok, maybe this will better explain...
The main table (tbmain) has a list of all the mobile phones on my datababse.
Nokia n71 | Nokia N90 | Bla Bla | Bla

The other tables are a list of phones on a network, e.g. vodafone, tmobile, orange, o2, virgin, btmobile, three

I want to see what handsets (from tbmain) are available on each network, and "echo" it on the page.

So, (for e.g.) the Nokia n71 is available on btmobile and virgin, so i want this to happen:

Nokia N71 on btmobile | Nokia N71 on Virgin

Then i want the script to go through all the handsets from tbmain, so i would get a list of all the handsets, still showing wha network the handset is on:

Nokia N71 on btmobile | Nokia N71 on Virgin |
Nokia N90 on orange | Nokia N90 on Virgin | Nokia N90 on o2 |
Bla Bla on orange |
Bla on vodafone | Bla on three |

Does that clear things up Twey?