How to solve 1

[I2007]

Hi...

I'm really interested in how you would solve a challenge I have...

I must search in a mysql database in a table for all records that contains a number between ie 100 and 2000.
With that result I have to check if 50 of those numbers exists in the result or not. I could always run a query for every number but I would image there's a smarter and faster way of doing it....? Are there any ways of searching for a result within a result?
i2007

 

[traingamer]

Try something like (pseudo code -not tested)

select f.field, count(f.field) as Cnt from table f
where (f.field > 100) and (F.field < 2000) and (Cnt > 49)
group by f.field
order by Cnt ;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[I2007]

Thanks, but I don't think it's useable in my case...

I have the result from the database, and lets say it's 20 numbers between 100 and 2000. But I have 50 different variables and I have to search the result with 20 numbers with the first variable and if it's in the result it will print the value. Then it searches for the next variable in that result and so on...

i2007

 

[traingamer]

Uhhh. Never mind.

 

[jpadie]

to get the result set and then compare each hit against an array of existing variables you could do this:

$result = mysql_query("select fieldname from tablename where fieldname between 100 and 2000");
while ($row = mysql_fetch_assoc($result)){
 if(in_array($row['fieldname'], $mainArray)){
    $matchedArray[] = $test;
  }
}
print_r ($matchedArray);

or this

//assume $mainArray exists
$compare = "(" . explode(",",$mainArray) . ")";
$result = mysql_query("Select fieldname from tablename where fieldname between 100 and 2000 and fieldname IN $compare");
while ($row = mysql_fetch_assoc($result)){
 echo $row['fieldname'].'<br/>\r\n';
}

[can't remember if my mysql syntax is correct - to validate try the mysql forum]

 

[I2007]

Thanks jpadie...!

The first one did it.

i2007

 

[jpadie]

no worries - the second one may be quicker though, as the heavy loading is done by mysql instead of php.