Querying a table for elements extracted from an array in another table.

[swatisonee]

Hi,

I have 2 tables. A field, Customerid in Table 1 contains upto 3 numbers separated by commas. Each of these numbers corresponds to a customerid in Table 2.

I now need to list all records in Table 1 which answer a query on Table 2. I thought of using  JOIN but since customerid in Table 1 is VARCHAR but in Table 2 is autincrement, I guess it would not work.

I then thought of using in_array but its throwing up an error i cannot figure out - i just get a blank page.

Could someone please tell me what i should be doing to correct this ?

Thanks. Swati


[code]$sqlb=    "SELECT * FROM `VisitReport` WHERE  (( (MONTH(`Visited_Date`) >= 4) AND (YEAR(`Visited_Date`) = $year0) ) || ( (MONTH(`Visited_Date`) <= 3) AND (YEAR(`Visited_Date`) = $year1) ) ) ORDER BY `Company` asc ";

//echo $sqlb;

$resultb = mysql_query($sqlb);

if ($myrowb = mysql_fetch_array($resultb))
{
do
{

$cid = $myrowb["Customerid"];   
$cidArray = array_unique(explode(',', $cid));

$sqla = "SELECT * FROM `Customers_List` WHERE  ((`BusinessState` LIKE 'AZ') || (`Business State` LIKE 'PA') ) ";

$resulta = mysql_query($sqla);

if ($myrowa = mysql_fetch_array($resulta))

{
$csid = $myrowa["Customerid"];
// echo $csid;


if ( in_array( $csid, $cidArray ) )

{
printf("<tr>
<brS.No&nbsp;<font size=2 face=Tahoma>%d,

//  and so on

</tr>",

  $i,
$myrowa["Company"],
  calculatedate($myrowb["Visited_Date"])  // and so on
);
}

}

}
while ($myrowb = mysql_fetch_array($resultb));
} [/code]

[Balmung-San]

[code]<brS.No&nbsp;<font size=2 face=Tahoma>%d,[/code]
Looks like you forgot to close your [code]<br>[/code] tag.

[swatisonee]

oh i did that in the original. Since i wasjust copying the snippet that was causing the probem, it must've got left out.

The critical error is in the following part i beleive but i dont know what it is .

Any ideas ?

Thanks. Swati

[code]$cidArray = array_unique(explode(',', $cid));

$sqla = "SELECT * FROM `Customers_List` WHERE  ((`BusinessState` LIKE 'AZ') || (`Business State` LIKE 'PA') ) ";

$resulta = mysql_query($sqla);

if ($myrowa = mysql_fetch_array($resulta))

{
$csid = $myrowa["Customerid"];
// echo $csid;


if ( in_array( $csid, $cidArray ) ) [/code]

[Balmung-San]

It seems like in_array does a text search, which doesn't work for numbers.

You could try traversing the array and doing a comparison on each element.

[swatisonee]

Aagh ! Then how do i do this ?

If I use

[code]$cid = $myrowb["Customerid"];   
$cidArray = array_unique(explode(',', $cid));

foreach($cidArray as $key => $value)
  if($cid == $value)[/code]

it still gives me a blank screen.  Now what ?

Thanks. Swati