help with SQL search through multiple tables

[LRW]

Well, I have another question that's hard to ask, so I may end up over
explaining the question.
I have about 40 tables, all with a similar column structure. I want to be
able to search through every table for a keyword.
Is there a way to do that using, an array or next or something.

So like, all the table, say tbl_1, tbl_2, tbl_3 etc have let's say 3
columns. id, name, note for example. And I want to seach all 40 tables in
the name column for %x%.

If someone can just point me to a Web site that has tips or whatnot, that'd
be cool. If I know where to look I can teach myself...just need a direction.
=)

Thanks for any feedback!!
Liam

[J.O. Aho]

LRW wrote:

Well, I have another question that's hard to ask, so I may end up over
explaining the question.
I have about 40 tables, all with a similar column structure. I want to be
able to search through every table for a keyword.
Is there a way to do that using, an array or next or something.

So like, all the table, say tbl_1, tbl_2, tbl_3 etc have let's say 3
columns. id, name, note for example. And I want to seach all 40 tables in
the name column for %x%.

If someone can just point me to a Web site that has tips or whatnot, that'd
be cool. If I know where to look I can teach myself...just need a direction.
=)

You need to make as many quieries as there are tables you want to search, of
course you could make a join, but that depends on how things are related to
eachother.

Assume you have a array with the names of all the talbes you need to search,
we call it $tables. Another arrya where you save the results, $found.

for(i=0;i<count($tables);i++) {
$query="SELECT * FROM " . $table[i] . " WHERE name LIKE '%x%'";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) {
array_push($found,$row);
}
}

Now you have all the result rows stored in $found, of course you can
send/process the $row in the while loop.
Follow up has been set to proper newsgroup, alt.php.sql

//Aho

[LRW]

"J.O. Aho" <us**@example.net> wrote in message
news:bu************@ID-130698.news.uni-berlin.de...

You need to make as many quieries as there are tables you want to search, of course you could make a join, but that depends on how things are related to eachother.

Assume you have a array with the names of all the talbes you need to search, we call it $tables. Another arrya where you save the results, $found.

for(i=0;i<count($tables);i++) {
$query="SELECT * FROM " . $table[i] . " WHERE name LIKE '%x%'";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) {
array_push($found,$row);
}
}

Now you have all the result rows stored in $found, of course you can
send/process the $row in the while loop.
Follow up has been set to proper newsgroup, alt.php.sql

Cripes! My ISP doesn't have alt.php.sql. That'd be a perfect group for a lot
of my questions! =/

So $found wouldn't have to exist before we query, right?
I do currently have another table that has all the table names in it.
eg: (but not exactly like this,)
40 tables with the following columns: id, tbl_id, name, note
Then a table (tbl_listing) with these columns: tbl_id, tbl_name
Make sense? Each row in all 40 tables has a tbl_id number that corresponds
to the table it's in, and the table tbl_listing has in each row the that
table number and its name.

So...that doesn't matter, does it? What would $table contain? The names of
all 40 tables comma separated?

Thanks for replying!
Liam

[J.O. Aho]

LRW wrote:

So $found wouldn't have to exist before we query, right?
I do currently have another table that has all the table names in it.
eg: (but not exactly like this,)
40 tables with the following columns: id, tbl_id, name, note
Then a table (tbl_listing) with these columns: tbl_id, tbl_name
Make sense? Each row in all 40 tables has a tbl_id number that corresponds
to the table it's in, and the table tbl_listing has in each row the that
table number and its name.
To be on the safe side, it's always best to create an empty arrya first
$found=array();

When you save stuff to the $found array, you should tell from which table the
result comes from, I leave it up to you how you want to make that. As using
the *, you will get all the columns of the database line, regadles how they
look like.

So...that doesn't matter, does it? What would $table contain? The names of
all 40 tables comma separated?

A normal array of strings,

$table=array("array1","array2","arrayA",...);

With help of the array you don't have to number the tables in a special way
and you can select the order in which to seach the tables and of course, you
can jump over a table if you want, then just don't include it in the array.

//Aho

[RP]

"LRW" <dr***@NOSPAHMcelticbear.com> schreef in bericht
news:hSfOb.88123$na.47077@attbi_s04...

Well, I have another question that's hard to ask, so I may end up over
explaining the question.
I have about 40 tables, all with a similar column structure. I want to be
able to search through every table for a keyword.
Is there a way to do that using, an array or next or something.

So like, all the table, say tbl_1, tbl_2, tbl_3 etc have let's say 3
columns. id, name, note for example. And I want to seach all 40 tables in
the name column for %x%.

If someone can just point me to a Web site that has tips or whatnot, that'd be cool. If I know where to look I can teach myself...just need a direction. =)

Thanks for any feedback!!
Liam

depending on the database (or mysql version :o) you use, UNION is the answer
I think:
http://www.mysql.com/doc/en/UNION.html

RP

[Lucas]

damn :), is it that hard?

http://groups.google.com/groups?q=al...oe=UTF-8&hl=en

With google you can access virtually any newsgroup(+archive).

Best Regards,

Lucas

"LRW" <dr***@NOSPAHMcelticbear.com> schrieb im Newsbeitrag
news:Q%gOb.87151$8H.130499@attbi_s03...

"J.O. Aho" <us**@example.net> wrote in message
news:bu************@ID-130698.news.uni-berlin.de...

You need to make as many quieries as there are tables you want to

search, of

course you could make a join, but that depends on how things are related to

eachother.

Assume you have a array with the names of all the talbes you need to

search,

we call it $tables. Another arrya where you save the results, $found.

for(i=0;i<count($tables);i++) {
$query="SELECT * FROM " . $table[i] . " WHERE name LIKE '%x%'";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) {
array_push($found,$row);
}
}

Now you have all the result rows stored in $found, of course you can
send/process the $row in the while loop.
Follow up has been set to proper newsgroup, alt.php.sql

Cripes! My ISP doesn't have alt.php.sql. That'd be a perfect group for a

lot of my questions! =/

So $found wouldn't have to exist before we query, right?
I do currently have another table that has all the table names in it.
eg: (but not exactly like this,)
40 tables with the following columns: id, tbl_id, name, note
Then a table (tbl_listing) with these columns: tbl_id, tbl_name
Make sense? Each row in all 40 tables has a tbl_id number that corresponds
to the table it's in, and the table tbl_listing has in each row the that
table number and its name.

So...that doesn't matter, does it? What would $table contain? The names of
all 40 tables comma separated?

Thanks for replying!
Liam