CODEWITHSUNDEEP
phpmysqlarrays
Kirari Akito
Kirari Akito

Reputation: 61

SQL JOIN result keeps repeating

In my database I have 3 tables: user, phone and number. Their structure accordingly:

ID | name
---+------
 1 | Joe
 2 | Gin
 3 | Ash


ID |   Brand   | series
---+-----------+--------
 1 |  Samsung  | s7
 2 |  Iphone   | 6s
 3 |  Samsung  | s5


ID |  number   
---+----------
 1 |  77612  
 2 |  34014  
 3 |  98271

What I want to do is select using JOIN. Here is my attempt:

$query = "SELECT u.id, p.brand, n.number
          FROM `user` u 
          LEFT OUTER JOIN `phone` p
              ON u.id = p.id
          LEFT OUTER JOIN `number` n 
              ON p.id = n.id
          WHERE u.id = '$selected'";

$sql = mysql_query($query);

if ($sql === FALSE) { 
    die(mysql_error());
}

while ($result = mysql_fetch_row($sql)) {
    $final[] = $result;
    echo '<pre>';
    print_r($final);
    echo '</pre>';
}

Where $selected is an array of arrays from form input that allows to choose what ID's to show, for example:

$selected = array(1, 3);

But the result is:

Array (
      [0] => Array (
          [0] => 1
          [1] => Samsung
          [2] => 77612
      )
    )
Array (
      [0] => Array (
          [0] => 1
          [1] => Samsung
          [2] => 77612
      )
      [1] => Array (
          [0] => 3
          [1] => Samsung
          [2] => 98271
      )
)

If we set $selected = array(1, 2, 3), the output will be the same as shown above. How can I solve this problem?

Upvotes: 2

Views: 87

Answers (2)

splash58
splash58

Reputation: 26153

$selected =  array(array(1), array(2), array(3));

// make list of id from source array
$selected = call_user_func_array('array_merge', $selected);
$selected = implode(',', $selected); // 1,2,3

and change where clause to

WHERE u.id in ($selected)

Upvotes: 1

Thanos Darkadakis
Thanos Darkadakis

Reputation: 1729

First you have to structure your ids seperated by commas. For this purpose I'm using the following function found in Easiest way to implode() a two-dimensional array?

function implode_r($g, $p) {
    return is_array($p) ?
           implode($g, array_map(__FUNCTION__, array_fill(0, count($p), $g), $p)) : 
           $p;
}

Then you have to change a little bit your query using the keyword IN:

$ids=implode_r(",",$selected);

$query = "SELECT u.id, p.brand, n.number FROM `user` u 
LEFT OUTER JOIN `phone` p ON u.id = p.id LEFT OUTER JOIN `number` n 
ON p.id = n.id WHERE u.id in ($ids)";

Upvotes: 0

Related Questions