Looped SQL queries with 2 variables

Question

I have a table (tags) with 3 fields, activityID, placeID, tagID (and a 4th, ID as PKey). I want to search through this table using 2 arrays, one of places and one of tags. For each match I want to return the activityID. I then want to used this activityID list in another table (activity) with each of the same array of PlaceIDs. I started to put this together as loops but I see lots of things saying not to do that. I was thinking I need to use a temp table but this also might not be required. I'm also struggling to do it with loops anyway so rather than struggle to make something which is poor practice anyway I thought I'd post the general idea to see if somebody could point me in the right direction... This code is not working but shows the general idea..EDIT... I'm only looking to resolve the loop in the first section, the second section I need to leave as a loop

$places = array("London","Madrid","Paris","Rome"); 
$tags = array("Shopping","Sight","Bar","Club");
$num_places = count($places);
$num_tags = count($tags);

/* I want to remove the loop from this section */
$counterP = 0; 
while($counterP <= ($num_places)) {
  $counterT = 0; 
  while($counterT <= ($num_tags)) {
    $conn->query('INSERT INTO temp (activityID)
    SELECT activityID, placeID
    FROM tags
    WHERE placeID = "'.$place[$counterP].'" AND tagID = "'.$tag[$counterT].'"');
  $counterT++;
  }
$counterP++;
}

/* This section will stay in a loop */
$counterP = 0; 
while($counterP <= ($num_places)) {
$sql_interests = 'SELECT a.summary, a.image, a.link
  FROM activity a 
  LEFT JOIN temp t
  ON t.activityID = a.activityID 
  WHERE a.placeID = "'.$place[$counterP].'"';

  $interests = array();
  $interests_result = $conn->query($sql_interests);
  if ( !empty($interests_result)) {
    while($interests_row = $interests_result->fetch_assoc()) {
      $interests[] = array($interests_row["summary"],$intersts_row["image"],$interests_row["link"]);
    }
    /* do stuff with data */
  }
  $counterP++;
}

can you make a sqlfiddle of the 2 or 3 tables? Someone will bang it out in mysql only, as 1 option — Drew
– Drew, Commented Nov 1, 2015 at 14:01
Then you need to specify what a "match" means. So is it London is a match, or is it (London and Shopping) or () ... etc. Parentheses matter — Drew
– Drew, Commented Nov 1, 2015 at 14:12
OK so here's an sqlfiddle with a few items in each table. sqlfiddle.com/#!9/bc650c/3 So I have an array of places and and array of tags. I want to find all the combinations in my tags table and get the accociated activity for each match. I then use this list of matched activities in a new query to get all the associated data from the 'activity' table. FYI I will need to use a loop for the second part as I will be building my HTML in sections by placeID. So it's mainly the first part... — Stuart Durning
– Stuart Durning, Commented Nov 1, 2015 at 15:08
so let's say I have 3 places and 3 tags. What is the permutation count you are expecting for a result — Drew
– Drew, Commented Nov 1, 2015 at 15:09

Drew · Accepted Answer · 2015-11-01 15:47:30Z

2

mysql only approach. The where clause filters tags with an in clause, and the join gets you to the activity table. The a and t are just aliases for either easier reading or the lazy (like me)

select a.* 
from activity a
join tags t
on t.activityID=a.activityId
where t.tagID in ('sightseeing','parks')
and t.placeID in ('Istanbul','Paris');

+----+------------+---------+---------------------------+
| ID | activityID | placeID | summary                   |
+----+------------+---------+---------------------------+
|  4 | 444        | Paris   | See Arc D'Triumph         |
|  6 | 666        | Paris   | See Eifel Tower           |
|  8 | 888        | Paris   | Walk through Central Park |
+----+------------+---------+---------------------------+
3 rows in set (0.01 sec)

answered Nov 1, 2015 at 15:47

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stuart Durning Over a year ago

Thanks @Drew (again :-) )! One key question.... can i use my arrays so its where t.tagID in $tags

Drew Over a year ago

if in PHP you create such a $sqlString with concatenation, then yes. I am not talking about PHP binding with prepared statements, but just PHP concat with .

Kiran Shakya · Accepted Answer · 2015-11-01 14:12:16Z

2

As I am currently limited to cell phone access, I can only suggest you to do following things:

Join both arrays with "','" as delimiter.
Write where clause using IN() function. Like: Where placeID IN('London','Madrid','Paris','Rome') And tagID IN(...)

This should give your required result.

answered Nov 1, 2015 at 14:12

Kiran Shakya

2,5792 gold badges28 silver badges38 bronze badges

1 Comment

Stuart Durning Over a year ago

Can I write 'WHERE placeID IN $places' ?

Collectives™ on Stack Overflow

Looped SQL queries with 2 variables

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related