Jump to content


Photo

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


  • Please log in to reply
4 replies to this topic

#1 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 30 July 2006 - 01:29 PM

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


$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)); 
}

Shishya

#2 Balmung-San

Balmung-San
  • Members
  • PipPipPip
  • Advanced Member
  • 327 posts

Posted 30 July 2006 - 01:33 PM

<brS.No&nbsp;<font size=2 face=Tahoma>%d,
Looks like you forgot to close your
<br>
tag.
"Paranoia is very useful in this work. ...If your cryptographic system can survive the paranoia model, it has at least a fighting chance of surviving in the real world." - Niels Ferguson & Bruce Schneier

They say there's more then one way to skin a cat.
In programming, there's no wrong way to skin a cat, just more efficent ways of doing so.

#3 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 30 July 2006 - 01:48 PM

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

$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 ) )

Shishya

#4 Balmung-San

Balmung-San
  • Members
  • PipPipPip
  • Advanced Member
  • 327 posts

Posted 30 July 2006 - 01:55 PM

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.
"Paranoia is very useful in this work. ...If your cryptographic system can survive the paranoia model, it has at least a fighting chance of surviving in the real world." - Niels Ferguson & Bruce Schneier

They say there's more then one way to skin a cat.
In programming, there's no wrong way to skin a cat, just more efficent ways of doing so.

#5 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 30 July 2006 - 02:07 PM

Aagh ! Then how do i do this ?

If I use

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

foreach($cidArray as $key => $value)
  if($cid == $value)

it still gives me a blank screen.  Now what ?

Thanks. Swati


Shishya




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users