Page 1 of 1

MySQL INNER JOIN Issue

Posted: Sun Jul 04, 2010 3:24 pm
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!!

Re: MySQL INNER JOIN Issue

Posted: Sun Jul 04, 2010 6:39 pm
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.

Re: MySQL INNER JOIN Issue

Posted: Mon Jul 05, 2010 7:30 am
by Luke Warm
I'm getting everything except the firstname and lastname fields from the members table.

Re: MySQL INNER JOIN Issue

Posted: Mon Jul 05, 2010 1:15 pm
by mikosiko
post your "members" table structure.... maybe your fields are not named "firstname" and "lastname" ?

Re: MySQL INNER JOIN Issue

Posted: Mon Jul 05, 2010 1:54 pm
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

Re: MySQL INNER JOIN Issue

Posted: Mon Jul 05, 2010 2:08 pm
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.