Hyperlinking table field to another table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rfigley
Forum Commoner
Posts: 70
Joined: Sun Apr 21, 2002 7:10 pm

Hyperlinking table field to another table

Post by rfigley »

I'm currently using this code to display data from table "tbl_chapter".

Code: Select all

$query = ("select * from tbl_chapter");

$result = mysql_query($query) or die("Query fail");

if (mysql_num_rows($result) == 0)
{

} else
{

   echo "<B> Current Chapters List </B>";
     echo "<table border=1>";
  while($row = mysql_fetch_array($result))
    {
        echo "<P><B> " . $rowї'chapter_name'] . " - " .$rowї'county'] . "</B><BR> " . $rowї'lunch_breakfast'] . "<BR> " . $rowї'location'] . "<BR> " . $rowї'time'] . "<BR> " . $rowї'day'] . "<P>";
           }

}
I need to have the "chapter_name" field hyperlink to another table called 'tbl_members' who's "chapter_ name" is the same. So that clicking on it will take it to the page that displays those members; chapter_name_members.php.
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

try....

Code: Select all

if (isset($chapter_name)) {
$result = mysql_query("SELECT * FROM tbl_chapter WHERE chapter_name = $chapter_name") or die("Query fail"); 
} else {
$result = mysql_query("SELECT * FROM tbl_chapter") or die("Query fail"); 
}

if (mysql_num_rows($result) == 0) 
{ 

} else 
{ 

   echo "<B> Current Chapters List </B>"; 
     echo "<table border=1>"; 
  while($row = mysql_fetch_array($result)) 
    &#123; 
        echo "<P><B> " . $row&#1111;'chapter_name'] . " - " .$row&#1111;'county'] . "</B><BR> " . $row&#1111;'lunch_breakfast'] . "<BR> " . $row&#1111;'location'] . "<BR> " . $row&#1111;'time'] . "<BR> " . $row&#1111;'day'] . "<P>"; 
           &#125; 

&#125;
Edit: Fixed coding.
Image Image
rfigley
Forum Commoner
Posts: 70
Joined: Sun Apr 21, 2002 7:10 pm

Post by rfigley »

OK, I don't know what "isset" means but this looks to be comparing "chapter_name" in the same table (tbl_chapter) where I need to compare the "chapter_name" in a table called tbl_members. Also I need to hyperlink the $row['chapter_name'] of the output from tbl_chapters to the output for tbl_members which uses a similar script to out put selected fields.
fatalcure
Forum Contributor
Posts: 141
Joined: Thu Jul 04, 2002 12:57 pm
Contact:

Post by fatalcure »

Code: Select all

$query = "select * from tbl_chapter"; 
$result = mysql_query($query) or die("Query fail"); 

if (mysql_num_rows($result) &gt; 0)  { 
     echo "&lt;b&gt; Current Chapters List &lt;/b&gt;
          &lt;table border=0&gt;"; 
     while($row = mysql_fetch_array($result)) { 
          echo "&lt;tr&gt;&lt;td&gt;Chapter Name:&lt;/td&gt;&lt;td&gt;&lt;a href='chapter_name_members.php?chapter_name=" . $row&#1111;'chapter_name'] . "'&gt;" . $row&#1111;'chapter_name'] . " - " . $row&#1111;'county'] . "&lt;/td&gt;&lt;/tr&gt;
          &lt;tr&gt;&lt;td&gt;Lunch - Breakfast:&lt;/td&gt;&lt;td&gt;" . $row&#1111;'lunch_breakfast'] . "&lt;/td&gt;&lt;/tr&gt;
          &lt;tr&gt;&lt;td&gt;Location:&lt;/td&gt;&lt;td&gt;" . $row&#1111;'location'] . "&lt;/td&gt;&lt;/tr&gt;
          &lt;tr&gt;&lt;td&gt;Time:&lt;/td&gt;&lt;td&gt;" . $row&#1111;'time'] . "&lt;/td&gt;&lt;/tr&gt;
          &lt;tr&gt;&lt;td&gt;Day:&lt;/td&gt;&lt;td&gt;" . $row&#1111;'day'] . "&lt;p&gt;&lt;/td&gt;&lt;/tr&gt;"; 
     } 
     echo "&lt;/table&gt;";
} else {
     //no records found
     echo "No Records Found.";
}
then on chapter_name_members.php, just have a query like so:

Code: Select all

$query = "select * from tbl_members WHERE chapter_name=" . $_GET&#1111;'chapter_name']; 
$result = mysql_query($query) or die("Query fail");
//do whatever u want with result
rfigley
Forum Commoner
Posts: 70
Joined: Sun Apr 21, 2002 7:10 pm

Post by rfigley »

OK, although this anwers my question, I see I explained it incorrectly. The name of the php file I mentioned was retorical and it should not have been. My mistake.

Actaully the page being linked will need to be I guess actually a dynamic link. The Chapters and the members are sometimes changing. I currently have a page name for each chapter, but I'm not sure it it should stay that way. I'm thinking the page should probably be more dynamic to name the chapter template page as the chapter is created?

#
# Table structure for table `tbl_chapter`
#

CREATE TABLE tbl_chapter (
chapter_ID int(5) unsigned zerofill NOT NULL auto_increment,
county varchar(30) NOT NULL default '',
chapter_name varchar(40) NOT NULL default '',
lunch_breakfast varchar(11) NOT NULL default '',
location varchar(80) NOT NULL default '',
time varchar(20) NOT NULL default '',
day varchar(12) NOT NULL default '',
director varchar(40) NOT NULL default '',
PRIMARY KEY (chapter_ID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `tbl_members`
#

CREATE TABLE tbl_members (
members_ID int(5) unsigned zerofill NOT NULL auto_increment,
name varchar(40) NOT NULL default '',
chapter varchar(30) NOT NULL default '',
position varchar(20) NOT NULL default '',
category varchar(40) NOT NULL default '',
business_name varchar(40) NOT NULL default '',
address1 varchar(40) NOT NULL default '',
address2 varchar(40) NOT NULL default '',
city varchar(20) NOT NULL default '',
state char(2) NOT NULL default '',
zip varchar(5) NOT NULL default '',
phone varchar(12) NOT NULL default '',
fax varchar(12) NOT NULL default '',
e-mail varchar(30) NOT NULL default '',
web_address varchar(40) NOT NULL default '',
PRIMARY KEY (members_ID)
) TYPE=MyISAM;


#
# Table structure for table `tbl_user-password`
#

CREATE TABLE tbl_user-password (
id int(5) unsigned zerofill NOT NULL auto_increment,
director_name varchar(40) NOT NULL default '',
director_phone varchar(20) NOT NULL default '',
director_e-mail varchar(40) NOT NULL default '',
user varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
fatalcure
Forum Contributor
Posts: 141
Joined: Thu Jul 04, 2002 12:57 pm
Contact:

Post by fatalcure »

huh??

did u even try my above code and test it out? It does create a dynamic link for each record found in tbl_chapters, the link links to another php page called chapter_name_members.php passing a variable called $chapter_name. In chapter_name_members.php, you then use that variable to get records that have that $chapter_name, you can change the variable around if thats not the relationship for the 2 tables, but you should get the general idea.

And you dont want a PHP script created for each chapter, that would be defeating the purpose of using a databse and php. So instead of trying to create a dynamic link to the php file, have one php script and passing it variables.

I think i got what u were trying to do and posted it for you above, if not, then your 2nd explanation did nothing to help me.
Post Reply