MySQL INNER JOIN Issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Luke Warm
Forum Newbie
Posts: 11
Joined: Fri Nov 13, 2009 9:42 am

MySQL INNER JOIN Issue

Post by Luke Warm »

Guys,

I am having an issue getting my INNERJOIN to work in php/mysql code. I have two table within my database (members and order). The common fields are the member_id field in both tables. I can't seem to pull the firstname and lastname fields from members into a display table.

Here's my query: "SELECT * FROM members INNER JOIN orders ON members.member_id=orders.member_id".

Here's where I want to display the data:

Code: Select all

// show data in table
 $html .= "<table align=\"left\" border=\"1\" cellspacing=\"0\" cellpadding=\"2\" width=\"1000\" style=\"margin:2px 0 0 -1px;border:1px solid #ddd;font-size:.8em;background:#fff;color:#000;-webkit-box-shadow: 2px 4px 12px #000;text-shadow:none;\" >"; 
 $html .= "<th colspan='2'></th><th align='center'>Member ID</th><th align='center'>First Name</th><th align='center'>Last Name</th><th align='center'>Order Number</th><th align='center'>Title</th><th align='center'>Author</th><th align='center'>Date Ordered</th><th align='center'>Price</th>";

while ($row = mysql_fetch_assoc($q)) {
	$html .= "<tr><td align='center' width='3%'><a href='edit_order.php?id=" . $row['member_id'] . "'>Edit</a></td>";
	$html .= "<td align='center' width='3%'><a href='delete_order.php?id=" . $row['member_id'] . "'>Delete</a></td>";
        $html .= "<td valign='middle' width='3%' align='center'>$row[member_id]</td>";
        $html .= "<td valign='middle' width='10%' align='center'>$row[firstname]</td>";
        $html .= "<td valign='middle' width='10%' align='center'>$row[lastname]</td>";
        $html .= "<td valign='middle' width='3%' align='center'>$row[OrderNo]</td>";
        $html .= "<td valign='middle' width='10%' align='center'>$row[title]</td>";
        $html .= "<td valign='middle' width='10%' align='center'>$row[author]</td>";
        $html .= "<td valign='middle' align='center' width='7%'>". date("l F j, Y   g:ia",strtotime($row['date_ordered'])) ."</td>";

   if ($row['price'] == 0) {
	$row['price'] = "<font color='red'>Free</font>";
   }
   else {
   	$row['price'] = "$" . $row['price']."";
   }

        $html .= "<td valign='top' align='center' width='6%'>$row[price]</td></tr>";
       }
 echo "</table>";

for ($k=0; $k<$pages; $k++) {
        if ($k != $_REQUEST[page]) {
         $lynx .= "<a href=$PHP_SELF"."?page=$k><input type='button' value='".($k+1)."' onclick=$PHP_SELF"."?page=$k></a>";
        } else {
         $lynx .= "<input type='button' value='".($k+1)."' style='color:#c0c0c0;'></b>";
        }
}

  mysql_close();
I've counted the items by the member_id for the page pagination...

Any help with this would be greatly appreciated!!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL INNER JOIN Issue

Post by califdon »

What are you getting? That SQL should return all columns from both tables for rows where there is a row in each table with the same member_id. Do you have rows that meet that condition? Remember, it won't return any data at all except for those that match in both tables.
Luke Warm
Forum Newbie
Posts: 11
Joined: Fri Nov 13, 2009 9:42 am

Re: MySQL INNER JOIN Issue

Post by Luke Warm »

I'm getting everything except the firstname and lastname fields from the members table.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MySQL INNER JOIN Issue

Post by mikosiko »

post your "members" table structure.... maybe your fields are not named "firstname" and "lastname" ?
Luke Warm
Forum Newbie
Posts: 11
Joined: Fri Nov 13, 2009 9:42 am

Re: MySQL INNER JOIN Issue

Post by Luke Warm »

Here's the table structure from phpMyAdmin:

Field Type Collation Attributes Null Default Extra
member_id int(11) UNSIGNED No None auto_increment
firstname varchar(100) latin1_swedish_ci Yes NULL
lastname varchar(100) latin1_swedish_ci Yes NULL
email varchar(100) latin1_swedish_ci No None
login varchar(100) latin1_swedish_ci No
passwd varchar(32) latin1_swedish_ci No
status varchar(12) latin1_swedish_ci No None
items_purchased int(50) UNSIGNED Yes NULL
items_ordered int(50) UNSIGNED Yes NULL
items_backordered int(50) UNSIGNED Yes NULL
balance decimal(8,2) UNSIGNED Yes NULL
timestamp timestamp on update CURRENT_TIMESTAMP No CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL INNER JOIN Issue

Post by califdon »

SELECT * FROM members INNER JOIN orders ON members.member_id=orders.member_id
I think that asterisk (*) should retrieve all columns, but you might try
SELECT members.*, orders.*...
to be sure.

In any case, if you are seeing the other columns of the appropriate records, it is not an inner join issue, it is something in your handling of the data that is returned by the query.
Post Reply