Jump to content
KillGorack

Left joins not being implemented correctly?

Recommended Posts

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 by KillGorack

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by KillGorack

Share this post


Link to post
Share on other sites

When you fetch the data, specify ASSOC and not the default BOTH (which gives you numeric keys and fieldname keys)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

Seems wrong that your ID field can match two different fields. Not a properly normalized database?

Share this post


Link to post
Share on other sites

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 by KillGorack

Share this post


Link to post
Share on other sites

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 by ginerjm

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

 

opt.PNG

 

 

 

 

app table "app_type", and "app_landing" are foreign keys from the opt table

app.PNG

 

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 by KillGorack

Share this post


Link to post
Share on other sites

I'm thinking that my suggestion is part of your solution.

Share this post


Link to post
Share on other sites

I'm thinking that my suggestion is part of your solution.

 

Trying to implement, stay tuned.. of this above I have no doubt.

Share this post


Link to post
Share on other sites

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
    )
)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by ginerjm

Share this post


Link to post
Share on other sites

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..

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Have no idea what this is. Time to close the post.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.