First, thanks for your guy spend time to read this.
Now I have two table, and the data is below:
table name: table1
+-----------+----------+---------------------------+
| tid | area | Subject |
+-----------+----------+---------------------------+
| 1 | US | The one restaurant |
| 2 | US | Landmark hotel |
| 3 | US | Tholo restaurant |
| 4 | CA | GE bar |
+-----------+----------+---------------------------+
table name: table2
+--------+---------+---------------------------+---------------+
| tid | area | Value | optionid |
+--------+---------+---------------------------+---------------+
| 1 | US | the one restaurant desc | restaurant |
| 1 | US | the one rest. contact | recontact |
| 1 | US | the one rest. address | readdress |
| 2 | US | landmark hotel desc | hotel |
| 2 | US | landmark hotel.contact | hocontact |
| 2 | US | landmark hotel.address | hoaddress |
| 3 | US | Tholo restaurant.desc | restaurant |
| 3 | US | Tholo restaurant.contact | recontact |
| 3 | US | Tholo restaurant.address | readdress |
| 4 | CA | GE bar.desc | bar |
| 4 | CA | GE bar.contact | bacontact |
| 4 | CA | GE bar.address | baaddress |
+--------+---------+---------------------------+---------------+
I would like to show the data like below when user query area=US
|tid | Subject | description | contact | Address |area|
+-----+----------------------+-------------------------+-----------------------------+--------------------------+----+
| 1 | The one restaurant | the one restaurant desc | the one rest.contact | the one rest.address | US |
| 2 | Landmark hotel | landmark hotel desc | landmark hotel contact | landmark.address | US |
| 3 | Tholo restaurant | Tholo restaurant.desc | Tholo restaurant.contact | Thoro restaurant.address | US |
Now I only can success alias and union, can't join the subject, and my code is below:
$query = mysql_query("SELECT value AS restaurant_description FROM table2 WHERE fid = US AND optionid = restaurant UNION SELECT value AS restaurant_contact FROM table2 WHERE fid = US AND optionid = recontact UNION SELECT value AS restaurant_address FROM table2 WHERE fid = US AND optionid = readdress UNION SELECT value AS hotel FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_description FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_contact FROM table2 WHERE fid = US AND optionid = hocontact UNION SELECT value AS hotel_address FROM table2 WHERE fid = US AND optionid = hoaddress UNION SELECT value AS bar_description FROM table2 WHERE fid = US AND optionid = bar UNION SELECT value AS bar_contact FROM table2 WHERE fid = US AND optionid = bacontact UNION SELECT value AS bar_address FROM table2 WHERE fid = US AND optionid = baaddress");
while($row = mysql_fetch_array($query)) {
echo $row['hotel_description'];
echo $row['hotel_lat'];
echo $row['subject'];
}
I have try the code below, but it cant show the data i need.
$query = mysql_query("SELECT value AS restaurant_description FROM table2 WHERE fid = US AND optionid = restaurant UNION SELECT value AS restaurant_contact FROM table2 WHERE fid = US AND optionid = recontact UNION SELECT value AS restaurant_address FROM table2 WHERE fid = US AND optionid = readdress UNION SELECT value AS hotel FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_description FROM table2 WHERE fid = US AND optionid = hotel UNION SELECT value AS hotel_contact FROM table2 WHERE fid = US AND optionid = hocontact UNION SELECT value AS hotel_address FROM table2 WHERE fid = US AND optionid = hoaddress UNION SELECT value AS bar_description FROM table2 WHERE fid = US AND optionid = bar UNION SELECT value AS bar_contact FROM table2 WHERE fid = US AND optionid = bacontact UNION SELECT value AS bar_address FROM table2 WHERE fid = US AND optionid = baaddress UNION SELECT subject FROM table1 WHERE fid = US AND table1.tid = table2.tid");
Thats more like creating pivot table. If you only want recontact ,restaurant and readdress values for each you can use the following technique.
select
t1.tid,
t1.subject,
t2.area,
max(case when t2.optionid = 'restaurant' then t2.Value end) as `description`,
max(case when t2.optionid = 'recontact' then t2.Value end ) as `contact`,
max(case when t2.optionid = 'readdress' then t2.Value end ) as `Address`
from table1 t1
join table2 t2 on t1.tid = t2.tid
group by t1.tid;
$query = mysql_query("SELECT value AS restaurant_description FROM table2 WHERE `fid` ="US" AND `optionid` = "restaurant" UNION SELECT value AS restaurant_contact FROM table2 WHERE `fid` = "US" AND `optionid` = "recontact" UNION SELECT value AS restaurant_address FROM `table2` WHERE `fid` ="US" AND `optionid` = "readdress" UNION SELECT value AS hotel FROM `table2` WHERE `fid` = "US" AND `optionid` = "hotel" UNION SELECT value AS hotel_description FROM `table2` WHERE `fid` = "US" AND `optionid` = "hotel" UNION SELECT value AS hotel_contact FROM `table2` WHERE `fid` = "US" AND `optionid` = "hocontact" UNION SELECT value AS hotel_address FROM `table2` WHERE `fid` = "US" AND `optionid` = "hoaddress" UNION SELECT value AS bar_description FROM `table2` WHERE `fid` = "US" AND `optionid` = "bar" UNION SELECT value AS bar_contact FROM table2 WHERE `fid` = "US" AND `optionid` = bacontact UNION SELECT value AS bar_address FROM `table2` WHERE `fid` ="US" AND `optionid` = "baaddress" UNION SELECT subject FROM `table1` WHERE `fid` = "US" AND `table1`.tid = `table2`.tid");
first change your query like this and echo it if any error it shows than let me know