combining the results of mysql query and finding the unique tuples in php

--001636c5ac69e5edb70499367011
Content-Type: text/plain; charset=ISO-8859-1

Hi
I have a certain number of mysql tables(relation), say 50, some of them
having 5 fields and some with 6 fields. a particular search item may be
found in multiple tables with multiple rows. I am using a loop to find all
those.
My problem is I want to first combine all those results and then find all
the unique entries. (the query results may overlap).


What is the best way to do it?


I am trying to make my code as simple as possible:

Suppose I have $nRows number of tables;

for ($j = 0 ; $j < $nRows ; $j++)
{
$query = "select * from ".$tables[$j]." where
name='xxxxxx'";
$qRes = mysql_query($query);
if (!$qRes) die ("Database access failed: " .
mysql_error());
$numRow = mysql_num_rows($qRes);
$numCol = mysql_num_fields($qRes);
for ($k = 0 ; $k < $numRow ; $k++)
{
$resRow = mysql_fetch_row($q1Res);
echo '';
for ($l = 0; $l<$numCol; $l++)
{
echo "$resRow[$l]";
}
echo"";
}
}


This code is printing following values fetched from different table that may
be duplicate. I just want the unique rows.

chr6 30867310 30867335 1007_s_at 1007_s_at:413:117 chr6 30867423
30867448 1007_s_at 1007_s_at:156:191 chr6 30867492 30867517 1007_s_at
1007_s_at:55:353 chr6 30867543 30867568 1007_s_at 1007_s_at:57:77 chr6
30867550 30867575 1007_s_at 1007_s_at:123:381 chr6 30867556 30867581
1007_s_at 1007_s_at:28:379 chr6 30867563 30867588 1007_s_at 1007_s_at:8:385
chr6 30867569 30867594 1007_s_at 1007_s_at:44:67 chr6 30867595 30867620
1007_s_at 1007_s_at:128:385 chr6 30867692 30867717 1007_s_at
1007_s_at:74:91 chr6 30867765 30867790 1007_s_at 1007_s_at:133:441 chr6
30867772 30867797 1007_s_at 1007_s_at:244:391 chr6 30867778 30867803
1007_s_at 1007_s_at:397:301 chr6 30867786 30867811 1007_s_at
1007_s_at:310:185 chr6 30867851 30867876 1007_s_at 1007_s_at:254:353 chr6
30867858 30867883 1007_s_at 1007_s_at:142:13 chr6 30867310 30867335
1007_s_at 1007_s_at:416:177 chr6 30867423 30867448 1007_s_at
1007_s_at:569:289 chr6 30867492 30867517 1007_s_at 1007_s_at:299:537 chr6
30867543 30867568 1007_s_at 1007_s_at:87:115 chr6 30867550 30867575
1007_s_at 1007_s_at:314:587 chr6 30867556 30867581 1007_s_at
1007_s_at:249:581 chr6 30867563 30867588 1007_s_at 1007_s_at:713:589 chr6
30867569 30867594 1007_s_at 1007_s_at:486:99 chr6 30867595 30867620
1007_s_at 1007_s_at:324:589 chr6 30867692 30867717 1007_s_at
1007_s_at:279:135 chr6 30867765 30867790 1007_s_at 1007_s_at:92:689 chr6
30867772 30867797 1007_s_at 1007_s_at:372:603 chr6 30867778 30867803
1007_s_at 1007_s_at:201:465 chr6 30867786 30867811 1007_s_at
1007_s_at:45:281 chr6 30867851 30867876 1007_s_at 1007_s_at:170:629 chr6
30867858 30867883 1007_s_at 1007_s_at:568:9 chr6 30867310 30867335
1007_s_at 1007_s_at:467:181 chr6 30867423 30867448 1007_s_at
1007_s_at:531:299 chr6 30867492 30867517 1007_s_at 1007_s_at:86:557 -----
----
---

Thanks for help

--
Fahim
Bioinforformatics Lab
University of Louisville
Louisville, KY - USA

--001636c5ac69e5edb70499367011--

Re: combining the results of mysql query and finding theunique tuples in php

On 1/7/2011 12:10 AM, Fahim M wrote:
> Hi
> I have a certain number of mysql tables(relation), say 50, some of them
> having 5 fields and some with 6 fields. a particular search item may be
> found in multiple tables with multiple rows. I am using a loop to find all
> those.
> My problem is I want to first combine all those results and then find all
> the unique entries. (the query results may overlap).
>
>
> What is the best way to do it?
>
If you're looking for unique results, do a UNION query for all the
tables. You'll need to add a dummy field for the 5-field tables, and
make sure the field names are the same (use AS
to make sure everything ends up the correct result
column). UNION queries automatically remove duplicates.

Toby

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: combining the results of mysql query and finding theunique tuples in php

On Fri, Jan 7, 2011 at 9:46 AM, Toby Hart Dyke wrote:
> On 1/7/2011 12:10 AM, Fahim M wrote:
>>
>> Hi
>> I have a certain number of mysql tables(relation), say 50, some of them
>> having 5 fields and some with 6 fields. a particular search item may be
>> found in multiple tables with multiple rows. I am using a loop to find a=
ll
>> those.
>> My problem is I want to first combine all those results and then find al=
l
>> the unique entries. (the query results may overlap).
>>
>>
>> What is the best way to do it?
>>
> If you're looking for unique results, do =A0a UNION query for all the tab=
les.
> You'll need to add a dummy field for the 5-field tables, and make sure th=
e
> field names are the same (use AS to make su=
re
> everything ends up the correct result column). UNION queries automaticall=
y
> remove duplicates.
>
> =A0Toby
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Though from the sounds of it, there is a problem with the table design
if you have multiple results in multiple tables

--=20

Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php