[eluser]quest13[/eluser]
Hi, I have three non-relational table on which I perform a UNION operation to redirect the result query to my view.But I am not able to get all the field values other than the common one. For example,
Code: $query=$this->db->query("select productid,name,description from product UNION select serviceid,name,description from services UNION select imageid,name,description from Image;)"
$result=return $query;
results in productid,name,description as the column heading,but displays all the data(resultant of three tables combined) under it.But I get an error in my view as I checked productid,serviceid and imageid to display the data.Before I chagne my view completely, I would like to know is there any soultion in MySQL query itself to display all the data at one go ?
Any quick reply in this regard is highly appreciated.
Thanks
[eluser]jedd[/eluser]
Describe the format / content of the data that you wish to retrieve.
Provide an example.
Compare to how it differs from what you have already.
[eluser]quest13[/eluser]
I am using a view in which I display the search results of individual table initially.So I check the primary key data like Code: if(! empty ($row['productid'])){
< href ... link .. > (based on the 'productid')
}
else (! empty ($row['serviceid'])){
< href ... link .. > (based on the 'serviceid')
}
and so on...
It works fine. But In the same view I have an other option as 'ALL' , where the user can search for string from any of the table and if he finds one, then I want to retrieve data from all the three tables and display in the view at one go.But when I run the UNION query as mentioned in my previous post,I get only the column headings of first select query of UNION ( in this case only 'productid' ),so it gives a wrong link address in the view as it fails to find other ids.So my requirement is to get all the data with all the coloumns ( these tables have no relation among them ).
Hope I made it clear.
[eluser]bretticus[/eluser]
From MySQL manual (emphasis added) :
Quote:The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)
[eluser]quest13[/eluser]
Thanks, I understood it.The data type is same for all the fields I entered.I do get all column results. But only the column headings of first SELECT statement appears. How do I get all the column names ?
[eluser]bretticus[/eluser]
This is a constraint of the RDMS, not CI. The problem is that the RDMS must return rows of data under the same columns. Perhaps you can just add an extra column. For example:
Code: $query=$this->db->query("select productid,name,description,1 AS 'tbl_type' from product UNION select serviceid,name,description from services UNION select imageid,name,description,2 AS 'tbl_type' from Image", FALSE);
[eluser]quest13[/eluser]
Thank you, it solved the problem to some extent.But my requirement is little complex.The user can have many search options that includes either a search for a product name or price or size or all of them. In that case I want to retrieve the output from all three tables according to the search option performed by the user.
The UNION option just helps to display all the data.But When I want to filter out based on the Where condition, I don't think this will work out.
Any more solution to this ?
[eluser]@rno[/eluser]
Hi,
I think you should slightly change your SQL, make ik look like:
Code: SELECT productid AS 'id', name, description, 1 AS 'tble_type' FROM product
UNION
SELECT serviceid AS 'id', name, description, 2 AS 'tble_type' FROM services
UNION
SELECT imageid AS 'id', name, description, 3 AS 'tble_type' FROM image
This way you get 4 columns, all having the same name, so displaying them all at once, or separated bij 'tbl_type' wouldn't be any problem.
Regards,
Arno
|
