MySQL INNER JOIN Issue
Posted: Sun Jul 04, 2010 3:24 pm
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:
I've counted the items by the member_id for the page pagination...
Any help with this would be greatly appreciated!!
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();
Any help with this would be greatly appreciated!!