如何通过外键从两个不同的表中获取所有字段

Question

I have a query as part of this function:

function user_data($MemberID){ //pass in memberid to get info about user
        $data = array();//data to be returned
        $MemberID =(int)$MemberID;//creating int from this input

        $func_num_args = func_num_args(); //count number of arguments from user data on init.php
        $func_get_args = func_get_args();

        if ($func_num_args >1) { //if more then 1, unset the first element of array 
            unset($func_get_args[0]);

            $fields = '`' . implode('`,`', $func_get_args) . '`';   //taking array and converting to string
            $data = mysql_fetch_assoc(mysql_query("SELECT $fields FROM `member`,`oddjob` WHERE `MemberID` = $MemberID"));

                return $data;

I call this function (user_data) in the users profile page so that it shows some info about them. However now I also need to be able to display data from another table called oddjob.

The MemberID is the Primary key in the member table. MemberID is the foreign key in oddjob table. OddJobID is the primary key in the oddjob table.

I need to edit the query above to pull all fields from both tables based on the MemberID. Ive tried:

$data = mysql_fetch_assoc(mysql_query("SELECT $fields FROM `member` INNER JOIN `oddjob` ON `member.MemberID` = `oddjob.MemberID` WHERE `MemberID` = $MemberID"));

But keep getting the following error when I log into the users profile page;

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given

Any help here would be great. Thanks.

Answer 1

Surely someone will tell you not to use the mysql functions, but instead use mysqli or pdo. your query could look like this tho

select *
from   member m,
       oddjob o
where  m.MemberID = o.MemberID
and    m.MemberID = $MemberID