[SOLVED] Making results from table 1 display based on results from table 2

[JSHINER]

I have two tables, a field in table 1 displays data as:

 

A,B,D,E,F

 

Table 2 has fields that state: Short = A, Long = Text, Short = B, Long = Text 2

 

So basically I am trying to set up so from table 1, the A, B would not display as A, B but as: Text, Text 2.

 

Any suggestions?

[php_joe]

Something like this?

 

<?
$table1 = explode(",", "./table1.txt");
$table2 = explode(",", "./table2.txt");
echo "<tr><td>{$table1[0]} = {$table2[0]}</td></tr>";
?>

[per1os]

As to why 2 tables, I am not sure.

 

Assuming you have the data structure setup as something like this:

CREATE TABLE table_1 (
    letterid int(11) auto_increment nOT NULL,
    letter varchar(1) NOT NULL,
    primary key (letterid)
);

CREATE TABLE table_2 (
    letterdefid int(11) auto_increment NOT NULL,
    letterid INT(11) NOT NULL,
    letterdef varchar(250),
    primary key(letterdefid)
);

 

Using that this would get you what you want:

 

<?php
     $sql = "SELECT table_1.letter letter, table_2.letterdef letterdef FROM table_2 LEFT JOIN (table_2) ON (table_1.letterid=table_2.letterid) ORDER BY table_1.letter";
     $query = mysql_query($sql) OR DIE(mysql_error());
     while ($row = mysql_fetch_array($query)) {
            $lettArr[$row['letter']] = $row['letterdef'];
     }

     print $lettArr['A']; // should print "Text" if that is setup in the DB
?>

 

Hope thats what you were looking for.

[Psycho]

Well, you have not given enough informatin really. It would have been VERY helpful if you had given the column names in the tables. You don't even give the relevant field name in table 1. I think you are saying there are two fieds in Table 2 called short and long. If that is the case, you can simply write a query like this:

 

SELECT *

FROM table1

LEFT JOIN table2

 ON table1.fieldname = table2.Short

[Barand]

Here's my understanding of the problem which requires table 1 to be joined twice to table 2

 

<?php

/**
* sample data, due to lack of in the question

CREATE TABLE fixture (
    home char(1),
    away char(1),
    matchdate DATE
);

INSERT INTO fixture VALUES
('A', 'B', '2007-04-14'),
('B', 'C', '2007-04-21'),
('A', 'C', '2007-04-28');



CREATE TABLE teamname (
    team char(1) not null primary key,
    name varchar(10)
);

INSERT INTO teamname VALUES
('A', 'Team A'),
('B', 'Team B'),
('C', 'Team C');

*/

$sql = "SELECT a.name as hometeam, b.name as awayteam, f.matchdate
        FROM fixture f
        INNER JOIN teamname a ON f.home = a.team
        INNER JOIN teamname b ON f.away = b.team
        ORDER BY f.matchdate";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
echo '<pre>';
while (list ($h, $a, $d) = mysql_fetch_row($res)  )  {
      printf ('%-10s%-10s%15s<br/>', $h, $a, $d);
}
echo '</pre>';
?>

 

gives-->[pre]

Team A    Team B        2007-04-14

Team B    Team C        2007-04-21

Team A    Team C        2007-04-28

 

[/pre]

[JSHINER]

Hi I'm sorry I was not more clear. The TABLE 1 has fields that contain A,B,C,D etc... So lets say a field, "LIST", in TABLE 1 is: A,B,C,D,G

 

TABLE 2 has many referneces... Fields in TABLE 2 are: LIST, Short, Long, etc... (The LIST corresponds to the field name in TABLE 1 - so we know what abreviations go with what)

 

TABLE 2:

 

SHORT would contain A, LONG would contain "Apple".

 

So if I have a field in TABLE 1 that contains "A,B,C" how can I get it to reference the proper fields in TABLE 2 to display "Apple, Ball, Car"

 

The FIELD in TABLE 1 is called LIST - A row in TALBE 2 also has LIST so we know that abreviation goes with it.

 

 

Sorry if this is confusing... I did not create these tables, so I myself am a little lost.