Page 1 of 2

mysql table help

Posted: Mon Dec 25, 2006 11:43 am
by paul_20k
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

Posted: Mon Dec 25, 2006 12:51 pm
by volka
I do not undestand. Can you please provide sample records (and expected results for these records)?

Posted: Mon Dec 25, 2006 1:46 pm
by paul_20k
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.

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>";
	 }
}
hope it will help you to understand more clear than before.

Thanks

Posted: Mon Dec 25, 2006 1:49 pm
by paul_20k
hi...cause of the formatting, you won't be able to see the clear table structure...its like text1 and text2 under field name(column_name) of table1 and text1,text2,text3,text4,text5 are under field name(column_name) of table2. Hope to get some help soon.

Thanks

Posted: Mon Dec 25, 2006 3:14 pm
by Ollie Saunders
hi...cause of the formatting, you won't be able to see the clear table structure...
put it in a

Code: Select all

block.

Posted: Mon Dec 25, 2006 3:44 pm
by paul_20k
hi
first I tried with code..but it didn't work properly so I re enterd as it is now.

thanks

Posted: Mon Dec 25, 2006 5:04 pm
by RobertGonzalez
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.

Posted: Mon Dec 25, 2006 5:32 pm
by paul_20k
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

Posted: Mon Dec 25, 2006 6:02 pm
by volka
a FULL OUTER JOIN would do the trick. But MySQL doesn't implement that.
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)
)
For your example the result is
0;text1
0;text2
1;text3
1;text4
1;text5

Posted: Mon Dec 25, 2006 6:20 pm
by paul_20k
Hi Volka

I am getting the results with my query also but my problem is I want to make links for the common values from the table1 and table2.

thanks

Posted: Mon Dec 25, 2006 7:05 pm
by volka
Please take a closer look at the 0 and 1 values.
0;text1
0;text2
1;text3
1;text4
1;text5

Posted: Mon Dec 25, 2006 7:37 pm
by paul_20k
Thanks so much Volka...I got it....:)

Posted: Tue Dec 26, 2006 9:29 am
by paul_20k
Hi Volka

The query is working as expected. How can I retrieve other values from table1 only with some other WHERE condition? Can I use another UNION?

Thanks

Posted: Tue Dec 26, 2006 11:00 am
by RobertGonzalez
Can you try adding a where clause?

Posted: Tue Dec 26, 2006 11:56 am
by paul_20k
hi

I tried all thats why I asked that question. I am trying to retrieve other column name values from table1 only. I don't need anything else from table2.

thanks