create a query using values from an array

[svgmx5]

So my question is the following:

 

i have a form where the user can search for multiple terms (green, red, black, orange etc) the terms are seperated by a coma when the form is submitted and then those terms are placed in an array.

 

I then make for values matching those terms, using the following script:

 

//$tagArr = array(red, black);

$makeQuery = mysql_query("SELECT * FROM points_tags WHERE tag_id IN('".implode("','", $tagArr)."')");
$num_results = mysql_num_rows($makeQuery);

 

The table "point_tags" containts two columns one that has that holds a value id and the other holds the term, a value can have multiple terms so it looks kinda like this:

 

tag_id

term_id

car

red

car

black

boat

orange

plane

black

 

You get the idea right?

 

Anyway the current query is grabbing all values matching those terms in. However what i want to get done is grab the values

that have those exact terms only, so in theory something similar to the following:

 

$makeQuery = mysql_query("SELECT * FROM points_tags WHERE tag_id="term1' AND tag_id='term2'");

 

Where if i search for red and black i only get car returned because car has both the terms red and black, while plane has black it does not contain

the term red. Does that make sense?

 

I've been trying to figuring this out myself for a while already and i just can't think of a way to do this. I hope someone here can help me out

 

EDIT:::

 

What i meant to accomplish with the second query example was that i need to accomplish that same type of task (or using OR ) but while keeping the values in an array, so again in theory that (tag_id='term1' AND tag_id='term2') would just keep incrementing based on the number of values i'm searching for, so if there is only one value been searched for then there would not be any "AND".

 

The AND or OR would get added if there are more than one value.

 

Make sense what i'm talking about?

 

 

 

[scootstah]

Use OR.

 

 

$makeQuery = mysql_query("SELECT * FROM points_tags WHERE tag_id="term1' OR tag_id='term2'");

[Drummin]

Maybe something along these lines.

<?php
$tagArr = array('car' => array('red','black'),'boat' => array('red','orange','blue'),'plane' => array('red','black'));
//print_r($tagArr);
$turms="";
foreach($tagArr as $field => $values){
foreach($values as $value){
$turms.="$field='$value'";
$turms.=" OR ";
}
}
if(!empty($turms)){
$turms = substr($turms,0,-4);
//echo "$turms";
$makeQuery = mysql_query("SELECT * FROM points_tags WHERE $turms");
}
?>

[svgmx5]

@Drummin:

 

Your method worked, even though i created a smaller version of that...

 

My next problem though is that using OR i'm still getting all terms that contain either value, however when i switch to using AND i get no results

 

The problem is that i want to get results with those exact values, so again, say i have the following table and the terms i'm looking for are "red" and "black":

 

Item

term

 

car

red

 

car

black

 

boat

red

 

bike

red

 

boat

black

 

boat

pink

 

 

Like previously stated, i need to able to grab boat and car only, because only those two items contain the term red and black. Using OR i get all three items even though bike does not contain the term black. But if used AND i get nothing...

 

I have checked the table to make sure the terms i'm looking for actually exist and they do yet i get no results when using AND.

 

Any ideas?

[Drummin]

strpos() will give you a pretty quick result if string is in a string.  I based this example off your last post and added a color selection box.

<?php
//colors array for selection
$colors = array('red','black','pink');
//$colors = implode($colors);
$tagArr = array('car' => array('red','black'),'boat' => array('red','black'),'boat' => array('black','pink'),'bike' => array('red'));

if(isset($_POST['submit']) && !empty($_POST['color'])){
$turms="";
foreach($tagArr as $field => $values){
foreach($values as $value){
$found=strpos($_POST['color'],$value);
if ($found !== false){
$turms.="$field='$value'";
$turms.=" OR ";
}
}
}
if(!empty($turms)){
$turms = substr($turms,0,-4);
echo "$turms";
//$makeQuery = mysql_query("SELECT * FROM points_tags WHERE $turms");
}
}

echo "<form method=\"post\" action=\"\">\n";
echo "<select name=\"color\" />\n";
echo "<option value=''>-----</option>\n";
foreach($colors as $key => $color){
echo "<option value='$color'>$color</option>\n";	  
}
echo "</select>\n";
echo "<input type=\"submit\" name=\"submit\" value=\"Search\" />\n";
echo "</form>\n";
?>

[Drummin]

I suppose a simple IF comparison to the posted color would work instead of the strpos(), but you get the idea.

[kicken]

I have checked the table to make sure the terms i'm looking for actually exist and they do yet i get no results when using AND.

 

That is because a where clause applies only to a single row at a time.  Each individual row cannot match both 'red' and 'black'.  You have to use OR to get all rows matching either.

 

Once you have that, you can use PHP to narrow the results further.  One method would be to group the rows by type into sub arrays, they check each type to ensure it contains both values,

 

Based on your first post:

<?php
$sql = "SELECT term_id, tag_id FROM points_tags WHERE term_id IN('".implode("','", $tagArr)."')";
$res = mysql_query($sql);
$groups = array();
while ($row=mysql_fetch_array($res)){
   if (!isset($groups[$row['tag_id']])){
        $groups[$row['tag_id']]=array();
   }

  $groups[$row['tag_id']][] = $row['term_id'];
}

$hasAll=array();
foreach ($groups as $tag=>$terms){
   if ($terms == $tagArr){
      $hasAll[] = $tag;
   }
}

print_r($hasAll);
?>

 

Untested, but something like that should do what you want.

[svgmx5]

Hey again,

 

Sorry i never replied back to this post, went out on vacation and well just got back this past weekend.

 

Anyway, @kicken - i tried your method, and it worked! but!!...i'm still have some issues....

 

While @kicken methods worked for some reason it's only working when i search for two terms or less...i've tried searching for a third term or more and i just get no results at all..

 

Any ideas on why? I've been breaking my head for the past several weeks and i just can't figure it out....

 

[svgmx5]

It would help to note that not all results are actually showing up. At first i thought it was not showing up results if i searched for more than 2 terms, but after testing and playing with it more, i'm realizing that it doesn't matter the number of results, for some reason not all the results are been added to the "hasAll" array even though they match the $tagArr array.

 

Still not sure why though...

[svgmx5]

So i was able to get this to work, all i had to do was the following changes

 

<?php
$sql = "SELECT term_id, tag_id FROM points_tags WHERE term_id IN('".implode("','", $tagArr)."')";
$res = mysql_query($sql);
$results = mysql_num_rows($res);
if($results !=0){
$groups= array();
$h = 0;
while ($row=mysql_fetch_assoc($res)){
   if (!isset($groups[$row['tag_id']])){
        $groups[$row['tag_id']]=array();
   }

  $groups[$row['tag_id']][] = $row['term_id'];
}

$hasAll=array();
sort($tagArr);
foreach ($groups as $tag=>$terms){
  sort($terms);
   if ($terms == $tagArr){
      $hasAll[] = $tag;
      $h++
   }
}
}
?>

 

Thanks to everyone for the help!