2

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?

lolbas
  • 794
  • 1
  • 9
  • 34
Kirari Akito
  • 61
  • 5

2 Answers2

1
$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)
splash58
  • 26,043
  • 3
  • 22
  • 34
  • Hello, thank you for your responds.. I got some mistake on $selected array structure. Here, i already edited it `$selected = array(1, 2, 3)` :) By the way, i tried yours, and it appears `array_merge(): Argument #1 is not an array in...` – Kirari Akito May 12 '16 at 18:23
  • in that case dont use the 1st line. just `$selected = implode(',', $selected); // 1,2,3 ` – splash58 May 12 '16 at 18:25
  • I tried it, but it appears an error `Unknown column '1' in 'where clause'`, D: – Kirari Akito May 12 '16 at 18:38
  • Here it is `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 u.id IN (1,2)` – Kirari Akito May 12 '16 at 18:44
  • `WHERE u.id u.id IN (1,2)` have to be `WHERE u.id IN (1,2)` – splash58 May 12 '16 at 18:47
  • Oops sorry my mistake, i double u.id in this comment, but in my code `WHERE u.id IN ($selected)` – Kirari Akito May 12 '16 at 18:52
  • Yep, anyway it works, i add "" in `implode("','", $selected)` and add '' in `('selected')`. Thank you ;) – Kirari Akito May 13 '16 at 00:12
0

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)";
Community
  • 1
  • 1
Thanos Darkadakis
  • 1,669
  • 2
  • 18
  • 30