Selecting from two tables

[Cyberspace]

I am having difficulty with displaying values from two tables.

 

My code is:

 

<?php   

$WalkNo = $_GET['WalkNo'];

$query = "SELECT * FROM walker WHERE WalkNo='$WalkNo'";

$result = mysql_query($query);
if(!$result){
   // check if is something wrong
   print "Error";
}else{
if(mysql_num_rows($result) == 0){  
     print "No Participants exist";
}else{
while ($account = @mysql_fetch_array($result)) {
    $WalkNo=$account["WalkNo"]; 
   ?>
<table class="join" cellspacing="0" width="861">

<tr>
<td class="Header">WalkNo</td>
<td class="Header">Forename</td>
<td class="Header">Surname</td>
<td class="Header">DateJoined</td>
</tr>
<tr>
<td class="Body"><?php echo $account["WalkNo"]; ?></td>
<td class="Body"><?php echo $account["Forename"]; ?></td> 
<td class="Body"><?php echo $account["Surname"]; ?></td>
<td class="Body"><?php echo $account["DateJoined"]; ?></td>


</tr> 
<br>
</table> 


<?php
  } 
}
}

?> 

 

I the code at the min selects values from a table called "walker" which stores the following fields:

 

WalkNo - number of walk the person is participating in

MemberRef - person member number

DateJoined - date member signed up for the walk programme

 

I need to get the Members "Forename" and "surname" from the Members table based on the MemberRef in the "Walks" table, the above code works ok but im not sure how to select the members name from the members table, do i need two queries?

 

 

 

 

[wildteen88]

Look into using JOIN's

[Cyberspace]

I tried the following code, but it doesnt display the forename and surname.

 

<?php   

$WalkNo = $_GET['WalkNo'];

$query = "SELECT * FROM walker WHERE WalkNo='$WalkNo'";

$result = mysql_query($query);
if(!$result){
   // check if is something wrong
   print "Error";
}else{
if(mysql_num_rows($result) == 0){ 
     print "No Participants exist";
}else{
while ($account = @mysql_fetch_array($result)) {

$query = "SELECT * FROM members WHERE MemberRef='$MemberRef'"; 

    $WalkNo=$account["WalkNo"]; 
    $MemberRef=$account["MemberRef"]
   ?>
<table class="join" cellspacing="0" width="861">

<tr>
<td class="Header">WalkNo</td>
<td class="Header">Member Ref</td>
<td class="Header">Forename</td>
<td class="Header">Surname</td>
<td class="Header">DateJoined</td>
</tr>
<tr>
<td class="Body"><?php echo $account["WalkNo"]; ?></td>
<td class="Body"><?php echo $account["MemberRef"]; ?></td>
<td class="Body"><?php echo $account["Forename"]; ?></td> 
<td class="Body"><?php echo $account["Surname"]; ?></td>
<td class="Body"><?php echo $account["DateJoined"]; ?></td>


</tr> 
<br>
</table> 


<?php
  } 
}
}

?> 

[zenag]

assign $MemberRef=$account["MemberRef"]

before $query = "SELECT * FROM members WHERE MemberRef='$MemberRef'"; like this.....

 

 

$MemberRef=$account["MemberRef"];

$query = "SELECT * FROM members WHERE MemberRef='$MemberRef'";

 

    $WalkNo=$account["WalkNo"];

   

  ?>

<table class="join" cellspacing="0" width="861">

 

<tr>

 

[Cyberspace]

assign $MemberRef=$account["MemberRef"]

before $query = "SELECT * FROM members WHERE MemberRef='$MemberRef'"; like this.....

 

 

$MemberRef=$account["MemberRef"];

$query = "SELECT * FROM members WHERE MemberRef='$MemberRef'";

 

    $WalkNo=$account["WalkNo"];

   

   ?>

<table class="join" cellspacing="0" width="861">

 

Hey thanks for the reply:

 

I have the following code but it still wont display the surname and forename from the Members table:

 

<?php   

$WalkNo = $_GET['WalkNo'];

$query = "SELECT * FROM walker WHERE WalkNo='$WalkNo'";

$result = mysql_query($query);
if(!$result){
   // check if is something wrong
   print "Error";
}else{
if(mysql_num_rows($result) == 0){ 
     print "No Participants exist";
}else{
while ($account = @mysql_fetch_array($result)) {

$MemberRef=$account["MemberRef"];
$query = "SELECT * FROM members WHERE MemberRef='$MemberRef'"; 

    $WalkNo=$account["WalkNo"]; 
   ?>
<table class="join" cellspacing="0" width="861">

<tr>
<td class="Header">WalkNo</td>
<td class="Header">Member Ref</td>
<td class="Header">Forename</td>
<td class="Header">Surname</td>
<td class="Header">DateJoined</td>
</tr>
<tr>
<td class="Body"><?php echo $account["WalkNo"]; ?></td>
<td class="Body"><?php echo $account["MemberRef"]; ?></td>
<td class="Body"><?php echo $account["Forename"]; ?></td> 
<td class="Body"><?php echo $account["Surname"]; ?></td>
<td class="Body"><?php echo $account["DateJoined"]; ?></td>


</tr> 
<br>
</table> 


<?php
  } 
}
}

?> 

 

<tr>

 

[wildteen88]

As I said earlier it'll be easier to use JOIN's. With a JOIN you can query multiple's tables at once. Can you post your table structure for the members and walkers table here and tell me what fields you want to retrieve from the two tables.

 

EDIT: Try the following code instead. I had a guess at it:

<?php

$WalkNo = $_GET['WalkNo'];

$query = "SELECT w.WalkerNo,
                 m.MemberRef,
                 m.Forname,
                 m.Surename,
                 m.DateJoined
          FROM members m,
               walker w
          WHERE m.MemberRef=w.MemberRef AND w.WalkerNo='$WalkNo'";

$result = mysql_query($query) or die('Query error!<br />Error:' . mysql_error() . 'Query: <pre>' . $query . '</pre>');

if(mysql_num_rows($result) == 0)
{
     print "No Participants exist";
}
else
{
?>
<table class="join" cellspacing="0" width="861">
  <tr>
<td class="Header">WalkNo</td>
<td class="Header">Member Ref</td>
<td class="Header">Forename</td>
<td class="Header">Surname</td>
<td class="Header">DateJoined</td>
  </tr>
<?php
    while ($account = @ysql_fetch_assoc($result))
    {
?>
  <tr>
<td class="Body"><?php echo $account["WalkNo"]; ?></td>
<td class="Body"><?php echo $account["MemberRef"]; ?></td>
<td class="Body"><?php echo $account["Forename"]; ?></td>
<td class="Body"><?php echo $account["Surname"]; ?></td>
<td class="Body"><?php echo $account["DateJoined"]; ?></td>
  </tr>
<?php
    }
?>
</table>
<?php
}
?>

[Cyberspace]

As I said earlier it'll be easier to use JOIN's. With a JOIN you can query multiple's tables at once. Can you post your table structure for the members and walkers table here and tell me what fields you want to retrieve from the two tables.

 

EDIT: Try the following code instead. I had a guess at it:

<?php

$WalkNo = $_GET['WalkNo'];

$query = "SELECT w.WalkerNo,
                 m.MemberRef,
                 m.Forname,
                 m.Surename,
                 m.DateJoined
          FROM members m,
               walker w
          WHERE m.MemberRef=w.MemberRef AND w.WalkerNo='$WalkNo'";

$result = mysql_query($query) or die('Query error!<br />Error:' . mysql_error() . 'Query: <pre>' . $query . '</pre>');

if(mysql_num_rows($result) == 0)
{
     print "No Participants exist";
}
else
{
?>
<table class="join" cellspacing="0" width="861">
  <tr>
<td class="Header">WalkNo</td>
<td class="Header">Member Ref</td>
<td class="Header">Forename</td>
<td class="Header">Surname</td>
<td class="Header">DateJoined</td>
  </tr>
<?php
    while ($account = @ysql_fetch_assoc($result))
    {
?>
  <tr>
<td class="Body"><?php echo $account["WalkNo"]; ?></td>
<td class="Body"><?php echo $account["MemberRef"]; ?></td>
<td class="Body"><?php echo $account["Forename"]; ?></td>
<td class="Body"><?php echo $account["Surname"]; ?></td>
<td class="Body"><?php echo $account["DateJoined"]; ?></td>
  </tr>
<?php
    }
?>
</table>
<?php
}
?>

 

Hey thanks for the reply

 

Table "members" has:

 

MemberRef, Forename, Surname, Address1, Address2, County, Postcode, TelNo, MobNo,WorkNo, Email

 

Table "walker" has:

 

WalkNo, MemberRef, DateJoined

[wildteen88]

I was close just needed to tweak the query. try:

<?php

$WalkNo = $_GET['WalkNo'];

$query = "SELECT w.WalkerNo,
                 m.MemberRef,
                 m.Forname,
                 m.Surename,
                 w.DateJoined
          FROM members m,
               walker w
          WHERE w.MemberRef=m.MemberRef AND w.WalkerNo='$WalkNo'";

$result = mysql_query($query) or die('Query error!<br />Error:' . mysql_error() . 'Query: <pre>' . $query . '</pre>');

if(mysql_num_rows($result) == 0)
{
     print "No Participants exist";
}
else
{
?>
<table class="join" cellspacing="0" width="861">
  <tr>
<td class="Header">WalkNo</td>
<td class="Header">Member Ref</td>
<td class="Header">Forename</td>
<td class="Header">Surname</td>
<td class="Header">DateJoined</td>
  </tr>
<?php
    while ($account = @mysql_fetch_assoc($result))
    {
?>
  <tr>
<td class="Body"><?php echo $account["WalkNo"]; ?></td>
<td class="Body"><?php echo $account["MemberRef"]; ?></td>
<td class="Body"><?php echo $account["Forename"]; ?></td>
<td class="Body"><?php echo $account["Surname"]; ?></td>
<td class="Body"><?php echo $account["DateJoined"]; ?></td>
  </tr>
<?php
    }
?>
</table>
<?php
}
?>

[Cyberspace]

I was close just needed to tweak the query. try:

<?php

$WalkNo = $_GET['WalkNo'];

$query = "SELECT w.WalkerNo,
                 m.MemberRef,
                 m.Forname,
                 m.Surename,
                 w.DateJoined
          FROM members m,
               walker w
          WHERE w.MemberRef=m.MemberRef AND w.WalkerNo='$WalkNo'";

$result = mysql_query($query) or die('Query error!<br />Error:' . mysql_error() . 'Query: <pre>' . $query . '</pre>');

if(mysql_num_rows($result) == 0)
{
     print "No Participants exist";
}
else
{
?>
<table class="join" cellspacing="0" width="861">
  <tr>
<td class="Header">WalkNo</td>
<td class="Header">Member Ref</td>
<td class="Header">Forename</td>
<td class="Header">Surname</td>
<td class="Header">DateJoined</td>
  </tr>
<?php
    while ($account = @mysql_fetch_assoc($result))
    {
?>
  <tr>
<td class="Body"><?php echo $account["WalkNo"]; ?></td>
<td class="Body"><?php echo $account["MemberRef"]; ?></td>
<td class="Body"><?php echo $account["Forename"]; ?></td>
<td class="Body"><?php echo $account["Surname"]; ?></td>
<td class="Body"><?php echo $account["DateJoined"]; ?></td>
  </tr>
<?php
    }
?>
</table>
<?php
}
?>

 

wildteen88 that worked fine, thankyou so much