KillGorack Posted December 26, 2017 Share Posted December 26, 2017 (edited) Hi, Here is my SQL, its build with a loop, and looks correct to what I think it should be. I believe the output "$data" below should only be 4 records.. though its a lot more, and some data is duplicated and shown twice in the array. Lot of stuff removed for simplicity. SELECT usr.usr_login, app.app_name, acs.acs_read, acs.acs_add, acs.acs_edit, acs.acs_delete, acs.acs_admin FROM acs LEFT JOIN usr ON usr.ID = usr.ID LEFT JOIN app ON app.ID = app.ID if it matters here's the code that generated the statement above from an array. <?php // ===================================================================================================== // preperation // ===================================================================================================== foreach($fldarray as $fld){ // get readable names and stick em into an array $nm[] = $fld['human']; // Field name, and joins (if the join is using the option table) if($fld['opt'] == 1 and ($fld['opt_table'] == "" or $fld['opt_field'] == "")){ $jn[] = array('table' => "opt", 'field' => "opt_value"); $fd[] = "opt.opt_value"; // Field name, and joins (if the join is using a table other than options) }elseif($fld['opt'] == 1 and ($fld['opt_table'] <> "" and $fld['opt_field'] <> "")){ $jn[] = array('table' => $fld['opt_table'], 'field' => $fld['opt_field']); $fd[] = $fld['opt_table'].".".$fld['opt_field']; // a field directly written no foreign keys.. }else{ $fd[] = $ap.".".$fld['fieldname']; } $sa[] = array('human' => $fld['human'], 'field' => $fld['fieldname'], 'opt_table' => $fld['opt_table'], 'opt_field' => $fld['opt_field']); } // ===================================================================================================== // Start building the SQL // ===================================================================================================== $sql = "SELECT ".implode(", ", $fd)." FROM ".$ap; // ===================================================================================================== // Add the joins if any.. // ===================================================================================================== if (isset($jn)){ foreach($jn as $j){ $sql .= " LEFT JOIN ".$j['table']." ON ".$j['table'].".ID = ".$j['table'].".ID"; } } // ===================================================================================================== $stmt = $db->query($sql); $data = $stmt->fetchAll(); echo $sql; echo "<pre>"; print_r($data); echo "</pre>"; // ===================================================================================================== ?> Tables acs ID | acs_usr | acs_app | acs_read | acs_add | acs_edit | acs_delete | acs_admin ======================================================================================== 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 1 | 1 | 1 | 1 | 0 | 1 | 1 | 0 app ID | app_name ============= 1 | Applications 2 | Users usr ID | login ============= 1 | joe 2 | fred 3 | carlie 4 | lisa outputs this array.. Array ( [0] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [1] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [2] => Array ( [usr_login] => fred [0] => fred [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [3] => Array ( [usr_login] => fred [0] => fred [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [4] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [5] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [6] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [7] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [8] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [9] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [10] => Array ( [usr_login] => fred [0] => fred [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [11] => Array ( [usr_login] => fred [0] => fred [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [12] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [13] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [14] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [15] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [16] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [17] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [18] => Array ( [usr_login] => fred [0] => fred [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [19] => Array ( [usr_login] => fred [0] => fred [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [20] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [21] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [22] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [23] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Permissions [1] => Permissions [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [24] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [25] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [26] => Array ( [usr_login] => fred [0] => fred [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [27] => Array ( [usr_login] => fred [0] => fred [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [28] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [29] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [30] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [31] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Benchmark [1] => Benchmark [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [32] => Array ( [usr_login] => Joe [0] => Joe [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [33] => Array ( [usr_login] => Joe [0] => Joe [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [34] => Array ( [usr_login] => fred [0] => fred [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [35] => Array ( [usr_login] => fred [0] => fred [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [36] => Array ( [usr_login] => carlie [0] => carlie [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [37] => Array ( [usr_login] => carlie [0] => carlie [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [38] => Array ( [usr_login] => lisa [0] => lisa [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [39] => Array ( [usr_login] => lisa [0] => lisa [app_name] => News [1] => News [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [40] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [41] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [42] => Array ( [usr_login] => fred [0] => fred [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [43] => Array ( [usr_login] => fred [0] => fred [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [44] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [45] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [46] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [47] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Passwords [1] => Passwords [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [48] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [49] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [50] => Array ( [usr_login] => fred [0] => fred [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [51] => Array ( [usr_login] => fred [0] => fred [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [52] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [53] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [54] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [55] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Fields [1] => Fields [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [56] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [57] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [58] => Array ( [usr_login] => fred [0] => fred [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [59] => Array ( [usr_login] => fred [0] => fred [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [60] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [61] => Array ( [usr_login] => carlie [0] => carlie [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) [62] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [63] => Array ( [usr_login] => lisa [0] => lisa [app_name] => Settings [1] => Settings [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) ) Edited December 26, 2017 by KillGorack Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/ Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 Never mind... trying to join a table to itself.. Sorry for the clutter.. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554890 Share on other sites More sharing options...
Barand Posted December 26, 2017 Share Posted December 26, 2017 Yes, you should be joining using the id of one table and its corresponding foreign key in another. +--------+ +---------+ | usr | | app | +--------+ +---------+ | ID |----+ +-----| ID | | login | | +----------+ | | app_name| +--------+ | | acs | | +---------+ | +----------+ | | | ID | | +----<| acs_usr | | | acs_app |>--+ | etc | +----------+Do you have a particular reason for using LEFT JOIN - that will list all records in acs table regardless of whether there is a matching record in other table? Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554891 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 (edited) Yes, the goal is to get all the records out in a table view. Structure for this example lends itself to requiring the user and app fields to have matching records. <edit> neglected to include the corrected statement; SELECT usr.usr_login, app.app_name, acs.acs_read, acs.acs_add, acs.acs_edit, acs.acs_delete, acs.acs_admin FROM acs LEFT JOIN usr ON usr.ID = acs.acs_usr LEFT JOIN app ON app.ID = acs.acs_app </edit> OK now the array from the table looks like it has the correct amount of records.. however the array looks funny to me. It looks like.. Array ( [0] => Array ( [usr_login] => Joe [0] => Joe [app_name] => Applications [1] => Applications [acs_read] => 1 [2] => 1 [acs_add] => 1 [3] => 1 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 1 [6] => 1 ) [1] => Array ( [usr_login] => fred [0] => fred [app_name] => Users [1] => Users [acs_read] => 1 [2] => 1 [acs_add] => 0 [3] => 0 [acs_edit] => 1 [4] => 1 [acs_delete] => 1 [5] => 1 [acs_admin] => 0 [6] => 0 ) ) When I would like it to look like; Array ( [0] => Array ( [usr_login] => Joe [app_name] => Applications [acs_read] => 1 [acs_add] => 1 [acs_edit] => 1 [acs_delete] => 1 [acs_admin] => 1 ) [1] => Array ( [usr_login] => fred [app_name] => Users [acs_read] => 1 [acs_add] => 0 [acs_edit] => 1 [acs_delete] => 1 [acs_admin] => 0 ) ) Is this something that the SQL is doing? Edited December 26, 2017 by KillGorack Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554892 Share on other sites More sharing options...
Barand Posted December 26, 2017 Share Posted December 26, 2017 When you fetch the data, specify ASSOC and not the default BOTH (which gives you numeric keys and fieldname keys) Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554893 Share on other sites More sharing options...
ginerjm Posted December 26, 2017 Share Posted December 26, 2017 Looks funny? Do you mean because it is showing both assoc and numeric keys? Try changing your fetch to be just ASSOC instead of the default of BOTH. Use the constant PDO::FETCH_ASSOC. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554894 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 Thanks that makes sense works great.. appreciate it! Next and last question still in the subject of joins; I have a table with some stuff that I populate selection fields with. When I have a table that uses more than ONE feature that uses this table it errors out. I'm thinking aliases, but cant get it to work. before fiddeling with the sql. SELECT app.app_name, app.app_var, opt.opt_value, app.app_enabeled, app.app_admin FROM app LEFT JOIN opt ON opt.ID = app.app_landing LEFT JOIN opt ON opt.ID = app.app_type something like? SELECT app.app_name, app.app_var, opt.opt_value, opt.opt_value, app.app_enabeled, app.app_admin FROM app LEFT JOIN opt Tbla ON Tbla.ID = app.app_landing LEFT JOIN opt Tblb ON Tblb.ID = app.app_type Still errors out Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554895 Share on other sites More sharing options...
Barand Posted December 26, 2017 Share Posted December 26, 2017 (edited) Those tell me nothing about what you are trying to do. What data are you starting with and what output do want? edit: When you get errors, tell us what the error is. "It errors out" again tells us nothing. Edited December 26, 2017 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554896 Share on other sites More sharing options...
ginerjm Posted December 26, 2017 Share Posted December 26, 2017 Seems wrong that your ID field can match two different fields. Not a properly normalized database? Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554897 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 (edited) Seems wrong that your ID field can match two different fields. Not a properly normalized database? Yes absolutely, the database is NOT perfectly normalized. I have a half dozen forms, and with the multiple selection form fields that are populated with this ONE table. Because of this, is that functionality not possible? FYI SELECT app.app_name, app.app_var, opt.opt_value, opt.opt_value, app.app_enabeled, app.app_admin FROM app LEFT JOIN opt Tbla ON Tbla.ID = app.app_landing LEFT JOIN opt Tblb ON Tblb.ID = app.app_type Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'opt.opt_value' in 'field list' in C:\xampp\htdocs\portal-x\all\cn\list.php:56 Stack trace: #0 C:\xampp\htdocs\portal-x\all\cn\list.php(56): PDO->query('SELECT app.app_...') #1 C:\xampp\htdocs\portal-x\all\switch.php(82): include('C:\\xampp\\htdocs...') #2 C:\xampp\htdocs\portal-x\index.php(17): include('C:\\xampp\\htdocs...') #3 {main} thrown in C:\xampp\htdocs\portal-x\all\cn\list.php on line 56 Edited December 26, 2017 by KillGorack Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554898 Share on other sites More sharing options...
ginerjm Posted December 26, 2017 Share Posted December 26, 2017 (edited) Well if you just want that query to work you need to use the aliases instead of the table name in the joined elements, ie, Tbla.opt_value and Tblb.opt_value. You'll need to result columns for the opt_value Edited December 26, 2017 by ginerjm Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554899 Share on other sites More sharing options...
Barand Posted December 26, 2017 Share Posted December 26, 2017 Storing them in one table is not a problem per se. It's how you store them that matters. How the hell do you expect us to help you with a query when we don't know what the data looks like? I give up. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554900 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 (edited) Storing them in one table is not a problem per se. It's how you store them that matters. How the hell do you expect us to help you with a query when we don't know what the data looks like? I give up. Apologies sir, your frustration is understandable. Getting data INTO the table is not an issue, it's just grabbing the key from the table, and outputting vale from the opt table. Data in the opt table below. app table "app_type", and "app_landing" are foreign keys from the opt table All I'm looking for; is to have an sql join, that will show the value in the opt table above instead of its foreign key. Works perfectly when I have only ONE field like this, but two it gives error in post above Edited December 26, 2017 by KillGorack Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554901 Share on other sites More sharing options...
ginerjm Posted December 26, 2017 Share Posted December 26, 2017 I'm thinking that my suggestion is part of your solution. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554902 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 I'm thinking that my suggestion is part of your solution. Trying to implement, stay tuned.. of this above I have no doubt. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554903 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 Well I have no errors, but now when i use the PDO::FETCH_ASSOC it doesn't fetch the values I'm getting with the joins. The code; <?php error_reporting(E_ALL); ini_set('display_errors', '1'); $sql = "SELECT app.app_name, app.app_var, Tbla.opt_value, Tblb.opt_value, app.app_enabeled, app.app_admin FROM app LEFT JOIN opt Tbla ON Tbla.ID = app.app_landing LEFT JOIN opt Tblb ON Tblb.ID = app.app_type"; $stmt = $db->query($sql); $data = $stmt->fetchall(PDO::FETCH_BOTH); echo "<pre>"; print_r($data); echo "</pre>"; ?> Results; PDO::FETCH_ASSOC Array ( [0] => Array ( [app_name] => Applications [app_var] => app [opt_value] => DB Table [app_enabeled] => 1 [app_admin] => 1 ) [1] => Array ( [app_name] => Users [app_var] => usr [opt_value] => DB Table [app_enabeled] => 1 [app_admin] => 1 ) [2] => Array ( [app_name] => Permissions [app_var] => acs [opt_value] => DB Table [app_enabeled] => 1 [app_admin] => 1 ) [3] => Array ( [app_name] => Benchmark [app_var] => benchmark [opt_value] => DB Table [app_enabeled] => 1 [app_admin] => 1 ) [4] => Array ( [app_name] => News [app_var] => nws [opt_value] => DB Table [app_enabeled] => 1 [app_admin] => 1 ) [5] => Array ( [app_name] => Test Bench [app_var] => test [opt_value] => DB Table [app_enabeled] => 1 [app_admin] => 1 ) [6] => Array ( [app_name] => Fields [app_var] => fld [opt_value] => DB Table [app_enabeled] => 0 [app_admin] => 1 ) [7] => Array ( [app_name] => Settings [app_var] => stg [opt_value] => DB Table [app_enabeled] => 1 [app_admin] => 1 ) ) PDO::FETCH_BOTH Array ( [0] => Array ( [app_name] => Applications [0] => Applications [app_var] => app [1] => app [opt_value] => DB Table [2] => Index [3] => DB Table [app_enabeled] => 1 [4] => 1 [app_admin] => 1 [5] => 1 ) [1] => Array ( [app_name] => Users [0] => Users [app_var] => usr [1] => usr [opt_value] => DB Table [2] => Index [3] => DB Table [app_enabeled] => 1 [4] => 1 [app_admin] => 1 [5] => 1 ) [2] => Array ( [app_name] => Permissions [0] => Permissions [app_var] => acs [1] => acs [opt_value] => DB Table [2] => Index [3] => DB Table [app_enabeled] => 1 [4] => 1 [app_admin] => 1 [5] => 1 ) [3] => Array ( [app_name] => Benchmark [0] => Benchmark [app_var] => benchmark [1] => benchmark [opt_value] => DB Table [2] => DB Table [3] => DB Table [app_enabeled] => 1 [4] => 1 [app_admin] => 1 [5] => 1 ) [4] => Array ( [app_name] => News [0] => News [app_var] => nws [1] => nws [opt_value] => DB Table [2] => Index [3] => DB Table [app_enabeled] => 1 [4] => 1 [app_admin] => 1 [5] => 1 ) [5] => Array ( [app_name] => Test Bench [0] => Test Bench [app_var] => test [1] => test [opt_value] => DB Table [2] => Index [3] => DB Table [app_enabeled] => 1 [4] => 1 [app_admin] => 1 [5] => 1 ) [6] => Array ( [app_name] => Fields [0] => Fields [app_var] => fld [1] => fld [opt_value] => DB Table [2] => Index [3] => DB Table [app_enabeled] => 0 [4] => 0 [app_admin] => 1 [5] => 1 ) [7] => Array ( [app_name] => Settings [0] => Settings [app_var] => stg [1] => stg [opt_value] => DB Table [2] => Index [3] => DB Table [app_enabeled] => 1 [4] => 1 [app_admin] => 1 [5] => 1 ) ) Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554904 Share on other sites More sharing options...
ginerjm Posted December 26, 2017 Share Posted December 26, 2017 This is what I see: $q = "SELECT app.app_name, app.app_var, Tbla.flda, Tblb.fldb, app.app_enabeled, app.app_admin FROM app LEFT JOIN (ID, flda, fldb, fldx from opt) Tbla ON Tbla.ID = app.app_landing LEFT JOIN (ID, flda, fldb, fldy from opt) Tblb ON Tblb.ID = app.app_type"; Note - I used specific column names in the joined table, just like I use in the primary table selection in all my queries. Also that I selected a different value from the opt table joins since I didn't know what you are really looking for. Obviously you can alter this. PS - Is that really the way you spell "enbabled? Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554905 Share on other sites More sharing options...
Solution ginerjm Posted December 26, 2017 Solution Share Posted December 26, 2017 (edited) Use different result names for your two different results. If you assign the name to both "opt_value"s, it's not going to look good coming out. Ie, Tbla.opt_value as Opt_a, Tblb.opt_value as Opt_b I didn't notice this in the code I myself posted for you. Edited December 26, 2017 by ginerjm Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554906 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 Thanks! I'll work that into the loop. I'll push forward on the joins, I'm fairly new to them. yea lol it's even in the table spelled that way. Thanks for pointing that out. This is an instance of a typo that is replicated via copy and paste.. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554907 Share on other sites More sharing options...
ginerjm Posted December 26, 2017 Share Posted December 26, 2017 LOL again! I even misspelled enabled while pointing out your misspell. Are you really saying that my point about using fldname aliases solved all of your problems? Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554908 Share on other sites More sharing options...
KillGorack Posted December 26, 2017 Author Share Posted December 26, 2017 Yea I think so, I know what the join is supposed to look like, now I get to try and get that into something that can handle a few different tables. All in all my issue wasn't with how to cram text together, it was needing to have a workable example to start with. With that example I'll be good. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554909 Share on other sites More sharing options...
ginerjm Posted December 26, 2017 Share Posted December 26, 2017 HTH! Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554910 Share on other sites More sharing options...
KillGorack Posted December 27, 2017 Author Share Posted December 27, 2017 Just FYI, here's the first version of that loop. Creates the joins for seven separate tables, they all work. Whoot! Please ignore the darn typo. <?php // ================================================================================= // Build the sql // ================================================================================= $a = range('a', 'z'); $jn = ""; $ac = 0; foreach($fldarray as $fld){ if($fld['enabeled'] == 1 and $fld['index'] = 1){ $nm[] = $fld['human']; if($fld['opt'] == 1){ if($fld['opt_table'] <> "" and $fld['opt_field'] <> ""){ $jn .= " LEFT JOIN ".$fld['opt_table']." "."Tbl".$a[$ac]." ON "."Tbl".$a[$ac].".ID = ".$ap.".".$fld['fieldname']; $fd[] = "Tbl".$a[$ac].".".$fld['opt_field']." as Opt_".$a[$ac]; }else{ $jn .= " LEFT JOIN opt "."Tbl".$a[$ac]." ON "."Tbl".$a[$ac].".ID = ".$ap.".".$fld['fieldname']; $fd[] = "Tbl".$a[$ac].".opt_value as Opt_".$a[$ac]; } $ac = $ac + 1; }else{ $fd[] = $ap.".".$fld['fieldname']; } } } $sql = "SELECT ".implode(", ", $fd)." FROM ".$ap.$jn; // ================================================================================= // Column Headers // ================================================================================= echo "<table class=\"cells\">"; echo "<tr>"; echo "<td class=\"topper\">".implode("</td><td class=\"topper\">", $nm)."</td>"; echo "</tr>"; // ================================================================================= // The data // ================================================================================= $stmt = $db->query($sql); $data = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach($data as $datum){ echo "<tr><td class=\"topper\">".implode("</td><td class=\"topper\">", $datum)."</td></tr>"; } // ================================================================================= // Cap it off // ================================================================================= echo "</table>"; // ================================================================================= ?> Still needs some tweaks, and what-nots. but it's working. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554912 Share on other sites More sharing options...
ginerjm Posted December 27, 2017 Share Posted December 27, 2017 Have no idea what this is. Time to close the post. Quote Link to comment https://forums.phpfreaks.com/topic/305995-left-joins-not-being-implemented-correctly/#findComment-1554913 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.