Using data in current query to get more data from same table

[aztec]

Hello

I am using XAMPP 2.5.8

PHP 5.10

MySql 5.1.37

Apache 2.2.12

 

The following code with no error testing is used to test my database and returns the expected results. The $page for the test is hard coded.

What I need to do is to use the "fid" data (which is a number and links to the id field) that is returned on the first row of the query to find another id and the associated data row.

 

If it is possible can anyone please show me an example how this can be done.

 

Kind Regards

 

<?php
  // connect to database
  mysql_connect("localhost","USER","PW");
mysql_select_db("DB");
$page =1;
  $a_result = @mysql_query("SELECT id, first_name, surname, fid FROM TABLE WHERE id='$page' or link_to_spouse='$page'
or fid= '$page' ");

  
  if ($a_result) {
    if (mysql_num_rows($a_result) > 0) {
      // here we loop through each member in the database
      while ($row = mysql_fetch_assoc($a_result)) {
      
        echo $row['id'];
        echo $row['first_name'];
        echo $row['surname'];
      }
    } 
  }
  
php?>

[fenway]

The fid links back to the same table? Then self-join

[aztec]

Thanks for your reply fenway.

 

I am now searching for a way to implement your suggestion which is not easy never having used joins before. I have looked at the Manuel for an explanation of joins but at this time in my learning process it is beyond my comprehension. Is there "model code" that I could look at to help me understand what a self_join is and how to code it for my query.

 

Kind Regards

[fenway]

SELECT * FROM TABLE 
INNER JOIN TABLE AS TABLE2 ON ( TABLE2.id = TABLE.fid )
WHERE TABLE.id='$page' or TABLE.link_to_spouse='$page'
or TABLE.fid= '$page'

 

Of course, you'll need to decide which fields you want, and if there's always a non-NULL & matching fid or not.

 

Also, don't use "table" as your table name.

[aztec]

Thanks fenway

 

Having looked through and tried your code with only the table name changed it does indeed produce the data for the fid link. In it's present format the code lost some data but I am working on recoding to get it back.

 

Am I right in thinking that the inner join produces a "virtual" table that can be searched. If this is correct then what fields would be in the "virtual" table.

 

I know that these are basic questions but it would help me get a better understanding of the workings of joins.

 

Kind Regards

[fenway]

Sorry, I don't follow.