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]

Re: help with SQL search through multiple tables

LRW wrote:[color=blue]
> 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.
> =)[/color]

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_q uery($query);
while($row=mysq l_fetch_array($ result)) {
array_push($fou nd,$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]

Re: help with SQL search through multiple tables

"J.O. Aho" <user@example.n et> wrote in message
news:buc3l2$g2u 5e$1@ID-130698.news.uni-berlin.de...[color=blue]
> You need to make as many quieries as there are tables you want to search,[/color]
of[color=blue]
> course you could make a join, but that depends on how things are related[/color]
to[color=blue]
> eachother.
>
> Assume you have a array with the names of all the talbes you need to[/color]
search,[color=blue]
> 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_q uery($query);
> while($row=mysq l_fetch_array($ result)) {
> array_push($fou nd,$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[/color]

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]

Re: help with SQL search through multiple tables

LRW wrote:
[color=blue]
> 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.[/color]

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.

[color=blue]
> So...that doesn't matter, does it? What would $table contain? The names of
> all 40 tables comma separated?[/color]

A normal array of strings,

$table=array("a rray1","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]

Re: help with SQL search through multiple tables


"LRW" <druid@NOSPAHMc elticbear.com> schreef in bericht
news:hSfOb.8812 3$na.47077@attb i_s04...[color=blue]
> 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,[/color]
that'd[color=blue]
> be cool. If I know where to look I can teach myself...just need a[/color]
direction.[color=blue]
> =)
>
> Thanks for any feedback!!
> Liam
>
>[/color]
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]

Re: help with SQL search through multiple tables

damn :), is it that hard?

http://groups.google.com/groups?q=alt.php.sql&ie=UTF-8&oe=UTF-8&hl=en

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

Best Regards,

Lucas

"LRW" <druid@NOSPAHMc elticbear.com> schrieb im Newsbeitrag
news:Q%gOb.8715 1$8H.130499@att bi_s03...[color=blue]
> "J.O. Aho" <user@example.n et> wrote in message
> news:buc3l2$g2u 5e$1@ID-130698.news.uni-berlin.de...[color=green]
> > You need to make as many quieries as there are tables you want to[/color][/color]
search,[color=blue]
> of[color=green]
> > course you could make a join, but that depends on how things are related[/color]
> to[color=green]
> > eachother.
> >
> > Assume you have a array with the names of all the talbes you need to[/color]
> search,[color=green]
> > 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_q uery($query);
> > while($row=mysq l_fetch_array($ result)) {
> > array_push($fou nd,$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[/color]
>
> Cripes! My ISP doesn't have alt.php.sql. That'd be a perfect group for a[/color]
lot[color=blue]
> 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
>
>[/color]