mySQL, PHP

[nikomou]

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

[djr33]

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.

[Pixelcode]

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%'";
} 

[Twey]

$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.

[nikomou]

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 " | "

[Twey]

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 . "|");
  }
} 

[nikomou]

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?!

[Twey]

Not really. Can you clarify?

[nikomou]

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!

[Twey]

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?