multi table results

[adzie]

Hi,

 

I'm working on script to display certain records from my db, what I need is to get the result from another table where one field matches the other.

 

if where i want the script to display the group ID from the table group where it equals the members table group entry for each member, I have it displaying the group from the members table but need it to display what that group is which is located under the groups table.

 

 

my code for this part of the script so far, any help would be much appreciated

 

 

thanks

 

 

$query = "SELECT * FROM member_locations WHERE last_update > DATE_SUB( NOW() , INTERVAL 5 MINUTE ) ORDER BY number";
$result = mysql_query($query);


$number = mysql_numrows($result);


if ($number > 0)
{

      for ($i=0; $i<$number; $i++)
        {
                  $number = mysql_result($result,$i,"number");
                  $town = mysql_result($result,$i,"town");
                  $data['county'] = mysql_result($result,$i,"county");
                  $type = mysql_result($result,$i,"type");

               $query = "SELECT * FROM members WHERE `member_number` ='$number'";
               $getnam = mysql_query($query);
               if (mysql_numrows($getnam) > 0) {
               $data['title'] = mysql_result($getnam,0,"name"); 
               $group ['group'] = mysql_result($getnam,0,"group");
                   

}
               else{$data['title'] = "No Member Number";

                    }

[samona]

You will need to use INNER JOIN.

 

For example if I have two tables:

 

User            Job

-----        --------       

UserID          JobID

UserName      JobDescription

                    UserID

 

say I have a user with has a userID of '1' and a username of 'test'.

and a JobID of '1' and JobDescription of 'phpDev' and I want to display the name of the person who is the phpDev I would write the following query:

 

Select User.UserName from User

INNER JOIN Job ON User.UserID = Job.UserID

Where Job.JobDescription = "phpDev";

 

 

That would print out Test.  I hope that makes sense.

[adzie]

anyone else want to take a shot?

 

i've tried copying the below modifying for the correct tables but to no avail

 

               $query = "SELECT * FROM members WHERE `member_number` ='$number'";
               $getnam = mysql_query($query);
               if (mysql_numrows($getnam) > 0) {
               $data['title'] = mysql_result($getnam,0,"name"); 

[adzie]

maybe i'm not making myself particularly clear

 

the first table  - members locations

 

the second table - members

 

the third table - groups

 

the script quite happily find information from the members table using information from the members locations table

 

what I need is to get information from groups based on the information from the members table

[phpQuestioner]

you need to create a JOIN.

[adzie]

I've read a snippet about join but not sure how to create it, is anyone able to point me in the right direction?

 

thanks

[adzie]

something like this? this will show both entries though wont it? I really only want to display the groups.id

 

 

$query = "SELECT members.group, groups.id".

"FROM members, groups ".

"WHERE members.group = groups.id";

 

$result = mysql_query($query);

[adzie]

i've tried the last snippet and I get this error

 

 

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in members.php on line 49

 

can anyone point me in the right direction?