lansing Posted September 7, 2006 Share Posted September 7, 2006 I have [b]TableA[/b] & [b]Table[/b]B. I am quering [b]TableA[/b] for records, but only want to pull record data if the [i][b]record_id[/b][/i] from [b]TableA[/b] is in [b]TableB[/b]. I have posted other places & came to the conclusion that I need to use [i]INNER JOIN[/i] to accomplish what I need. I have posted what I have below, but this code keeps pulling data from [b]TableB[/b] & not [b]TableA[/b].To fully explain this the first time. I am taking the records from [b]TableA[/b] that are NOT in [b]TableB[/b] & displaying them in a drop down menu. I then use this data on my page.[code=php:0] mysql_select_db($db_name, $conn); $query_seminars = ("SELECT a.customers_id, a.order_id, a.order_date, a.referring_id, a.order_status FROM $order_table a INNER JOIN $commisions_table b ON (a.order_id=b.order_id) WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= a.order_date AND a.referring_id != '' AND a.order_status = 'Filled' ORDER BY a.order_id DESC"); $seminars = mysql_query($query_seminars, $conn) or die(mysql_error()); $row_seminars = mysql_fetch_assoc($seminars); $totalRows_seminars = mysql_num_rows($seminars); $colname_chosen_seminar = "-1"; if (isset($_POST['order_id'])) { $colname_chosen_seminar = (get_magic_quotes_gpc()) ? $_POST['order_id'] : addslashes($_POST['order_id']); } mysql_select_db($db_name, $conn); $query_chosen_seminar = sprintf(" SELECT * FROM $order_table WHERE order_id = %s", $colname_chosen_seminar); $chosen_seminar = mysql_query($query_chosen_seminar, $conn) or die(mysql_error()); $row_chosen_seminar = mysql_fetch_assoc($chosen_seminar); $totalRows_chosen_seminar = mysql_num_rows($chosen_seminar); ?><form action="<?php $_SERVER['PHP_SELF'] ?>" method="post" name="news_entry" id="news_entry"> <table width="40%" border="0" align="center" cellpadding="4" cellspacing="0" class="newsheader"> <tr> <td width="44%" align="left" valign="top"><div align="right"><strong>Order ID :</strong></div></td> <td width="56%" align="left" valign="top"><div align="left"> <select name="order_id" onChange="chkFrm(this)"> <option value="Choose" <?php if (!(strcmp("Choose", $_POST['customers_id']))) {echo "SELECTED";} ?>>Select Order ID</option> <?phpdo {?> <option value="<?php echo $row_seminars['order_id']?>"<?php if (!(strcmp($row_seminars['order_id'], $_POST['order_id']))) {echo "SELECTED";} ?>><?php echo $row_seminars['order_id']?></option> <?php} while ($row_seminars = mysql_fetch_assoc($seminars)); $rows = mysql_num_rows($seminars); if($rows > 0) { mysql_data_seek($seminars, 0); $row_seminars = mysql_fetch_assoc($seminars); }?> </select>[/code]I know that on the line that displays the order_id in the drop down menu it is pulling it from the first query with should make that line be like the the line below, but if I make it like that then it just shows blank drop down menu. [code=php:0]<option value="<?php echo $row_seminars['a.order_id']?>"<?php if (!(strcmp($row_seminars['a.order_id'], $_POST['order_id']))) {echo "SELECTED";} ?>><?php echo $row_seminars['a.order_id']?></option>[/code] Quote Link to comment Share on other sites More sharing options...
emehrkay Posted September 7, 2006 Share Posted September 7, 2006 while ($row_seminars = mysql_fetch_assoc($seminars)); $rows = mysql_num_rows($seminars); if($rows > 0) {this is kinda redundant and why do it when you already have $row_seminars = mysql_fetch_assoc($seminars);print_r($row_seminars) to see what is being passed Quote Link to comment Share on other sites More sharing options...
btherl Posted September 7, 2006 Share Posted September 7, 2006 Lansing, do you want entries which are in both Table A and Table B, or entries which are in Table A and NOT in Table B?If you want entries in Table A and Table B, then inner join will work. If you want entries in Table A but NOT in Table B, then you need to use this:[code]SELECT a.* FROM $order_table a LEFT OUTER JOIN $commisions_table b ON (a.user_id = b.user_id) WHERE b.user_id IS NULL[/code]This will give you all rows from Table A which does NOT match a row from Table B. This is because an outer join puts NULL in the b.user_id (and all columns from b) if no row from b was found. Quote Link to comment Share on other sites More sharing options...
lansing Posted September 7, 2006 Author Share Posted September 7, 2006 [quote author=btherl link=topic=107192.msg429690#msg429690 date=1157598105]Lansing, do you want entries which are in both Table A and Table B, or entries which are in Table A and NOT in Table B?If you want entries in Table A and Table B, then inner join will work. If you want entries in Table A but NOT in Table B, then you need to use this:[code]SELECT a.* FROM $order_table a LEFT OUTER JOIN $commisions_table b ON (a.user_id = b.user_id) WHERE b.user_id IS NULL[/code]This will give you all rows from Table A which does NOT match a row from Table B. This is because an outer join puts NULL in the b.user_id (and all columns from b) if no row from b was found.[/quote]Thanks...works great. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.