CODEWITHSUNDEEP
phpmysqljoin
Lairds
Lairds

Reputation: 87

How to get all fields from two different tables by foreign key

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.

Upvotes: 0

Views: 178

Answers (2)

Vivek
Vivek

Reputation: 329

PDO's are the way moving forward, Go for PDO_MYSQL - http://www.php.net/manual/en/ref.pdo-mysql.php

Upvotes: 0

NappingRabbit
NappingRabbit

Reputation: 1918

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

Upvotes: 1

Related Questions