Jump to content

Left joins not being implemented correctly?


KillGorack

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
        )

)
Link to comment
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?
Link to comment
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?

Link to comment
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

Link to comment
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
Link to comment
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

Link to comment
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
    )
)
Link to comment
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?

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.