Lookup Examples

This page shows some examples of $lookupAssoc arrays - these represent SQL WHERE clauses within the SmartDB.

Where exactly are these arrays used?

Each of the example arrays on this page can be used as a $lookupAssoc for the following functions (or on the DbManager directly):

• $table->LookupRow($lookup)
• $table->LookupRows($lookup)
• $table->DeleteRow($lookup)
• $table->DeleteRows($lookup)
• $table->LookupColumnValue($lookup)
• $table->LookupColumnValues($lookup)
• $column->GetMaxValue($lookup)
• $column->GetMinValue($lookup)
• $column->GetAggregateValue($aggregateFunction, $lookup)

 

What do these arrays contain?

Each array path must contain at least 1 operator, condition, column name, and value. The array's keys/indexes can be operators, conditions, or column names. The array's values contain the actual value to lookup (string or numeric data). Each array and array dimension groups everything inside of it (adds a new set of parenthesis).

• Operators
• AND (default if not set)
• OR
• Conditions
  
• = (default if not set)
• == (same as =)
• !=
• <> (same as !=)
• >
• >=
• <
• <=
• like (% is a wildcard and will match any number of characters. Ex: "%beef is%dinner%" would match "beef is not for dinner!")
• not like (% is a wildcard here too)
• is not
• Column Names (as defined in your XML database schema)
  
• Values (the actual value to lookup - string or numeric data)

The MINIMUM you need for a $lookup array is a column name and a value. See examples below.

 Examples

Not all of these examples are practical. They were created only for demonstration.

WHERE col1 = 5 AND col2 = 10 AND col3 = 15

$lookup = [ // (outer arrays default to "AND" and "=")
    "col1"=>5,   //col1=5
                 //AND
    "col2"=>10,  //col2=10
                 //AND
    "col3"=>15   //col3=15
];

/** alternative syntax - this method would yield the same array **/
$lookup = array();
$lookup["col1"] = 5;
$lookup["col2"] = 10;
$lookup["col3"] = 15;

 

 

WHERE (col1 = 5 OR col2 = 10 OR col3 = 15)

$lookup = [ // (outer array defaults to "AND" and "=")
    "OR" => [ // (use operator "OR" for all inner elements)
        "col1"=>5,   //col1=5
                     //OR
        "col2"=>10,  //col2=10
                     //OR
        "col3"=>15   //col3=15
    ]
];

/** alternative syntax - this method would yield the same array **/
$lookup = array();
$lookup["OR"]["col1"] = 5;
$lookup["OR"]["col2"] = 10;
$lookup["OR"]["col3"] = 15;

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup["OR"] = [
    "col1" => 5,
    "col2" => 10,
    "col3" => 15
];

 

 

WHERE foo2 = 'bar2' AND (foo3 = 'bar3' OR foo4 = 'bar4') AND foo5 = 'bar5'

$lookup = [ // (outer array defaults to "AND" and "=")
  "foo2" => "bar2",     //foo2='bar2'
                        //AND
  "OR" => [ // (nested arrays inherit operator (AND,OR), condition ( =, <=, !=, >, etc.), column name, and value)
    "foo3" => "bar3",   //foo3='bar3'
                        //OR
    "foo4" => "bar4"    //foo4='bar4'
  ],
                        //AND
  "foo5" => "bar5"      //foo5='bar5'
];

/** alternative syntax - this method would yield the same array **/
$lookup = array();
$lookup["foo2"] = "bar2";
$lookup["OR"]["foo3"] = "bar3"
$lookup["OR"]["foo4"] = "bar4"
$lookup["foo5"] = "bar5"; 

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup["foo2"] = "bar2";
$lookup["OR"] = [
    "foo3" => "bar3",
    "foo4" => "bar4"
];
$lookup["foo5"] = "bar5"; 

 

 

WHERE (foo1 = 'bar1' AND foo2 = 'bar2') OR (foo3 = 'bar3' AND foo4 = 'bar4')

$lookup = [ "OR" => [ // (outer array defaults to "AND" by default, so override it)
  [ // (nested arrays inherit operator (AND,OR), condition ( =, <=, !=, >, etc.), column name, and value)
    "foo1" => "bar1",   //foo1='bar1'
                        //AND
    "foo2" => "bar2"    //foo2='bar2'
  ],                    
                        //OR
  [ // (nested arrays inherit operator (AND,OR), condition ( =, <=, !=, >, etc.), column name, and value)
    "foo3" => "bar3",   //foo3='bar3'
                        //AND
    "foo4" => "bar4"    //foo4='bar4'
  ]
]];

/** alternative syntax - this method would yield the same result **/
$lookup = [];
$lookup["foo1"] = "bar1";
$lookup["foo2"] = "bar2";
$lookup["foo3"] = "bar3";
$lookup["foo4"] = "bar4"; 

 

 

WHERE (col1 = 4 AND col3 = 5) AND (col1 = 6 OR col2 = 7)

$lookup = [ // (outer array defaults to "AND" and "=")
  "AND"=>[ // (set operator to "AND" for this group)
    "col1"=>4,         //col1=4
                       //AND
    "col3"=>5          //col3=5
  ],
                       //AND (from outer array/default)
  "OR"=>[ // (set operator to "OR" for this group)
    "col1"=>6,         //col1=6
                       //OR
    "col2"=>7          //col2=7
  ]
];

/** alternative syntax - this method would yield the same array **/
$lookup = array();
$lookup["AND"]["col1"] = 4; //the "AND" is unnecessary since parent array defaults to "AND"
$lookup["AND"]["col3"] = 5; //the "AND" is unnecessary since parent array defaults to "AND"
$lookup["OR"]["col1"] = 6;
$lookup["OR"]["col2"] = 7;

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup[] = [ //the "AND" is unnecessary since parent array defaults to "AND"
    "col1" => 4,
    "col3" => 5
];
$lookup["OR"] = [
    "col1" => 6,
    "col2" => 7
];

 

 

WHERE (col1 > 40) AND (col2 < 100)

$lookup = [ // (outer array defaults to "AND" and "=")
  "col1" => [ ">" => 40 ],     //col1 > 40
                                    //AND
  "col2" => [ "<" => 100 ]     //col1 > 100
];

/** alternative syntax - this method would yield the same array **/
$lookup = array();
$lookup["col1"][">"] = 40;
$lookup["col2"]["<"] = 100; 

 

 

WHERE (col1 > 4) AND col3 = 5 AND (col1 = 6 OR col2 = 7)

$lookup = [
    "col1" => [">" => 4],  //col1 > 4
                                //AND
    "col3"=>5,                  //col3 = 5
                                //AND
    "OR"=>[
        "col1" => 6,              //col1 = 6
                                //OR
        "col2" => 7               //col2 = 7
    ]

    // (anything else added here is AND'ed)
);

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"][">"] = 4;
$lookup["col3"] = 5;
$lookup["OR"]["col1"] = 6;
$lookup["OR"]["col2"] = 7;

/** another alternative (you can create these arrays so many ways) **/
$lookup = array();
$lookup["col1"][">"] = 4;
$lookup["col3"] = 5;
$lookup["OR"] = [
    "col1" => 6,
    "col2" => 7
];

 

 

WHERE (col1 = 3 AND col1 = 5 AND col1 = 7)

//this is not practical
$lookup = [
    "col1" => ["3","5","7"]
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"] = ["3","5","7"];

 

 

WHERE ((col1 = 3 OR col1 = 5 OR col1 = 7))

$lookup = [ 
    "col1" => [
         "OR" => ["3","5","7"]
     ]
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"]["OR"] = [3,5,7];

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup["col1"]["OR"][] = 3;
$lookup["col1"]["OR"][] = 5;
$lookup["col1"]["OR"][] = 7;

 

 

WHERE (col1 < 10 AND (col1 = 3 OR col1 = 5 OR col1 = 7)) AND col2 = 11

$lookup = [ // (outer array defaults to "AND" and "=")
  "col1" => [
    "<" => 10,                   //col1<10
                                 //AND
    "OR" => ["3","5","7"]        //col1=3 OR col1=5 OR col1=7
  ],
                                 //AND
  "col2" => 11                   //col2=11
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"]["<"] = 10;
$lookup["col1"]["OR"] = [3,5,7];
$lookup["col2"] = 11;

 

 

WHERE (col1 > 40 AND col1 <= 50)

$lookup = [ // (outer array defaults to "AND" and "=")
  "col1" => [
    ">" => 40,                 //col1 > 40
                               //AND
    "<=" => 50                 //col1 <= 50
  ],
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"][">"] = 40;
$lookup["col1"]["<="] = 50;

 

 

WHERE ((col1 <= 40 OR col1 > 50))

$lookup = [ // (outer array defaults to "AND" and "=")
  "col1" => [
    "OR" => [ // (set operator to "OR" for this group)
      "<=" => 40,                //col1 <= 40
                                 //OR
      ">" => 50                  //col1 > 50
    ]
  ]
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"]["OR"]["<="] = 40;
$lookup["col1"]["OR"][">"] = 50;

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup["col1"]["OR"] = [
    "<=" => 40,
    ">"  => 50
];

 

 

WHERE ((col1 <= 40 OR col1 > 50) AND col1 != 54)

$lookup = [ // (outer array defaults to "AND" and "=")
  "col1" => [
    "OR" => [ // (set operator to "OR" for this group)
      "<=" => 40,                //col1 <= 40
                                 //OR
      ">" => 50                  //col1 > 50
    ],
                                 //AND
    "!=" => "54"                 //col1 != 54
  ],
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"]["OR"]["<="] = 40;
$lookup["col1"]["OR"][">"] = 50;
$lookup["col1"]["!="] = 54; 

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup["col1"]["OR"] = [
    "<=" => 40,
    ">"  = 50
];
$lookup["col1"]["!="] = 54;

 

 

WHERE (((col1 > 4 OR col1 = 1) AND (col1 != 9 AND col1 < 15)))

$lookup = [ // (outer array defaults to "AND" and "=")
  "col1" => [
    "AND" => [ // (this AND is not needed here)
        "OR" => [
          ">" => 4,               //col1 > 4
                                  //OR
          1,                      //col1 = 1
        ],
                                  //AND
        "AND" => [
          "!=" => 9,              //col1 != 9
                                  //AND
          "<" => 15               //col1 < 15
        ]
     ]
  ]
];

/** alternative syntax - this method would yield the same result **/
$lookup = [];
$lookup["col1"]["OR"][">"] = 4;
$lookup["col1"]["OR"]["="] = 1;
$lookup["col1"]["AND"]["!="] = 9;
$lookup["col1"]["AND"]["<"] = 15; 

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup["col1"]["OR"] = [
    ">" => 4,
    "=" => 1
];
$lookup["col1"]["AND"] = [
    "!=" => 9,
    "<" => 15
];

 

 

WHERE ((col1 < 0 OR (col1 > 3 AND (col1 != 10 AND col1 != 12)))) AND col2 = 5

$lookup = [ // (outer array defaults to "AND" and "=")
  "col1" => [
    "OR" => [
      "<" => 0,                   //col1 < 0
                                  //OR
      "AND" => [ // (override outer "OR" with "AND" for this group)
        ">" => 3,                 //col1 > 3
                                  //AND
        "!=" => [10,12]      //col1 != 10 AND col1 != 12
      ]
    ]
  ],
                                  //AND
  "col2" => 5                     //col2 = 5
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"]["OR"]["<"] = 0;
$lookup["col1"]["OR"]["AND"][">"] = 3;
$lookup["col1"]["OR"]["AND"]["!="] = [10,12];
$lookup["col2"] = 5;

/** another alternative (you can create these arrays so many ways) **/
$lookup = array();
$lookup["col1"]["OR"] = [
    "<" = 0,
    "AND" = [
        ">" => 3,
        "!=" => [10,12],
    ]
]
$lookup["col2"] = 5;

 

 

WHERE (col1 = 5 OR (col2 = -1 AND col3 = 'yo')) AND (col4 LIKE '%yo again%') AND (col5 != 5)

$lookup = [
    "OR" => [
        "col1" => 5,
        "AND" => [
            "col2"=>-1,
            "col3"=>"yo"
        ]
    ],
    "col4" => [
        "LIKE"=>"%yo again%"
    ],
    "col5" => [
        "<>" => 5
    ]
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["OR"]["col1"] = 5;
$lookup["OR"]["AND"]["col2"] = -1;
$lookup["OR"]["AND"]["col3"] = "yo";
$lookup["col4"]["LIKE"] = "%yo again%";
$lookup["col5"]["!="] = 5; //"!= " is the same thing as "<>"

/** another alternative (you can create these arrays so many ways) **/
$lookup = [];
$lookup["OR"] = [
    "col1" => 5,
    "AND" = [
        "col2" => -1,
        "col3" => "yo"
    ]
];
$lookup["col4"]["LIKE"] = "%yo again%";
$lookup["col5"]["!="] = 5;

 

 

WHERE (col1 < 'z') AND col2 is null AND (col3 IS NOT 5)

$lookup = [
    "col1" => ["<" => "z"],
    "col2" => NULL,
    "col3" => ["IS NOT" => 5]
];

/** alternative syntax - this method would yield the same array **/
$lookup = [];
$lookup["col1"]["<"] = "z";
$lookup["col2"] = NULL;
$lookup["col3"]["IS NOT"] = 5;

The SET Datatype

Extra examples for lookup up within SETs can be found here