+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Search in three table same time (/showthread.php?tid=6384)
Search in three table same time - El Forum - 02-25-2008
[eluser]Sawariya[/eluser]
Hi friends
How to search in three tables at same time... here i think join is not possible..
any idea..??
expecting your help...
Search in three table same time - El Forum - 02-25-2008
[eluser]adamp1[/eluser]
That I know of you can only search one 'table' at a time. A Join seems to be the only option. If you can't join then 3 searches.
Search in three table same time - El Forum - 02-25-2008
[eluser]Sawariya[/eluser]
I will explain my table structure
Table one- content
-----------
table two - events
--------------
table three- gallery
there is no relation with all these three table..
so how can search in these three table for particular data which user looking for.
any idea...
Search in three table same time - El Forum - 02-25-2008
[eluser]adamp1[/eluser]
Either search each individually OR multiply the tables together and then search that, but I would not suggest the second. Since if you had only 3 tables with 2 items in each it would create a new table with 8 items in. This amount goes up exponentially. {number of tables}^{number of items in each table}. So for 3 tables with 10 items in each that's 59049 rows to search over.
As said search each for what the user wants.
Search in three table same time - El Forum - 02-25-2008
[eluser]webthink[/eluser]
If you have a search that gets run often and needs to search all of those tables at the same time you should consider a table that combines the searchable fields of all three. Also if it's a text based search look into Zend Lucene search index library.
Search in three table same time - El Forum - 02-25-2008
[eluser]jcopling[/eluser]
If I'm understanding your problem correctly you are trying to search 3 different tables for the same criteria and then return the results as 1 query.
If that is correct you can achieve this quite simply by using the UNION command.
I don't believe that Code Igniter has yet implemented this into the Active Record class but it is not difficult to do on your own.
I would suggest doing something like this:
Code:
$sql = "SELECT Field1 AS CommonFieldName FROM Table1 WHERE Condition ";
$sql .= "UNION";
$sql .= "SELECT Field2 AS CommonFieldName FROM Table2 WHERE Condition ";
$sql .= "UNION";
$sql .= "SELECT Field3 AS CommonFieldName FROM Table3 WHERE Condition ";
$this->db->query($sql);The only thing that you must remember here is that 3 queries will be combined into 1 and therefore all field names must be the same in all 3 queries.
Hope this helps.
Search in three table same time - El Forum - 02-26-2008
[eluser]Sawariya[/eluser]
Thanks for your rply jcopling
Here field names are different in all three tables...
#1222 - The used SELECT statements have a different number of columns
i am getting above error while executing this query...
Search in three table same time - El Forum - 02-26-2008
[eluser]jcopling[/eluser]
I'm guessing that the error says it all. I suppose I forgot to mention that all queries included in UNION statements must have the same number of fields returned and they must all be named the same thing.
Hope that helps.
Search in three table same time - El Forum - 07-08-2010
[eluser]yayot[/eluser]
If your tables are related, you can use a inner join trough a table, and then with and or OR, you can choose what field you want from the tables.
like so:
select table1.field1, table2.field2, table3.field3
from relationshiptable
innjer join
table1
on
table1.id = relactionshiptable.table1_id
innjer join
table2
on
table2.id = relactionshiptable.table2_id
innjer join
table3
on
table3.id = relactionshiptable.table3_id
WHERE
table1.field1 like '%something%'
or
table2.field2 like '%something%'
or
table3.field4 like '%something%'
Or somthing like that.
Hope it helps
Search in three table same time - El Forum - 07-11-2010
[eluser]brycmills[/eluser]
The following is an example that you may find useful:
Example Tables:
Code:
TBL: BREEDS
breed_id
breed_name
active
...
TBL: FAMILY
family_id
breed_id
family_name
active
...
TBL: STYLE
style_id
breed_id (if the style reflects breed specific)
family_id (if the style reflects family specific)
style_name
active
...QUERY EXAMPLE:
Code:
select * from (breeds left join family on breeds.breed_id = family.breed_id) left join style on style.breed_id = breeds.breed_idOf course, you will may want to insert this with CI $this->db
Hope this helps