Re: [PHP-DB] combining the results of mysql query and finding the unique 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 oldfieldname AS newfieldname 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: [PHP-DB] combining the results of mysql query and finding the unique tuples in php
On Fri, Jan 7, 2011 at 9:46 AM, Toby Hart Dyke t...@hartdyke.com 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 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 oldfieldname AS newfieldname 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 Though from the sounds of it, there is a problem with the table design if you have multiple results in multiple tables -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] combining the results of mysql query and finding the unique tuples in php
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='xx';
$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 'tr';
for ($l = 0; $l$numCol; $l++)
{
echo td$resRow[$l]/td;
}
echo/tr;
}
}
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
