How to SELECT multiple key/value pairs where the key is constant?

Question

I have two tables, which I'll call foo and bar. Each table has a primary key, foo_id and bar_id, respectively. Each row in foo can be part of a group, which is a row in bar - think members on a committee, each member can be on multiple committees and each committee can have multiple members. I have a joining table which can be called foo_bar, which has only two columns (one each for the primary key of the other two tables) and lists which foo is part of which bar:

| foo_id | bar_id |
| ------ | ------ |
|   test |    res |
|   test |   res2 |
| --------------- |

Here is my PHP (due to my webhost I am unable to use $stmt->get_result()):

$stmt = $db->prepare("SELECT bar_id FROM foo_bar WHERE foo_id = ?");

$stmt->bind_param("s", "test");
$stmt->execute();
$stmt->bind_result($result);
$stmt->fetch();
$stmt->close();

Right now, printing $result gives me "res". How do I make it so that my result is all of the bars that the test foo is part of?

SQL Fiddle: http://sqlfiddle.com/#!9/2dfd2/1

Answer 1

you can use mysqli something like this :

$mysqli = new MySQLi(SERVER, USERNAME, PASSWORD, DATABASE);  

$query = $mysqli->query("SELECT bar_id FROM foo_bar WHERE foo_id = 'test'");

  while ($row = $query->fetch_assoc()) {
                $results[] = $row;
            }

the $results will have all the bar ids with foo id equal to "test".

Answer 2

You are binding parameters for each call of $stmt-fetch();

call $stmt->fetch() again (before $stmt->close()) and $result will be "res2"

You can do this in a loop and put all of $result in an array.

Alternatively, you can do

$result = $stmt->get_result();
$rows = $result->fetch_all(MYSQLI_ASSOC); 

$rows will then be all the result rows from the query as associative arrays. You can then map this to an array

function getBarID($row) {
  return $row["bar_id"];
}

$bar_ids = array_map("getBarID",$rows);