Linking tables to display data?

[Garry Jones]

I want to link two tables together to create a list. The unique value
loppnum appears in both tables. I need to look up its corresponding value
for each entry.

table1 has 2 cols
A Jag
D Cht
E Lio
P Mkn
Q Res

table2 has 2 cols
MN A
PL D
RT E
PO P
LM D
NB D

In the first table both values are unique
In the second table the first value is Unique

Now I need to display a list of data in this form

MN Jag (Displays second table data and looks up value of A in first table)
PL Cht (Displays second table data and looks up value of D in first table)
RT Lio (Displays second table data and looks up value of E in first table)
PO Mkn (Displays second table data and looks up value of P in first table)
LM Cht (Displays second table data and looks up value of D in first table)
NB Cht (Displays second table data and looks up value of D in first table)

I have made a start

$upgcollect='SE LECT * FROM table1 ORDER BY scfmfor';
$upg1=mysql_que ry($upgcollect) ;

$ml_collect='SE LECT * FROM table2 ORDER BY loppnmn';
$upg2=mysql_que ry($ml_collect) ;

That gives me two variables with complete table data.
The rest of this is beyond me at the moment.

Can someone please point me in the right direction.

Garry Jones
Sweden

[Rik]

Re: Linking tables to display data?

Garry Jones wrote:

I want to link two tables together to create a list. The unique value
loppnum appears in both tables. I need to look up its corresponding
value for each entry.
>
table1 has 2 cols
A Jag
D Cht
E Lio
P Mkn
Q Res
>
table2 has 2 cols
MN A
PL D
RT E
PO P
LM D
NB D
>
In the first table both values are unique
In the second table the first value is Unique
>
Now I need to display a list of data in this form
>
MN Jag (Displays second table data and looks up value of A in first
table) PL Cht (Displays second table data and looks up value of D in
first table) RT Lio (Displays second table data and looks up value of
E in first table) PO Mkn (Displays second table data and looks up
value of P in first table) LM Cht (Displays second table data and
looks up value of D in first table) NB Cht (Displays second table
data and looks up value of D in first table)
>
I have made a start
>
$upgcollect='SE LECT * FROM table1 ORDER BY scfmfor';
$upg1=mysql_que ry($upgcollect) ;
>
$ml_collect='SE LECT * FROM table2 ORDER BY loppnmn';
$upg2=mysql_que ry($ml_collect) ;
>
That gives me two variables with complete table data.
The rest of this is beyond me at the moment.
>
Can someone please point me in the right direction.

SELECT
x.field1, y.field2
FROM table1 x
LEFT JOIN table2 y
ON x.field2 = y.field1

Grtz,
--
Rik Wasmus

[Rik]

Re: Linking tables to display data?

Rik wrote:

SELECT
x.field1, y.field2
FROM table1 x
LEFT JOIN table2 y
ON x.field2 = y.field1

Euhm, switch around tablenames offcourse:
SELECT
x.field1, y.field2
FROM table2 x
LEFT JOIN table1 y
ON x.field2 = y.field1

Grtz,
--
Rik Wasmus

[Garry Jones]

Re: Linking tables to display data?

I tried to follow your advice. Not very well I am afraid. These are my
actual table names, field names and the resulting error.

<?
(code to open database)
$upgcollect='SE LECT * FROM scfmforening ORDER BY scfmfor';
$upg=mysql_quer y($upgcollect);
$ml_collect='SE LECT * FROM ml_lopp ORDER BY loppnmn';
$ml_upg=mysql_q uery($ml_collec t);
$num_ml=mysql_n umrows($ml_upg) ;
mysql_close();

SELECT;
$upg1.scfmnumm, $num_ml.scfmnum ;
FROM scfmforening $upg1;
LEFT JOIN ml_lopp $num_ml;
ON $upg1.scfmfor = $num_ml.loppnmn ;

echo '<table>';
$i = 0;
while i < $num_ml{
$scfmnum=mysql_ result($ml_upg, $i,"scfmnum");
$loppnmn=mysql_ result($ml_upg, $i,"loppnmn");
echo '<tr><td width="225">';
print $loppnmn;
echo '</td><td width="225">';
print upg1.scfmfor;
echo '</td><td width="50">';
print $scfmnum;
echo '</td></tr>';
$i++;
}
echo '</table>';

?>

This give this error

Parse error: syntax error, unexpected T_VARIABLE on line 10

Line 10 is
$upg1.scfmnum, $num_ml.scfmnum

Any idea what I am not grasping?

Garry Jones
Sweden

[Garry Jones]

Re: Linking tables to display data?

DUH!

Ok, I am starting to grasp this now.

A little bit nearer since my last effort.

$ml_collect='SE LECT * FROM ml_lopp LEFT JOIN scfmforening.sc fmfor ON
ml_lopp.scfmnum = scfmforening.sc fmnum ORDER BY loppnmn';
$ml_upg=mysql_q uery($ml_collec t);
$num_ml=mysql_n umrows($ml_upg) ;

But that is misfiring and not picking up any rows.

Can you see why?

Garry Jones
Sweden

[Rik]

Re: Linking tables to display data?

Garry Jones wrote:

DUH!
>
Ok, I am starting to grasp this now.
>
A little bit nearer since my last effort.
>
$ml_collect='SE LECT * FROM ml_lopp LEFT JOIN scfmforening.sc fmfor ON
ml_lopp.scfmnum = scfmforening.sc fmnum ORDER BY loppnmn';
$ml_upg=mysql_q uery($ml_collec t);
$num_ml=mysql_n umrows($ml_upg) ;
>
But that is misfiring and not picking up any rows.
>
Can you see why?

First of all, don't use SELECT *, use the actual fieldnames you want.
Second of all is that you JOIN a table, not a table field.

If I understand your table fields correctly:

SELECT x.`scfmnum`,y.` loppnmn`
FROM `ml_lopp` x
LEFT JOIN `scfmforening` y
ON x.`scfmnum` = y.`scfmnum`
ORDER BY x.`loppnmn`

Grtz,
--
Rik Wasmus

[Garry Jones]

Re: Linking tables to display data?

"Rik" <luiheidsgoeroe @hotmail.comskr ev i meddelandet
news:3738b$4538 401e$8259c69c$1 1497@news1.tude lft.nl...

Largely based on your answer I did a lot of testing and finally arrived at

$ml_collect='SE LECT * FROM ml_lopp LEFT JOIN scfmforening
ON(scfmforening .scfmnum=ml_lop p.scfmnum)';

Which works fine. (I needed the * as this was part of a larger list and
thought this answer may help some other baffled person in the future).

Put simply the code I am using is

SELECT *
FROM tabell1
LEFT JOIN tabell2
ON(tabell1.fiel d=tabell2.match ingfield);

I can then access all values in table1 and for the matching field all values
in the corresponding record of the other table.

Thanks for your help

Garry Jones
Sweden