mysql table help
Moderator: General Moderators
mysql table help
hi
I have 2 tables ie table 1 and table 2. Both tables has only one same column name. I am trying to make a link on the text in those columns if that text exists in columns of table 1 and table 2. For example if I have text1, text2, text3, text4, text5.........and if only text2 and text3 are common in both tables, then I want to show these texts ie text2 and text3 as links and rest only as text ie text1, text,4 and text5 as text only. hope somebody will help.
thanks
I have 2 tables ie table 1 and table 2. Both tables has only one same column name. I am trying to make a link on the text in those columns if that text exists in columns of table 1 and table 2. For example if I have text1, text2, text3, text4, text5.........and if only text2 and text3 are common in both tables, then I want to show these texts ie text2 and text3 as links and rest only as text ie text1, text,4 and text5 as text only. hope somebody will help.
thanks
Hi
Thanks for your reply. I am trying to explain you with an example.
Table1 Table2
------------------------------------------------- ---------------------------------------------
Column name | Different Column names Column name |Different Column names
_______________________________ ______________________________
text1 | ........... text1 |............
text2 | .......... text2 |...........
.................... text3 |...........
text4 |...........
................... and so on text5 |----------and so on
so its like my 2 tables with one same column name(ie Column name in the above example). text1, text2, text3,text4,text5 etc are the values in the column name fields. So you can see that text1 and text 2 are common or repeating in column name in table1 and table2.. So I want to get these text1 and text2 as links in the output and text3, text4 and text5 as only text.
this is the code I am trying to work out with. I know I have problem with the if statement but I am unable to figure it out. This below code is giving me output with all fields as links but I want to get links only if they are the same in table1 and table2. I really have no idea how to use "if" statement here to get the result which I am looking for. This column_name is not primary key.
hope it will help you to understand more clear than before.
Thanks
Thanks for your reply. I am trying to explain you with an example.
Table1 Table2
------------------------------------------------- ---------------------------------------------
Column name | Different Column names Column name |Different Column names
_______________________________ ______________________________
text1 | ........... text1 |............
text2 | .......... text2 |...........
.................... text3 |...........
text4 |...........
................... and so on text5 |----------and so on
so its like my 2 tables with one same column name(ie Column name in the above example). text1, text2, text3,text4,text5 etc are the values in the column name fields. So you can see that text1 and text 2 are common or repeating in column name in table1 and table2.. So I want to get these text1 and text2 as links in the output and text3, text4 and text5 as only text.
this is the code I am trying to work out with. I know I have problem with the if statement but I am unable to figure it out. This below code is giving me output with all fields as links but I want to get links only if they are the same in table1 and table2. I really have no idea how to use "if" statement here to get the result which I am looking for. This column_name is not primary key.
Code: Select all
$SQL0 = "SELECT column_name FROM table1 UNION SELECT column_name FROM table2";
$result0 = mysql_query($SQL0);
$numofrows2 = mysql_num_rows($result0);
echo $numofrows2. "<BR>";
while ($db_field = mysql_fetch_assoc($result0))
{
if($db_field['column_name'])
{
$db_field['column_name']="<a href='xyz.php?column_name=$db_field[column_name]'> $db_field[column_name]</a>";
print $db_field['column_name']. "<BR>";
}
else if(!$db_field['column_name']){
print $db_field['column_name']. "<BR>";
}
}Thanks
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
put it in ahi...cause of the formatting, you won't be able to see the clear table structure...
Code: Select all
block.- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Your query looks fine. Why not throw some error reporting in there so if there is a mysql error we can the error report. But by the looks of it, the union query shoiuld run without incident. The only thing I would suggest is maybe aliasing the second half of the union so that what is selected is aliased to the same field name as the select field name of the first query.
hi...I tried it but it didn't work. My query is fine, its giving me all the data from table 1 and table 2 but its making all data as links...but I want to make link on the data only which is common in both tables. I guess its something to do with "if" statement. Can you please suggest something about it?
thanks
thanks
a FULL OUTER JOIN would do the trick. But MySQL doesn't implement that.
Take a look at the result ofFor your example the result is
Take a look at the result of
Code: Select all
(
SELECT
Isnull(table2.column_name),table1.column_name
FROM
table1
LEFT JOIN
table2
ON
table1.column_name=table2.column_name
)
UNION
(
SELECT
Isnull(table1.column_name),table2.column_name
FROM
table2
LEFT JOIN
table1
ON
table1.column_name=table2.column_name
WHERE
ISNULL(table1.column_name)
)0;text1
0;text2
1;text3
1;text4
1;text5
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA