Page 1 of 1

Checking if records exist in 2 diff tables using unique id's

Posted: Wed May 25, 2005 2:33 pm
by wthero
Hi All -

Much obliged in advance for any help. Here's what I'm trying to do:

I have 2 data tables each with different fields but both using the same values for the primary keys, establishing a one to one relationship between the two. The first table "dealer_list" contains records for every authorized dealer for my company and the second table "dealer_ind" contains more specific information, but only for some, not all, of the dealers in the the "dealer_list" table. I have a page that dynamically displays the general information for all our dealers from the "dealer_list" table and I am attempting to attach a hyperlink to the dealer name for records that also have data in the "dealer_ind" table which will send the user to a new page with more detailed information by passing the primary key value, or simply display the dealer's name if not. Here's the code I'm currently working with, although I've tried a few other things to no avail as well.

Code: Select all

function SqlData($p_value)
			{
			//$returnvalue = mysql_real_escape_string($p_value);
			$returnvalue = $p_value;
			return $returnvalue;
			}
$result = mysql_query("SELECT * FROM dealer_list WHERE dlr_state=\"".SqlData($_GET["item_abbv"])."\" ORDER BY dlr_city, dlr_name",$db);
<? while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { ?>
              <tr class="bodysmall">
                <td height="26" align="left">
				  <? if (in_array($row["dlr_number"],array("SELECT ind_dlrnum FROM dealer_ind"))) { ?>
				    <a href="dealers_ind_detail.php?item_dlrnum=<? echo($row["dlr_number"]); ?>" target="_blank"><? echo($row["dlr_name"]); ?></a>
				    <? }
				  } 
				  else {
				    echo($row["dlr_name"]);
				    <? } ?>
				  <? } ?>
				</td>
                <td align="left"><? echo($row["dlr_address"]); ?></td>
                <td align="left"><? echo($row["dlr_city"]); ?></td>
                <td align="left"><? echo($row["dlr_state"]); ?></td>
                <td align="left"><? echo($row["dlr_zip"]); ?></td>
                <td align="left"><? echo($row["dlr_phone"]); ?></td>
			  </tr>
            <? } ?>
Does anyone have any suggestions on another approach? Thanks!

Posted: Thu May 26, 2005 6:41 am
by artexercise
First I'd wonder why you have the php tag <? starting at while instead of before the function. Looks like php code falling outside of delimiters.

Also array("SELECT ind_dlrnum FROM dealer_ind") in line 10. At first glance it appears as though you are searching through a mysql result to match $row["dlr_number"]. But I think you are searching instead through a single item array that contains the string "SELECT ind_dlrnum FROM dealer_ind". Which means it isn't going to do a thing.

Right off the bat I'd make sure that all my php code is enclosed in the right tags. And create your arrays first. One arary contains all pertinent items from the dealer_list table and the other array contains all id numbers from the dealer_ind table. Use that second array in your in_array statement.

JOE--

Posted: Thu May 26, 2005 10:14 am
by pickle
I think a JOIN statement might work here. I'm no guru so I can't just spit out a working query, but something similar to this might work:

Code: Select all

SELECT
   dl.*,
   count(di.id) as 'count'
FROM
   dealer_list as dl
LEFT JOIN
   dealer_ind as di
ON
   dl.id = di.id
WHERE
   dl.slr_state = 'some state'
GROUP BY
   dl.delr_name
ORDER BY
   dl.dlr_city,
   dl.dlr_name
I'm assuming some fields here, but this should give you a result set with all dealers and a count (likely either 1 or 0) which indicates if they have extra data in the dealer_ind field.

Posted: Thu May 26, 2005 1:18 pm
by wthero
Thanks for the input folks. The JOIN statement was just the ticket. I was overthinking this thing.