doupao1530
doupao1530
2015-11-01 13:56

带有2个变量的循环SQL查询

已采纳

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++;
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douqin0676 douqin0676 6年前

    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)
    
    点赞 评论 复制链接分享
  • dqwcdqs358367 dqwcdqs358367 6年前

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

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

    This should give your required result.

    点赞 评论 复制链接分享

相关推荐